optimizer_dynamic_sampling

  

参数：
optimizer_dynamic_sampling

基本概念：
动态采样技术是在Oracle 9i的版本开始有的功能，该功能可以对未进行分析的表进行不同程度的采样。

查看方法：
SQL>show parameter optimizer

为了便于快速掌握和理解，仅截取官方部分信息并翻译：



按照上面的表格来说，10G以上的版本一般默认是在level2的采样水平：

Dynamic Sampling Levels


The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter:


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).
翻译：满足如下条件的进行动态采样：
（1）在查询中至少存在一个未进行分析的表；
（2）未进行分析的表出现在关联查询或者是子查询中；
（3）该未经分析的表没有索引
（4）未经分析的表所用的数据块数量超过默认的动态采样数据块数（32块）

Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
翻译：对所有未经分析的表应用动态采样，采样的数据块数目是默认采样数目的2倍。

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. For unanalyzed tables, the number of blocks sampled is two times 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. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.


Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.


Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.


The sampling levels are as follows if the dynamic sampling level for a table is set using the DYNAMIC_SAMPLING optimizer hint:


Level 0: Do not use dynamic sampling.


Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).


Levels 2, 3, 4, 5, 6, 7, 8, and 9: The number of blocks sampled is 2, 4, 8, 16, 32, 64, 128, or 256 times the default number of dynamic sampling blocks respectively.


Level 10: Read all blocks in the table.




 从以上描述中，我们不难看出，动态采样的级别越高，优化器的估算值会越来越准确，越来越接近实际值，更容易做出最优的路径选择。既然有如此的好处，也必然会付出相应的代价：消耗资源随着采样等级的升高而增多，负载会逐级增加。

 对于OLTP系统来说，此值一般设置为默认值或者是最高到4即可，不宜开的过高。
 对于OLAP或者数据仓储来说，可以开的高一些，尤其是一些数据量很庞大的数据库，低级别的动态采样可能会造成偏离实际值比较大的误差。