9i,10g 优化器动态采样级别参数 -- optimizer_dynamic_sampling (统计优化)

             我们经常会碰到由于一些表没有分析导致执行计划错误的情况，但是Oracle9.2 中dynamic sampling的出现一定程度的减少了错误的产生。dynamic sampling针对没有分析过的表可以采样估计表的选择性，对于生产正确的执行计划有一定的帮助。 

           优化器动态采样级别参数 optimizer_dynamic_sampling ，是在Oracle9.2.0 版本之后才有的。设置级别可以由0到10， 0表示不进行动态采样， 10 表示采样级别最高。 Oracle9.2或以上(9i)下参数 optimizer_dynamic_sampling 默认值是1，Oracle10g 下默认值是2 。

Level 0:   Do not dynamically sample the table(s)   不进行动态采样 。

Level 1： Sample tables that have not been analyzed if there is more than one table in the query,the table in question has not been analyzed and it has no indexes,and the optimizer determines that the query plan would be affected based on the size of this objects  

Level 2:    Sample all unanalyzed tables referenced in teh query using default sampling amounts(small sample)

Level 3 -- Level 10     更详细的sample 。

              如果一个表没有统计信息，那么，CBO优化器将采用默认的统计数据，这样是很不准确的， 会直接导致错误的执行计划 。利用optimizer_dynamic_sampling参数，CBO优化器可收集引用对象上足够的统计信息得出更加准确的执行计划。 不过在Oracle9中需要有多表关联的时候optimizer_dynamic_sampling才会起作用，单个表不起作用。在10g中在optimizer_dynamic_sampling >=2  (10g默认是2) 的时候单个表就能实现优化器动态采样 。 

              我们知道，在Oracle9i, 10g中如果选择的是CHOOSE优化模式，那么在没有分析统计信息的时候，Oracle会选择使用RULE 方式，Oracle9i中，如果使用HINT强制用CBO的话，系统会读取一点信息来模拟实际的统计信息。当然Oracle10g 默认的优化模式是CBO ，所以除非人为更改，一般不会走RULE模式。 

               Oracle9i中如果关联的两个表都没有分析，那么  optimizer_dynamic_sampling 不起作用(可以sql trace 测试一下) ， 关联的表中有一个分析过了，那么优化器动态采样就起作用了。

              但是Oracle10g中如果两个表都没有分析，optimizer_dynamic_sampling 也会起作用。 以上应该都是由于Oracle10g中已经没有了基于RULE的优化器，在9i中如果一个查询中所有的表都没有分析，那么它会选择rule优化器，而忽略掉  dynamic  sampling  。10g中已经没有了rule ,   没有选择，只有 dynamic  sampling。 

 

--------------------------------------------------------------------------------- 

以下的解释还在理解中，有朋友能很好理解关于采样数据块的请帮忙解释一下 ： 

Level 1:    Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query;    (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view;   (3) this unanalyzed table has no indexes;    (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).  

Level 2:      Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is the default number of dynamic sampling blocks.

 

 

 

----------------------------------------------------------------------------------------------- 

更加详细的Level :

----------------------------------------------------------------------------------------------- 

Level 0:    Do not use dynamic sampling.
Level 1:    Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
Level 2:      Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is the default number of dynamic sampling blocks.
Level 3:      Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks.
Level 4:      Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks.
Level 5:      Apply dynamic sampling to all tables that meet the Level 4 criteria using 2 times the default number of dynamic sampling blocks.
Level 6:      Apply dynamic sampling to all tables that meet the Level 5 criteria using 4 times the default number of dynamic sampling blocks.
Level 7:      Apply dynamic sampling to all tables that meet the Level 6 criteria using 8 times the default number of dynamic sampling blocks.
Level 8:      Apply dynamic sampling to all tables that meet the Level 7 criteria using 32 times the default number of dynamic sampling blocks.
Level 9:     Apply dynamic sampling to all tables that meet the Level 8 criteria using 128 times the default number of dynamic sampling blocks.
Level 10:    Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

 

 

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

转载于:http://blog.itpub.net/35489/viewspace-592375/