New Optimizer Feature with 11g and 12c

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
optimizer_use_invisible_index=true
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:
Dynamic plans

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.

Reoptimization

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

With 12c

• 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 12.1.0.1 or later, and OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter is set to the default of FALSE.