【DB笔试面试631】在Oracle中，什么是动态采样（Dynamic Sampling）？

• 2019 年 10 月 10 日
• 筆記

题目部分

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

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('%&parameter%');  Enter value for parameter: _optimizer_dyn_smp_blks  old   8: and lower(a.KSPPINM) like  lower('%&parameter%')  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而言，采样的数据块数量有差异，详见官方文档）不同采样级别的差异：

采样的数据块越多，得到的分析数据就越接近于真实，但同时伴随着资源消耗也越大。

引入动态采样有如下几方面的作用：

①　CBO依赖的是充分的统计信息，但是并不是每个用户都会非常认真、及时地去对每个表做分析。为了保证执行计划都尽可能地准确，Oracle需要使用动态采样技术来帮助CBO获取尽可能多的信息。

②　全局临时表。通常来讲，临时表的数据是不做分析的，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。

③　为了相对准确地估算出当目标SQL语句WHERE条件中出现有关联关系的列时整个WHERE条件的组合可选择率，进而能相对准确地估算出返回结果集的Cardinality。动态采样除了可以在段对象没有分析时，给CBO提供分析数据之外，还可以对不同列之间的相关性做统计。

④　在Oracle 11gR2开始，Oracle对动态采样进行了增强。在Oracle提供的增强特性中，对于并行或大表的复杂条件，即使表上存在统计信息，Oracle也会开启动态采样的功能，试图来更精准的评估返回结果集的记录数，并且自行定义动态采样的级别，Oracle会忽略OPTIMIZER_DYNAMIC_SAMPLING参数或提示Hint的DYNAMIC_SAMPLING值，而自行决定采样级别，如下所示：

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';”或关闭表的并行来屏蔽该动态采样的增强特性。

动态采样的一些缺点如下所示：

①　采样的数据块有限，对于海量数据的表，结果难免有偏差。

②　采样会消耗系统资源，特别是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)。

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

创建表：

SYS@orclasm > create table T_DS_20170601_LHR as select owner,object_type from all_objects;  Table created.  SYS@orclasm > SELECT COUNT(*) FROM T_DS_20170601_LHR;    COUNT(*)  ----------       75297  

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

SYS@orclasm > set autot traceonly explain  SYS@orclasm > select /*+dynamic_sampling(t 0) */ * from T_DS_20170601_LHR T;  Execution Plan  ----------------------------------------------------------  Plan hash value: 1447218522  ---------------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   | 16745 |   457K|    57   (0)| 00:00:01 |  |   1 |  TABLE ACCESS FULL| T_DS_20170601_LHR | 16745 |   457K|    57   (0)| 00:00:01 |  ---------------------------------------------------------------------------------------  

在没有做动态分析的情况下，CBO估计的记录数是16745条，与真实的75297相差甚远。下面用动态分析来查看一下：

SYS@orclasm > select * from T_DS_20170601_LHR;  Execution Plan  ----------------------------------------------------------  Plan hash value: 1447218522  ---------------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   | 78053 |  2134K|    58   (2)| 00:00:01 |  |   1 |  TABLE ACCESS FULL| T_DS_20170601_LHR | 78053 |  2134K|    58   (2)| 00:00:01 |  ---------------------------------------------------------------------------------------  Note  -----     - dynamic sampling used for this statement (level=2)  

上面的查询结果显示使用了Level 2级的动态采样，CBO 估计的结果是78053与75297很接近。需要注意的是，在没有动态采样的情况下，对于没有分析过的段，CBO也可能错误地将结果判断的程度扩大化，例如：

SYS@orclasm > delete from T_DS_20170601_LHR;  75297 rows deleted.  SYS@orclasm > COMMIT;  Commit complete.  SYS@orclasm > SELECT /*+DYNAMIC_SAMPLING(T 0) */ * FROM T_DS_20170601_LHR T;  Execution Plan  ----------------------------------------------------------  Plan hash value: 1447218522  ---------------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   | 16745 |   457K|    57   (0)| 00:00:01 |  |   1 |  TABLE ACCESS FULL| T_DS_20170601_LHR | 16745 |   457K|    57   (0)| 00:00:01 |  ---------------------------------------------------------------------------------------  SYS@orclasm > SELECT * FROM T_DS_20170601_LHR T;  Execution Plan  ----------------------------------------------------------  Plan hash value: 1447218522  ---------------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   |     1 |    28 |    57   (0)| 00:00:01 |  |   1 |  TABLE ACCESS FULL| T_DS_20170601_LHR |     1 |    28 |    57   (0)| 00:00:01 |  ---------------------------------------------------------------------------------------  Note  -----     - 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程序员面试笔试宝典》，作者：小麦苗