xsb Oracle Blog

暂无签名

  • 博客访问: 3047269
  • 博文数量: 343
  • 用 户 组: 普通用户
  • 注册时间: 1970-01-01 08:00
个人简介

鏆傛棤浠嬬粛

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(343)

文章存档

2012年(1)

2011年(6)

2010年(2)

2009年(6)

2008年(32)

2007年(28)

2006年(182)

2005年(78)

2004年(8)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

oracle10g的dynamic sampling 2011-01-18 09:36:54

分类: Oracle

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.
阅读(3118) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册