New Optimizer Feature with 11g
1) Invisible indexes
With 11g release,index can be made invisible. They will be maintained all the time and can be made visible any time
alter index K invisible;
Optimizer will not use the index if it is invisible
alter index K visible
If want one query to use invisible index,we can set this parameter in the session
Then the query in the session will use the invisible index
2)Extented stats on the table
If we use any function on the predicate in the query,the optimizer is not able to get the selectivity properly.With 11g we can create extented stats on the column for the function .
exec dbms_stats.create_extented_stats(ownname => ‘OWN’,tab_name=> ‘TAB’ ,extension=> ‘func(col)’);
Then optimizer will be able to find the correct selectivity
New Optimizer Features with 12c R1
Adaptive Execution Plans
This is quite noticeable features in the Release 12c. A query plan changes during execution because runtime conditions indicate that optimizer estimates are inaccurate.
All adaptive execution plans rely on statistics that are collected during query execution.
The two adaptive plan techniques are:
a) In this plan ,the final decision is based on statistics that are collected during execution.
b) Alternate subplans are precomputed and stored in the cursor.
c) Statistic collectors are inserted at key points in the plan.
d) If statistics prove to be out of range, subplans can be swapped.
e) It requires buffering near the swap point to avoid returning rows to users.
f) Only join methods and the distribution method can change.
In Pre 12c
- Statistics feedback (formerly known as Cardinality feedback) was introduced in Oracle Database 11g, Release 2.
- Statistics feedback is useful for queries where the data volume being processed is stable over time.
- During query execution optimizer estimates are compared to execution statistic: if they vary significantly then a new plan will be chosen for subsequent executions
- So the subsequent plan are changed based on Statistics feedbac
- The optimizer automatically changes a plan during subsequent executions of a SQL statement.
- Join statistics are also included.
- Statements are continually monitored to see if statistics fluctuate over different executions.
- It works with adaptive cursor sharing for statements with bind variables.
- IS_REOPTIMIZABLE column is added in V$SQL.
- Information found at execution time is persisted as SQL plan directives.
The database uses adaptive execution plans when OPTIMIZER_FEATURES_ENABLE is set to 220.127.116.11 or later, and OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter is set to the default of FALSE.