Unlocking Oracle 12c Optimizer: Adaptive Query Optimization and New Statistics Features

1. Adaptive Query Optimization

Oracle 12c introduces adaptive query optimization, a set of features that allow the optimizer to adjust execution plans in real time and gather additional statistics when existing ones are insufficient. It consists of two main components: adaptive plans and adaptive statistics.

2. Adaptive Plan

The optimizer can postpone generating a final plan until execution time. It embeds a statistics collector in the default plan to compare estimated cardinalities with actual row counts. If a significant mismatch is detected, the plan—or parts of it—can be automatically adjusted during the first execution.

3. Adaptive Join Methods

Multiple sub‑plans are generated for certain join branches, enabling the optimizer to switch join methods at runtime. For example, an initial nested‑loop join between order_items and product_info can be replaced by a hash join if the collector observes that the actual row count exceeds the estimate.

4. Adaptive Parallel Distribution Method

When a statement runs in parallel, the optimizer can delay choosing a distribution method until execution, using a "Hybrid Hash" approach. If the collected row count is below a threshold (twice the degree of parallelism), the method switches from hash to broadcast; otherwise, it stays hash.

5. Adaptive Statistics

The optimizer can generate adaptive statistics to supplement missing or inadequate base‑table statistics, improving cardinality estimates for complex predicates.

6. Dynamic Statistics

Dynamic sampling is enhanced to "dynamic statistics" (level 11). When enabled, the optimizer automatically gathers dynamic statistics for any SQL, even if base statistics exist, based on predicate complexity and expected execution time.

7. Automatic Re‑Optimization

After the first execution, the optimizer may automatically re‑optimize a statement if runtime statistics differ markedly from original estimates. This process can repeat, continually refining plans.

8. Statistics Feedback (Cardinality Feedback)

When repeated executions show cardinality estimation errors, the optimizer creates a statistics‑feedback monitor and a SQL plan directive that stores corrected cardinalities for future executions.

9. Performance Feedback (AutoDOP)

If AutoDOP is enabled in adaptive mode, the optimizer records the parallel degree chosen during the first execution and compares it with the degree calculated from actual performance metrics. Significant differences trigger re‑optimization and store the feedback for subsequent runs.

10. SQL Plan Directives

SQL plan directives are metadata objects created from automatic re‑optimization. They reside in the SYSAUX tablespace and can be viewed via DBA_SQL_PLAN_DIRECTIVES. Directives guide the optimizer in future executions, for example, forcing dynamic sampling for specific predicate patterns.

11. Optimizer Statistics

Accurate optimizer statistics—describing tables, indexes, and column distributions—are essential for maintaining database performance. Oracle continuously improves automatic statistics collection with each release.

12. New Histogram Types

Oracle 12c adds two histogram types: top‑frequency histograms and hybrid histograms, complementing the traditional frequency and height‑balanced histograms.

13. Top‑Frequency Histograms

When a column has a small set of values that dominate the data (e.g., >99% of rows), a top‑frequency histogram captures those values precisely, provided ESTIMATE_PERCENT=AUTO_SAMPLE_SIZE is used.

14. Hybrid Histograms

Hybrid histograms are created for columns with many distinct values (>254). They record the frequency of each endpoint, allowing more buckets and more accurate cardinality estimates for values that were only approximated in height‑balanced histograms.

15. Online Statistics Collection

When creating indexes, CTAS, or IAS operations, Oracle automatically gathers optimizer statistics as part of the load, eliminating the need for a separate full‑table scan. To collect histograms or index statistics without re‑gathering base column stats, use DBMS_STATS.GATHER_TABLE_STATS with the GATHER AUTO option.