合 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 | 当优化器探测到需要的采样时,对段段对象自动采样 | 自动决定 |
采样的数据块越多,得到的分析数据就越接近于真实,但同时伴随着资源消耗也越大。