合 Oracle统计信息之动态采样（Dynamic Sampling）
👉 本文共约2592个字，系统预计阅读时间或需10分钟。
什么是动态采样（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 | 当优化器探测到需要的采样时，对段段对象自动采样 | 自动决定 |
采样的数据块越多，得到的分析数据就越接近于真实，但同时伴随着资源消耗也越大。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
