Unlock Oracle 12c Optimizer: New Stats, Adaptive Features & Advanced Techniques

Session‑Level Statistics for Global Temporary Tables

Oracle 12c introduces the GLOBAL_TEMP_TABLE_STATS option in DBMS_STATS, defaulting to SESSION . Each session can now maintain its own statistics for a global temporary table (PRESERVE ROWS ON COMMIT), avoiding the previous shared‑statistics issue when data volume or characteristics differ between sessions.

New DBMS_STATS Reporting Subprograms

Oracle 12c adds several reporting functions to DBMS_STATS:

REPORT_STATS_OPERATIONS – Generates a detailed report of statistics‑gathering activities within a time window.

REPORT_SINGLE_STATS_OPERATION – Reports details for a specific statistics‑gathering operation.

REPORT_GATHER_*_STATS – Shows which objects would be processed by a GATHER_*_STATS call, with additional detail_level and format parameters.

New Optimization Techniques

Partial Join Elimination replaces a DISTINCT operation with an early INNER or SEMI join, reducing row counts and improving plan performance for queries involving aggregates, UNION/INTERSECT, or EXISTS subqueries.

Null‑Aware Semi‑Join allows a semi‑join to retain rows where the join column is NULL, enabling better plans for EXISTS predicates combined with IS NULL.

Scalar Subquery Unnesting transforms scalar subqueries in the SELECT list into joins with a GROUP‑BY view, eliminating per‑row execution of the subquery.

Multi‑Table Left Outer Join (Oracle’s (+) syntax) now supports multiple tables on the left side, allowing view merging and more optimal join orderings.

New Initialization Parameters

OPTIMIZER_ADAPTIVE_FEATURES – Enables adaptive query optimization when OPTIMIZER_FEATURES_ENABLE is set to 12.1.0.1 or higher.

OPTIMIZER_ADAPTIVE_REPORTING_ONLY – When TRUE, adaptive plans are only reported, not applied.

OPTIMIZER_DYNAMIC_SAMPLING – Adds level 11, allowing the optimizer to automatically decide when dynamic statistics are beneficial.

SQL Plan Management (SPM) Enhancements

SPM continues to protect performance by capturing accepted plans as baselines. In 12c, plan evolution is automated via the SYS_AUTO_SPM_EVOLVE_TASK, which ranks unaccepted plans and evolves them during maintenance windows. Manual evolution uses DBMS_SPM.REPORT_EVOLVE_TASK and DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE.

Enhanced Statistics Collection

Incremental Statistics – For partitioned tables with INCREMENTAL=TRUE, global stats are derived from aggregated partition synopses, avoiding full table scans.

Incremental Staleness – The INCREMENTAL_STALENESS attribute controls when partition stats are considered stale (NULL = immediate, USE_STALE_PERCENT, or USE_LOCKED_STATS).

Partition‑Swap Loading – Non‑partitioned tables can now collect synopsis stats before a swap, enabling automatic incremental maintenance after the exchange.

Concurrent Statistics Collection – The CONCURRENT option (ALL or AUTOMATIC) now groups small objects into shared jobs, reducing scheduler overhead.

Automatic Column‑Group Detection

Extended statistics improve cardinality estimates for multi‑column predicates. Oracle can automatically discover useful column groups via a three‑step process:

Enable column usage monitoring with DBMS_STATS.SEED_COL_USAGE (e.g., 300‑second window).

Create column groups using DBMS_STATS.CREATE_EXTENDED_STATS, which consumes the captured usage data.

Re‑gather statistics on the affected tables so the new groups are maintained.

Conclusion

The Oracle 12c optimizer represents a substantial leap thanks to adaptive query methods, richer statistics, and a suite of new features that give DBAs and developers finer control over plan quality and performance. Understanding and leveraging these enhancements can smooth upgrade paths and ensure consistently optimal execution plans.