Oracle Database - Adaptive Plan

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.

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.

A subplan is a portion of a plan that the optimizer can switch to as an alternative at run time.

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.

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 

You can view adaptive reports by using DBMS_XPLAN.DISPLAY_CURSOR. The format argument passed to DBMS_XPLAN.DISPLAY_CURSOR must include +ADAPTIVE.

The following view:

• V_$SQL
• V_$SQLAREA

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' );

See configuration

• 9.2.1 Reading Adaptive Plans - Oracle® Database SQL Tuning Guide 12c Release 1 (12.1)