Oracle Database - Adaptive Optimization

Card Puncher Data Processing

Oracle Database - Adaptive Optimization

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.

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





Discover More
Card Puncher Data Processing
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...
Card Puncher Data Processing
Oracle Database - Automatic reoptimization / (statistics|cardinality) feedback

Statistics feedback also known as: cardinality feedback Automatic reoptimization is an adaptive feature. It improves plans for repeated queries that have cardinality misestimates. adaptive plans...



Share this page:
Follow us:
Task Runner