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
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.
|
| 