Mastering Oracle Dynamic Sampling for Accurate Cardinality Estimates

What Is Oracle Dynamic Sampling?

Oracle dynamic sampling (called Dynamic Statistics in 12c) supplements traditional statistics by sampling table data at parse time, providing the optimizer with more accurate cardinality estimates when data changes rapidly or when conventional statistics are insufficient.

Typical Application Scenarios

Temporary tables created during ETL or BI processes, where row counts vary dramatically and no stable statistics exist.

Queries with composite filter predicates, non‑equality conditions, or functions applied to columns that prevent the use of column‑group or extended statistics.

In Oracle 12c, dynamic sampling can also estimate cardinalities for GROUP BY results and join result sets, a capability not available in earlier releases.

Case Study: CRM System in the Financial Industry

A CRM query routinely took over five minutes because the optimizer severely underestimated row counts (e.g., estimating 1 row while the actual count was 14 million). The misestimate caused a nested‑loop join that scanned the driving table millions of times.

Investigation revealed that the table P_CUST_STAT had statistics collected on October 9 when it was empty (Num_Rows = 0), so the optimizer assumed a single row. The table is used as a temporary store, so its data changes frequently.

A second example involved table B_S_CUST_STAT where a filter on Data_date = to_date('20161008','yyyymmdd') also yielded an estimated cardinality of 1 row instead of 14 million, again leading to an inefficient nested‑loop execution.

The statistics showed that on October 9 the table contained 13.7 million rows, but the Data_date column had only one distinct value (October 7). After the data was replaced with October 8 entries, the stale statistics caused the optimizer to continue estimating a single row.

Solution: Delete and Lock Statistics

Removing and locking the statistics for the temporary tables forces the optimizer to use dynamic sampling, yielding accurate cardinalities.

Exec dbms_stats.delete_table_stats('CRM','B_S_CUST');
Exec dbms_stats.lock_table_stats('CRM','B_S_CUST');
Exec dbms_stats.delete_table_stats('CRM','P_CUST_STAT');
Exec dbms_stats.lock_table_stats('CRM','P_CUST_STAT');

Dynamic Sampling for Complex Queries

When queries contain intricate predicates such as status in ('COM','ERR') and v1 like '10%' or custom functions, traditional statistics may not help; dynamic sampling becomes the only viable option.

In a test with a 1‑million‑row table, the optimizer estimated 395 rows while the actual count was 11 113 (≈30× error). Using dynamic sampling at level 6 reduced the estimate to 16 595 rows, a discrepancy of less than 2×.

Enhancements in Oracle 12c

Setting the sampling level to 11 (or AUTO) allows the optimizer to estimate the cardinality of GROUP BY and join result sets with high accuracy. In a test where the result set contained 13 rows, the 12c optimizer estimated 12 rows, demonstrating near‑perfect estimation.

Conclusion

Oracle dynamic sampling is a valuable technique for improving execution‑plan quality in environments with volatile data or complex predicates, and the 12c enhancements further extend its usefulness to group‑by and join cardinality estimation.