Use of Dynamic Sampling while estimating Statistics

The purpose of dynamic sampling is to improve query performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. These more accurate estimates allow the optimizer to produce better performing plans.

You can use dynamic sampling to:

•Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.

•Estimate statistics for tables and relevant indexes without statistics.

•Estimate statistics for tables and relevant indexes whose statistics are too out of date to trust.

The dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter.

The default value of OPTIMIZER_DYNAMIC_SAMPLING is depend on settings of OPTIMIZER_FEATURES_ENABLE.

•If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2

•If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1

•If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0 that means dynamic sampling feature is disabled.

How dynamic Sampling Works
--------------------------------
The primary performance attribute is compile time. Oracle determines at compile time whether a query would benefit from dynamic sampling. If so, a recursive SQL statement is issued to scan a small random sample of the table's blocks, and to apply the relevant single table predicates to estimate predicate selectivities. The sample cardinality can also be used, in some cases, to estimate table cardinality. Any relevant column and index statistics are also collected.

Depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, a certain number of blocks are read by the dynamic sampling query.

When to Use Dynamic Sampling
------------------------------------------------
For a query that normally completes quickly (in less than a few seconds), you will not want to incur the cost of dynamic sampling. However, dynamic sampling can be beneficial under any of the following conditions:

•A better plan can be found using dynamic sampling.
•The sampling time is a small fraction of total execution time for the query.
•The query will be executed many times.

Dynamic sampling can be applied to a subset of a single table's predicates and combined with standard selectivity estimates of predicates for which dynamic sampling is not done.