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.
Articles Related
Management
View
Performance Improvment
SELECT CHILD_NUMBER, CPU_TIME/EXECUTIONS, ELAPSED_TIME/EXECUTIONS, BUFFER_GETS/EXECUTIONS
FROM V$SQL WHERE SQL_ID ='&sql_id';
Configuration
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
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