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.
SELECT CHILD_NUMBER, CPU_TIME/EXECUTIONS, ELAPSED_TIME/EXECUTIONS, BUFFER_GETS/EXECUTIONS
FROM V$SQL WHERE SQL_ID ='&sql_id';
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
The OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter controls the 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