题目部分

在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。

 [email protected] > set pagesize 9999
 [email protected] > set line 9999
 [email protected] > col NAME format a40
 [email protected] > col KSPPDESC format a50
 [email protected] > col KSPPSTVL format a20
 [email protected] > 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)。

下面给出动态采样的一个示例：

创建表：

[email protected] > create table T_DS_20170601_LHR as select owner,object_type from all_objects;
2Table created.
[email protected] > SELECT COUNT(*) FROM T_DS_20170601_LHR;
4  COUNT(*)
5----------
6     75297

这里创建了一张普通表，没有做分析，在Hint中用0级来限制动态采样，此时CBO唯一可以使用的信息就是表存储在数据字典中的一些信息，如有多少个extent，有多少个block，但是这些信息是不够的。

 [email protected] > set autot traceonly explain
 [email protected] > 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相差甚远。下面用动态分析来查看一下：

 [email protected] > 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也可能错误地将结果判断的程度扩大化，例如：

 [email protected] > delete from T_DS_20170601_LHR;
 275297 rows deleted.
 [email protected] > COMMIT;
 4Commit complete.
 [email protected] > 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---------------------------------------------------------------------------------------
[email protected] > 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私聊。

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● QQ：646634621  QQ群：618766405

● 提供OCP、OCM和高可用部分最实用的技能培训

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

DBA宝典

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。