Oracle Database - Adaptive Plan
Table of Contents
1 - About
Adaptive plan is an adaptive functionality.
The Adaptive plan functionality enables the optimizer to:
- generate multiple predetermined subplans
- defer the subplan decision until execution time based on execution statistics.
Adaptive plan is useful when the optimizer picks a suboptimal plan because of a cardinality misestimate.
An adaptive plan chooses among subplans during the current statement execution. In contrast, automatic reoptimization changes a plan only on executions that occur after the current statement execution.
Adaptive plans do not support adapting the join order during execution. This case is resolved during a second SQL execution with the automatic reoptimization feature.
Adaptive plans support adapting the Join Method.
2 - Articles Related
3 - Example
A nested loops join is preferable if the database can avoid scanning a significant portion of product_information because its rows are filtered by the join predicate. If few rows are filtered, however, then scanning the right table in a hash join is preferable.
4 - Glossary
4.1 - Subplan
A subplan is a portion of a plan that the optimizer can switch to as an alternative at run time.
4.2 - Statistics collector
An optimizer statistics collector is a row source inserted into a plan at key points to collect run-time statistics. These statistics help the optimizer make a final decision between multiple subplans.
The statistics collector is an execution plan operation (See Id 4):
---------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 36 | | * 1 | HASH JOIN | | 1 | 1 | 36 | |- 2 | NESTED LOOPS | | 1 | | 106 | |- 3 | NESTED LOOPS | | 1 | 1 | 106 | |- 4 | STATISTICS COLLECTOR | | 1 | | 106 | | * 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 1 | 106 | |- * 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 0 | 1 | 0 | |- * 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 0 | 1 | 0 | | * 8 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 1 | 2 | ----------------------------------------------------------------------------------
After the optimizer chooses a subplan, the statistics collector stops collecting statistics and buffering rows, and permits the rows to pass through instead.
5 - Management
5.1 - View
5.1.1 - Execution Plan Output
You can determine whether the database used adaptive query optimization for a SQL statement based on the comments in the Notes section of plan.
Example:
Note ----- - this is an adaptive plan
5.1.2 - Adaptive report
You can view adaptive reports by using DBMS_XPLAN.DISPLAY_CURSOR. The format argument passed to DBMS_XPLAN.DISPLAY_CURSOR must include +ADAPTIVE.
5.1.3 - Data Dictionary
The following view:
has the following column IS_RESOLVED_ADAPTIVE_PLAN.
This column shows whether all of the adaptive parts of a plan have been resolved to the final plan (ie that all subplan decision have been made)
- NULL: If the plan is not adaptive
- Y: If the plan is fully resolved
- N: If the plan is not yet fully resolved
Then this kind of query gives you all query (in memory) with adaptive plan.
SELECT * FROM v$sql WHERE IS_RESOLVED_ADAPTIVE_PLAN IN( 'Y', 'N' ) AND parsing_user_id = SYS_CONTEXT( 'USERENV', 'CURRENT_USERID' );
5.2 - Configuration
See configuration