专栏首页小麦苗的DB宝专栏【DB笔试面试631】在Oracle中,什么是动态采样(Dynamic Sampling)?

【DB笔试面试631】在Oracle中,什么是动态采样(Dynamic Sampling)?

题目部分

在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程序员面试笔试宝典》,作者:小麦苗

本文分享自微信公众号 - DB宝(xiaomaimiaolhr),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-09-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 查看虚拟机版本

    二、 左侧没有终端图标的情况,可以搜索按钮(图中左侧第一个),输入termial,打开终端

    小麦苗DBA宝典
  • 【OCP最新题库解析(052)--题27】Your database is configured in archivelog

    Which clause or clauses ensure that no media recovery is required when the table...

    小麦苗DBA宝典
  • 【DB笔试面试627】在Oracle中,对表执行COMMENT(注释)操作需要什么权限?

    Oracle的COMMENT语句可以给一个列、表、视图或快照添加一个最多2K字节的注释。注释被存储在数据字典中,并且可以通过数据字典视图DBA_COL_C...

    小麦苗DBA宝典
  • Leetcode 48 Rotate Image

    You are given an n x n 2D matrix representing an image. Rotate the image by 90...

    triplebee
  • LWC 68: 766. Toeplitz Matrix

    思路: 对角线遍历,注意对角线的性质:当前元素为matrix[i][j], 下一元素为matrix[i+1][j+1]。

    用户1147447
  • 一起读源码之zookeeper(1) -- 启动分析

    从本文开始,不定期分析一个开源项目源代码,起篇从大名鼎鼎的zookeeper开始。 为什么是zk,因为用到zk的场景实在太多了,大部分耳熟能详的分布式系统都有...

    用户1177380
  • 结构化的室内场景建模_预处理

    上篇文章,我介绍了 "Structured Indoor Modeling" 这篇论文提供的数据集。

    无雨森
  • 玩爆EDUP智能插座Part1

    智能插座实现了远程开启/关闭连入的设备,的确是一款懒人神器!其实现原理就是通过WiFi模块实现对继电器的开启或者关闭。EDUP智能插座通过用户提供的凭证连接家庭...

    FB客服
  • Set Matrix Zeroes

    问题:将数组中的某个值为0的元素所在行和列的其他值都为0 分析;遍历数组找到某一值为0然后遍历他的上下左右直到边界,要用while而不能用搜索,因为搜索过去新节...

    用户1624346
  • sparkstreaming的状态计算-updateStateByKey源码

    转发请注明原创地址:https://www.cnblogs.com/dongxiao-yang/p/11358781.html

    sanmutongzi

扫码关注云+社区

领取腾讯云代金券