♣
题目部分
在Oracle中，什么是动态采样（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。
1SYS@orclasm > set pagesize 9999
2SYS@orclasm > set line 9999
3SYS@orclasm > col NAME format a40
4SYS@orclasm > col KSPPDESC format a50
5SYS@orclasm > col KSPPSTVL format a20
6SYS@orclasm > SELECT a.INDX,
7 2 a.KSPPINM NAME,
8 3 a.KSPPDESC,
9 4 b.KSPPSTVL
10 5 FROM x$ksppi a,
11 6 x$ksppcv b
12 7 WHERE a.INDX = b.INDX
13 8 and lower(a.KSPPINM) like lower('%&parameter%');
14Enter value for parameter: _optimizer_dyn_smp_blks
15old 8: and lower(a.KSPPINM) like lower('%&parameter%')
16new 8: and lower(a.KSPPINM) like lower('%_optimizer_dyn_smp_blks%')
17
18 INDX NAME KSPPDESC KSPPSTVL
19---------- ---------------------------------------- -------------------------------------------------- --------------------
20 2082 _optimizer_dyn_smp_blks number of blocks for optimizer dynamic sampling 32
下表针对Oracle 11.2.0.4（对Oracle 10g而言，采样的数据块数量有差异，详见官方文档）不同采样级别的差异：
采样的数据块越多，得到的分析数据就越接近于真实，但同时伴随着资源消耗也越大。
引入动态采样有如下几方面的作用：
① CBO依赖的是充分的统计信息，但是并不是每个用户都会非常认真、及时地去对每个表做分析。为了保证执行计划都尽可能地准确，Oracle需要使用动态采样技术来帮助CBO获取尽可能多的信息。
② 全局临时表。通常来讲，临时表的数据是不做分析的，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。
③ 为了相对准确地估算出当目标SQL语句WHERE条件中出现有关联关系的列时整个WHERE条件的组合可选择率，进而能相对准确地估算出返回结果集的Cardinality。动态采样除了可以在段对象没有分析时，给CBO提供分析数据之外，还可以对不同列之间的相关性做统计。
④ 在Oracle 11gR2开始，Oracle对动态采样进行了增强。在Oracle提供的增强特性中，对于并行或大表的复杂条件，即使表上存在统计信息，Oracle也会开启动态采样的功能，试图来更精准的评估返回结果集的记录数，并且自行定义动态采样的级别，Oracle会忽略OPTIMIZER_DYNAMIC_SAMPLING参数或提示Hint的DYNAMIC_SAMPLING值，而自行决定采样级别，如下所示：
1SQL> select /*+ dynamic_sampling (my_table 2) */ * from my_table;
2
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 3006137970
6
7--------------------------------------------------------------------------------------------------------------
8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
9--------------------------------------------------------------------------------------------------------------
10| 0 | SELECT STATEMENT | | 9408K| 1704M| 4000 (2)| 00:00:01 | | | |
11| 1 | PX COORDINATOR | | | | | | | | |
12| 2 | PX SEND QC (RANDOM)| :TQ10000 | 9408K| 1704M| 4000 (2)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
13| 3 | PX BLOCK ITERATOR | | 9408K| 1704M| 4000 (2)| 00:00:01 | Q1,00 | PCWC | |
14| 4 | TABLE ACCESS FULL| MY_TABLE | 9408K| 1704M| 4000 (2)| 00:00:01 | Q1,00 | PCWP | |
15--------------------------------------------------------------------------------------------------------------
16
17Note
18-----
19 - dynamic sampling used for this statement (level=5)
在10053的Trace文件中会看到以下内容：
1Dynamic sampling level auto-adjusted from 2 to 5
可以通过“alter session set "_fix_control"='7452863:OFF';”或关闭表的并行来屏蔽该动态采样的增强特性。
动态采样的一些缺点如下所示：
① 采样的数据块有限，对于海量数据的表，结果难免有偏差。
② 采样会消耗系统资源，特别是OLTP数据库，尤其不推荐使用动态采样。动态采样也需要额外的消耗数据库资源。在OLTP系统中，SQL被反复执行，变量被绑定，硬解析很少，在这样一个环境中，是不宜使用动态采样的。在OLAP或者数据仓库环境下，SQL执行消耗的资源要远远大于SQL解析，那么让解析在消耗多一点资源做一些动态采样分析，从而做出一个最优的执行计划是非常值得的。所以，一般在OLAP或者数据仓库环境中，将动态采样的level设置为3或者4比较好。相反，在OLTP系统下，尽量避免使用动态采样。
③ 存在部分Bug。例如，Bug 17760686，当某个查询涉及到分区表时，动态采样可能会估算出很小甚至是0的结果集。
在执行计划的Note部分若有“dynamic sampling used for this statement (level=2)”，则表示Oracle使用了level为2的动态采样。详细情况参考：Bug 17760686 - Bad Cardinality estimation with dynamic sampling (文档 ID 17760686.8)。
下面给出动态采样的一个示例：
创建表：
1SYS@orclasm > create table T_DS_20170601_LHR as select owner,object_type from all_objects;
2Table created.
3SYS@orclasm > SELECT COUNT(*) FROM T_DS_20170601_LHR;
4 COUNT(*)
5----------
6 75297
这里创建了一张普通表，没有做分析，在Hint中用0级来限制动态采样，此时CBO唯一可以使用的信息就是表存储在数据字典中的一些信息，如有多少个extent，有多少个block，但是这些信息是不够的。
1SYS@orclasm > set autot traceonly explain
2SYS@orclasm > select /*+dynamic_sampling(t 0) */ * from T_DS_20170601_LHR T;
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 1447218522
6---------------------------------------------------------------------------------------
7| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
8---------------------------------------------------------------------------------------
9| 0 | SELECT STATEMENT | | 16745 | 457K| 57 (0)| 00:00:01 |
10| 1 | TABLE ACCESS FULL| T_DS_20170601_LHR | 16745 | 457K| 57 (0)| 00:00:01 |
11---------------------------------------------------------------------------------------
在没有做动态分析的情况下，CBO估计的记录数是16745条，与真实的75297相差甚远。下面用动态分析来查看一下：
1SYS@orclasm > select * from T_DS_20170601_LHR;
2Execution Plan
3----------------------------------------------------------
4Plan hash value: 1447218522
5---------------------------------------------------------------------------------------
6| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
7---------------------------------------------------------------------------------------
8| 0 | SELECT STATEMENT | | 78053 | 2134K| 58 (2)| 00:00:01 |
9| 1 | TABLE ACCESS FULL| T_DS_20170601_LHR | 78053 | 2134K| 58 (2)| 00:00:01 |
10---------------------------------------------------------------------------------------
11Note
12-----
13 - dynamic sampling used for this statement (level=2)
上面的查询结果显示使用了Level 2级的动态采样，CBO 估计的结果是78053与75297很接近。需要注意的是，在没有动态采样的情况下，对于没有分析过的段，CBO也可能错误地将结果判断的程度扩大化，例如：
1SYS@orclasm > delete from T_DS_20170601_LHR;
275297 rows deleted.
3SYS@orclasm > COMMIT;
4Commit complete.
5SYS@orclasm > SELECT /*+DYNAMIC_SAMPLING(T 0) */ * FROM T_DS_20170601_LHR T;
6Execution Plan
7----------------------------------------------------------
8Plan hash value: 1447218522
9---------------------------------------------------------------------------------------
10| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
11---------------------------------------------------------------------------------------
12| 0 | SELECT STATEMENT | | 16745 | 457K| 57 (0)| 00:00:01 |
13| 1 | TABLE ACCESS FULL| T_DS_20170601_LHR | 16745 | 457K| 57 (0)| 00:00:01 |
14---------------------------------------------------------------------------------------
15SYS@orclasm > SELECT * FROM T_DS_20170601_LHR T;
16Execution Plan
17----------------------------------------------------------
18Plan hash value: 1447218522
19---------------------------------------------------------------------------------------
20| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
21---------------------------------------------------------------------------------------
22| 0 | SELECT STATEMENT | | 1 | 28 | 57 (0)| 00:00:01 |
23| 1 | TABLE ACCESS FULL| T_DS_20170601_LHR | 1 | 28 | 57 (0)| 00:00:01 |
24---------------------------------------------------------------------------------------
25Note
26-----
27 - dynamic sampling used for this statement (level=2)
在没有采用动态分析的情况下，CBO对T_DS_20170601_LHR表估计的还是16745行记录，但是用动态分析就显示1条记录。而表中的数据在查询之前就已经被删除掉了。出现这种情况的原因是因为高水位。虽然表的数据已经删除，但是表分配的EXTENT和BLOCK没有被回收，所以在这种情况下CBO依然认为有那么多的数据在表中。
& 说明：
有关动态采样的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2139284/
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
