Dynamic Sampling

http://www.oracle-base.com/articles/10g/PerformanceTuningEnhancements10g.php#dynamic_sampling

Dynamic sampling enables the server to improve performance by:

• Estimate single-table predicate selectivities where available statistics are missing or may lead to bad estimations.
• Estimate statatistics for tables and indexes with missing statistics.
• Estimate statatistics for tables and indexes with out of date statistics.

Dynamic sampling is controled by the OPTIMIZER_DYNAMIC_SAMPLING parameter which accepts values from "0" (off) to "10" (agressive sampling) with a default value of "2".

 At compile-time Oracle determines if dynamic sampling would improve query performance.

 If so it issues recursive statements to estimate the necessary statistics.

Dynamic sampling can be beneficial when:

• The sample time is small compared to the overall query execution time.
• Dynamic sampling results in a better performing query.
• The query may be executed multiple times.

In addition to the OPTIMIZER_DYNAMIC_SAMPLING system parameter the dynamic sampling level can be set using the

DYNAMIC_SAMPLING optimizer hint for specific queries like the following.

SELECT /*+ dynamic_sampling(emp 10) */

       empno, ename, job, sal

FROM   emp

WHERE  deptno = 30;

The results of dynamic sampling are repeatable provided no rows are inserted, updated or deleted from the sampled table. The OPTIMIZER_FEATURES_ENABLE parameter will turns off dynamic sampling if it is set to a version earlier than 9.2.0.