ITPub博客

oracle10g的dynamic sampling

原创 作者:xsb 时间:2011-01-18 09:36:54 0 删除 编辑

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同样可以做动态采样:

SELECT /*+ dynamic_sampling(4) */ * 
FROM ...

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.
下一篇: Oracle RAC TAF [zt]
请登录后发表评论 登录
全部评论
  • 博文量
    66
  • 访问量
    3080913