Oracle Database - Automatic reoptimization / (statistics|cardinality) feedback

Card Puncher Data Processing

About

Statistics feedback also known as:

is an adaptive feature.

It improves plans for repeated queries that have cardinality misestimates.

adaptive plans do not support adapting the join order during execution. In these cases, the optimizer considers automatic reoptimization (statistics feedback). In contrast to adaptive plans, automatic reoptimization changes a plan on subsequent executions after the initial execution.

Steps

The optimizer may enable monitoring for statistics feedback for the shared SQL area in the following cases:

  • Tables with no statistics
  • Multiple conjunctive or disjunctive filter predicates on a table
  • Predicates containing complex operators for which the optimizer cannot accurately compute selectivity estimates

If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use. The optimizer also creates a SQL plan directive so that other SQL statements can benefit from the information obtained during this initial execution.

Management

View

You can determine whether the database used statistics feedback to adjust its cost estimates for the second execution of the query based on the comments in the Notes section of plan.

Example:

Note: 
- statistics feedback used for this statement

Suppress

Flush the shared pool

Configuration

See Oracle Database - Adaptive Optimization

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - Adaptive Optimization

Adaptive Optimization includes the following functionalities: adaptive plan automatic reoptimization duringautomatic reoptimizationafter When the OPTIMIZER_FEATURES_ENABLE initialization...
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...



Share this page:
Follow us:
Task Runner