oracle10g的dynamic sampling

dynamic sampling主要是用来改善oracle的性能，使其得到更为正确的执行计划。oracle是通过optimizer_dynamic_sampling这个参数实现动态采样，在9i里面dynamic sampling需要在多表关联的语句里面才会起作用。在10g里面optimizer_dynamic_sampling在2及以上级别时单表就能dynamic sampling。

[@more@]

oracle文档上对于该参数的介绍如下：

You control dynamic sampling with the OPTIMIZER_DYNAMIC_SAMPLING parameter, which can be set to a value from 0 to 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.

Dynamic sampling is repeatable if no rows have been inserted, deleted, or updated in the table being sampled.

The parameter OPTIMIZER_FEATURES_ENABLE turns off dynamic sampling if set to a version prior to 9.0.2.

另外使用DYNAMIC_SAMPLING hint同样可以做动态采样：

enables dynamic sampling 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 table that has not been analyzed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12402/viewspace-1044816/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12402/viewspace-1044816/