New Optimizer Feature with 11g
(1) Invisible indexes
With an 11g release, the index can be made invisible. They will be maintained all the time and can be made visible at 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 the 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)Extended 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 extended 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 a noticeable feature 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 statistics: if they vary significantly then a new plan will be chosen for subsequent executions
- So the subsequent plan is changed based on statistical feedback
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.
New Optimizer Feature with 19c
Automatic Indexing
- Oracle 19c introduced Automatic Indexing as a major feature to improve database performance.
- This feature automates the process of managing indexes, including their creation, rebuilding, and dropping, based on the changing patterns of data access.
- It continuously monitors the database workload and determines if a new index would improve performance, or if an existing index is no longer useful and should be dropped.
Real-Time Statistics and High-Frequency Statistics Collection
Enhancements in statistics collection allow the Optimizer to have more current information about the data distribution in the tables, leading to better execution plans.
SQL Quarantine:
- This feature automatically detects and quarantines SQL statements that consume excessive system resources, preventing them from overloading the system.
- When DBRM detects a SQL statement is exceeding a resource or run-time limit, the SQL execution plan used by the statement is quarantined. If the SQL statement is executed again and it is using the same SQL execution plan then it will be terminated immediately.
- This can significantly reduce the amount of system resources that would otherwise be wasted.
ENHANCEMENTS TO DBMS EXPLAIN PLAN
(1)Hint Usage Reporting
Hints used in SQL statements can be difficult to ‘debug’. For example, it is not always easy to understand why a particular hint is not being used. There is no error reporting for hints, so if they are incorrectly specified in any way, they will be ignored silently.
Hint usage reporting addresses this. It provides detailed information like invalid hints, conflicting hints, ignored hints (e.g. because of the
query block location specified) and hints that successfully affected the final plan
(2)plan comparison feature
The plan comparison feature (henceforth referred to compare plans tool) takes as input a reference plan and an arbitrary list of test
plans. It compares the reference plan with each of the test plans in the list and produces a difference report
Optimizer Statistics Advisor
- From Oracle Database 12 Release 2, a feature called the Optimizer Statistics Advisor is available. The goal of the advisor is to analyze how statistics are gathered, validate the quality of statistics already gathered and check the status of auto stats gathering (for example, checking for successful completion).
- To achieve this, it examines the data dictionary with respect to a set of rules. Where exceptions to the rules are found, findings may be generated and these, in turn, may lead to specific recommendations
Enhancement in Adaptive Query Optimization
- There was a lot of improvement done from 12.1.
- There are two distinct aspects of Adaptive Query Optimization: adaptive plans, which focus on improving the execution of a query and adaptive statistics, which uses additional information to improve query execution plans.
- From Oracle Database 12.2 onwards, adaptive plans are enabled and disabled using the database parameter
- optimizer_adaptive_plans. Use of the default value of TRUE (enabled) is recommended