Tips by Burleson
The main objective of dynamic sampling is more accurate
selectivity and cardinality estimates, which in turn helps
plans performance. Dynamic sampling is normally used to
estimate single-table predicate selectivity when collected
statistics cannot be used or are likely to lead to
significant errors in estimation. It is also used to
estimate table cardinality for tables without statistics or
for tables whose statistics are too out of date to trust.
The optimizer_dynamic_sampling initialization parameter
controls the number of blocks read by the dynamic sampling
query. The parameter can be set to a value from 0 to 10. In
10g, the default for this parameter is set to '2'.
A value of 0 means dynamic sampling will not be
performed. Increasing the value of the parameter results in
more aggressive dynamic sampling, in terms of both the type
of tables sampled (analyzed or un-analyzed) and the amount
of I/O spent on sampling.