Oracle Database - Adaptive Optimization

> Database > Oracle Database

1 - About

Adaptive Optimization includes the following functionalities:

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.

Advertising

3 - Management

3.1 - View

3.1.1 - Performance Improvment

SELECT CHILD_NUMBER, CPU_TIME/EXECUTIONS, ELAPSED_TIME/EXECUTIONS, BUFFER_GETS/EXECUTIONS
FROM   V$SQL WHERE  SQL_ID ='&sql_id';

4 - Configuration

4.1 - Enable / Disable

When the OPTIMIZER_FEATURES_ENABLE initialization parameter is set to 12.1.0.1 or later, adaptive mode is enabled but can be not active if in reporting-only mode

show parameter OPTIMIZER_FEATURES_ENABLE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
optimizer_features_enable            string      12.1.0.1

4.2 - Reporting-only mode

The OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter controls the reporting-only mode:

  • FALSE: the adaptive optimizations are enabled as usual.
  • TRUE: adaptive optimizations run in reporting-only mode.

In reporting-only mode, the information required for an adaptive optimization is gathered, but no action is taken to change the plan. For instance, an adaptive plan will always choose the default (optimizer-chosen) plan, but information is collected on what plan to adapt to in non-reporting mode. This information can be viewed in the adaptive plan report.

When set to false (default), the adaptive optimizations work as usual.

show parameter OPTIMIZER_ADAPTIVE_REPORTING_ONLY
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
optimizer_adaptive_reporting_only    boolean     FALSE
Advertising