Donald K. Burleson Oracle Tips

Enabling Oracle Dynamic Sampling

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', automatically enabling dynamic sampling. Beware that the optimizer_features_enable parameter will turns off dynamic sampling if it is set to a version earlier than 9.2.0.

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.

It is recommended that you set optimizer_dynamic_sampling = 4 or use the dynamic_sampling (4) hint. Always test your SQL in a test environment before implementing it in production and be careful setting optimizer_dynamic_sampling to a value greater than 4 because there is overhead associated with run-time statistics sampling that may outweigh any performance gains from faster execution time.