IN SQL Server I am aware of two statements that drop the caching of a query plan. I am looking for the equivalent in Oracle to use when optimizing a query. The SQL Server statements are.
you can use optimizer settings by either setting the initialization parameter optimizer_mode=<value>, issue at the session level by issuing alter session set optimizer_mode=<value> or use hints in sql statements to overide the default optimizer setting.
Optimizer CBO (Cost Based Optimizer) is based on table statistics gatheres using the DBMS_STATS package. This can be done at database, schema or table/index level.
heres some info:-
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
rule
The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics.
choose
The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.
If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.
first_rows_n
The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
all_rows
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.