Oracle Dynamic Sampling

Question

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?

Answer 1

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.

• shawn: Thanks for your answer!! Why"it is possible" benefit ?
• Joe Obbish: @shawn Adding the hint may not change the EXPLAIN PLAN generated by Oracle. Suppose that Oracle guesses that 5% of the rows will be returned from a table and it just so happens that 5% is the correct number. Dynamic sampling for that table will not help in that case.
• shawn: I got it. I have another question - When oracle optimizer calculate the cardinality estimate? It's my first time to know this action. So I want to know more. Thanks, you are very patient.
• shawn: I found it. After sending query block into the optimizer, it first does Query Transformer and then gets statistic from dictionary to do Estimation job, and finally send query and estimate to Plan Generator to generate a plan.