Oracle 12c – Guide to Dynamic Sampling for Performance Tuning

index-tuningoracleoracle-12cperformanceperformance-tuning

The optimizer will attempt to use default database statistics in preference to dynamic statistics. In some cases, oracle will auto use dynamic statistics.

  • parallel execution
  • missing statistics
  • stale statistics
  • insufficient statistics
  • have sql plan directives

My question is if a SQL statement with highly selective filters on column that has missing index statistics, will it benefit from dynamic sampling?

Best Answer

The amount of benefit from dynamic sampling will depend on the degree of cardinality estimate errors and how the query plan changes after you correct those errors. It is possible that your query will benefit and a good next step for you is to add a DYNAMIC_SAMPLING hint in your query and to observe how the EXPLAIN PLAN changes. However, a better solution would be to just fix your statistics. All queries will benefit from that instead of just the ones that you tune by hand.

In the workloads that I've observed, the queries that benefit the most from dynamic sampling go the other way: the optimizer estimates that very few rows will be returned from a large table which leads to nested loop joins and index use throughout the plan. If the optimizer knew that the large table returned many rows it would pick a better plan. Suppose the following query returns 100% of the data in X_TABLE:

SELECT COUNT(*)
FROM X_TABLE
WHERE NULLIF(X_COLUMN.NETWORKED_ID, 'X') IS NOT NULL;

On my machine Oracle uses a cardinality estimate of 5% of the rows from the table. This is a default cardinality estimate used in some cases when the optimizer doesn't have enough information. If I add a dynamic sampling hint then the estimate is fixed:

SELECT /*+ DYNAMIC_SAMPLING(X_TABLE 10) */ COUNT(*)
FROM X_TABLE
WHERE NULLIF(X_COLUMN.NETWORKED_ID, 'X') IS NOT NULL;

My rule of thumb is to use a sampling level of 10 for small tables and to use something less than 10 for larger tables. If you use 10 then Oracle will read all blocks from the table before generating an EXPLAIN PLAN.

Related Question