Optimizer Sampling

Oracle9i New Feature Series: Dynamic Optimizer Statistics Sampling

Dynamic sampling introduced in Oracle9i Release 2 improves server performance by determining more accurate selectivity and cardinality estimates for queries.

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 table cardinality for tables without statistics or for tables whose statistics are too out of date to trust.

Dynamic sampling is controlled by the initialization parameter OPTIMIZER_DYNAMIC_SAMPLING. It could have a value between 0 and 10.

A value of 0 means dynamic sampling will not be done.
A value of 1 (the default) means dynamic sampling will be performed if all of the following conditions are true:
There is more than one table in the query.
Some table has not been analyzed and has no indexes.
The optimizer determines that a relatively expensive table scan would be required for this unanalyzed table.
Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling.

 

To read more:

 

DYNAMIC_SAMPLING hint

 

OPTIMIZER_DYNAMIC_SAMPLING the parameter

 

Enabling Dynamic Statistic Sampling  

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.