合 Oracle统计信息之动态采样（Dynamic Sampling）
什么是动态采样（Dynamic Sampling）？
对于没有收集统计信息的表，Oracle为了能够得到相对准确的执行计划，会在执行SQL之前对SQL语句涉及到的表做动态采样（Dynamic Sampling，从Oracle 11.2.0.4开始称之为Dynamic Statistic）。
有两种方法可以开启动态采样：
（1）将参数OPTIMIZER_DYNAMIC_SAMPLING的值设为大于或等于1。从Oracle 10g开始，该值默认为2，若设置为0，则禁用动态采样。
（2）使用动态采样的Hint：DYNAMIC_SAMPLING(T LEVEL)。该Hint表示对目标表T强制使用等级为参数level指定值的动态采样。
默认采样数据块数量受隐含参数“_OPTIMIZER_DYN_SMP_BLKS”的控制，其默认值是32，表示动态采样时默认采样数据块数量为32。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SYS@orclasm > set pagesize 9999 SYS@orclasm > set line 9999 SYS@orclasm > col NAME format a40 SYS@orclasm > col KSPPDESC format a50 SYS@orclasm > col KSPPSTVL format a20 SYS@orclasm > SELECT a.INDX, 2 a.KSPPINM NAME, 3 a.KSPPDESC, 4 b.KSPPSTVL 5 FROM x$ksppi a, 6 x$ksppcv b 7 WHERE a.INDX = b.INDX 8 and lower(a.KSPPINM) like lower('%¶meter%'); Enter value for parameter: _optimizer_dyn_smp_blks old 8: and lower(a.KSPPINM) like lower('%¶meter%') new 8: and lower(a.KSPPINM) like lower('%_optimizer_dyn_smp_blks%') INDX NAME KSPPDESC KSPPSTVL ---------- ---------------------------------------- -------------------------------------------------- -------------------- 2082 _optimizer_dyn_smp_blks number of blocks for optimizer dynamic sampling 32 |
下表针对Oracle 11.2.0.4（对Oracle 10g而言，采样的数据块数量有差异，详见官方文档）不同采样级别的差异：
| 层级 | 优化器何时使用动态采样 | 动态采样数据块数量 |
|---|---|---|
| 0 | 不做动态采样分析 | 0 |
| 1 | Oracle对没有分析的表进行动态采样，但需要同时满足以下3个条件：（1）SQL中至少有一个未分析的非分区表（2）未分析的表没有索引（3）未分析的表占用的数据块要大于动态采样的数据块（32个） | 32 |
| 2 | 对所有的未分析表做分析，动态采样的默认级别，默认动态采样数据块数为64 | 64 |
| 3 | 采样的表包含满足Level 2定义的所有表，同时包括，那些谓词有可能潜在地需要动态采样的表 | 64 |
| 4 | 采样的表包含满足Level 3定义的表，同时还包括一些表，它们包含一个单表的谓词会引用另外的2个列或者更多的列 | 64 |
| 5 | 采样的表包含满足Level 4定义的表 | 128 |
| 6 | 采样的表包含满足Level 4定义的表 | 256 |
| 7 | 采样的表包含满足Level 4定义的表 | 512 |
| 8 | 采样的表包含满足Level 4定义的表 | 1024 |
| 9 | 采样的表包含满足Level 4定义的表 | 4086 |
| 10 | 采样的表包含满足Level 4定义的表，使用动态采样对所有数据块做动态分析 | 所有块 |
| 11 | 当优化器探测到需要的采样时，对段段对象自动采样 | 自动决定 |
采样的数据块越多，得到的分析数据就越接近于真实，但同时伴随着资源消耗也越大。
引入动态采样有如下几方面的作用：
① CBO依赖的是充分的统计信息，但是并不是每个用户都会非常认真、及时地去对每个表做分析。为了保证执行计划都尽可能地准确，Oracle需要使用动态采样技术来帮助CBO获取尽可能多的信息。
② 全局临时表。通常来讲，临时表的数据是不做分析的，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。
③ 为了相对准确地估算出当目标SQL语句WHERE条件中出现有关联关系的列时整个WHERE条件的组合可选择率，进而能相对准确地估算出返回结果集的Cardinality。动态采样除了可以在段对象没有分析时，给CBO提供分析数据之外，还可以对不同列之间的相关性做统计。
④ 在Oracle 11gR2开始，Oracle对动态采样进行了增强。在Oracle提供的增强特性中，对于并行或大表的复杂条件，即使表上存在统计信息，Oracle也会开启动态采样的功能，试图来更精准的评估返回结果集的记录数，并且自行定义动态采样的级别，Oracle会忽略OPTIMIZER_DYNAMIC_SAMPLING参数或提示Hint的DYNAMIC_SAMPLING值，而自行决定采样级别，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SQL> select /*+ dynamic_sampling (my_table 2) */ * from my_table; Execution Plan ---------------------------------------------------------- Plan hash value: 3006137970 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9408K| 1704M| 4000 (2)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 9408K| 1704M| 4000 (2)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 9408K| 1704M| 4000 (2)| 00:00:01 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| MY_TABLE | 9408K| 1704M| 4000 (2)| 00:00:01 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=5) -- 在10053的Trace文件中会看到以下内容： Dynamic sampling level auto-adjusted from 2 to 5 |
可以通过“alter session set "_fix_control"='7452863:OFF';”或关闭表的并行来屏蔽该动态采样的增强特性。
