Oracle Database - Adaptive Plan

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.

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.

Glossary

Subplan

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

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.

Management

View

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 

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.

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

Configuration

Documentation / Reference


Powered by ComboStrap