Table of Contents

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