dynamic sampling

dynamic sampling

dynamic sampling对于我们来说其实不算太陌生，从9i r2开始，dynamic sampling其实已经不动声色地融入到数据库中了。

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

 

dynamic sampling分为10个级别，从0－10，由参数optimizer_dynamic_sampling控制

 

下面是取自" Performance Tuning Guide and Reference "的对10种级别的定义

 

• 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.

dynamic_sampling hint的级别定义如下

 

• Level 0: Do not use dynamic sampling.
• Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).
• Level 2: The number of blocks sampled is 2 times the default number of dynamic sampling blocks.
• Level 3: The number of blocks sampled is 4 times the default number of dynamic sampling blocks.
• Level 4: The number of blocks sampled is 8 times the default number of dynamic sampling blocks.
• Level 5: The number of blocks sampled is 16 times the default number of dynamic sampling blocks.
• Level 6: The number of blocks sampled is 32 times the default number of dynamic sampling blocks.
• Level 7: The number of blocks sampled is 64 times the default number of dynamic sampling blocks.
• Level 8: The number of blocks sampled is 128 times the default number of dynamic sampling blocks.
• Level 9: The number of blocks sampled is 256 times the default number of dynamic sampling blocks.
• Level 10: Read all blocks in the table.

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

转载于:http://blog.itpub.net/7728585/viewspace-616120/