Oracle 11g中dynamic sampling自动调节(auto-adjusted)机制

1、从Dynamic Sampling谈起
 
 

为了应对统计量缺失的情况,Oracle推出了Dynamic Sampling(12c之后称为Dynamic Statistic)动态采样收集的技术策略。简单点说,如果数据表等对象没有统计量存在,Oracle有需要统计量生成执行计划,数据库会进行一次临时性的数据收集动作。根据不同的采用比例,Oracle实时的采集部分数据块,应用查询条件进行小规模试算。最后根据统计规则,将结果集合放大后,作为结果集合统计量和row source纳入到CBO体系里面。
 
从SQL执行的角度看,Dynamic Sampling给Oracle用户带来两个方面好处:
 
ü  关联条件SQL语句精准估算。对于常见的统计量,如选择率、数据分布,都是以单列为中心进行计数的。如果SQL语句中对应的是两个相关联的where条件,那么单列统计量计算出来的结果集合往往是偏小。如果采用Dynamic Sampling,采用动作是实时进行计算的,可以消除关联where条件影响;
 
ü  CBO普遍应用。应该说,没有统计量,CBO是没有任何工作的可能的!Dynamic Sampling虽然不能给Oracle提供出最精确的统计量,但是起码可以让CBO运行提供基本条件;
 
从不利的方面看,Dynamic Sampling也带来一些问题。比如,和驻留在数据字典中的统计量不同,Dynamic Sampling并不会将收集采用值保存在数据字典里面,而是一次SQL就进行一次收集动作。采样比例如果高,就意味着parse前的动态收集动作消耗资源高。如果采样比例低,性能虽然可以好一些,但是存在着“低效”执行计划生成的纪律。
 
这其实是一个矛盾。如果数据表比较小,采样比例小一些,执行计划效率低一些其实也没有过多问题。但是如果数据表很大,SQL语句属于关键SQL,这样低效的执行计划其实就意味着风险。
 
Oracle中,是通过optimizer_dynamic_sampling设置采样比例。11gR2中,默认为2。
 
 

SQL> show parameter dyn
 
NAME                                TYPE        VALUE
 
------------------------------------ ----------- ------------------------------
 
optimizer_dynamic_sampling          integer    2
 
 

为了应对由于动态采样带来的问题,在11g里面针对特殊的数据对象和SQL场景,Oracle CBO采取了自动调节采样率的优化策略。
 
下面我们进行相应的实验。
 
 

2、环境介绍
 
 

我们使用Oracle 11gR2版本进行测试,默认的采样控制参数是2。
 
 

SQL> select * from v$version;
 
 

BANNER
 
-------------------------------------------
 
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 
PL/SQL Release 11.2.0.3.0 - Production
 
CORE    11.2.0.3.0      Production
 
TNS for Linux: Version 11.2.0.3.0 - Production
 
NLSRTL Version 11.2.0.3.0 - Production
 
 

SQL> show parameter dyn
 
NAME                                TYPE        VALUE
 
-------------- ----------- ----------------
 
optimizer_dynamic_sampling          integer    2
 
 

创建一个大数据表,体积为2G。
 
 

SQL> select tablespace_name, bytes/1024/1024/1024 from dba_segments where owner='SYS' and segment_name='T';
 
 

TABLESPACE_NAME                BYTES/1024/1024/1024
 
------------------------------ --------------------
 
TESTTBL                                2.099609375
 
 

Owner列上面创建索引IDX_T_OWNER。
 
 

3、自动sampling adjusted
 
 

当前采用取值为2,由于数据表比较大,采用并行查询策略。
 
 

SQL> explain plan for select /*+ parallel(t, 2) */count(*) from t;
 
 

Explained
 
 

SQL> select * from table(dbms_xplan.display);
 
 

PLAN_TABLE_OUTPUT
 
--------------------------------------------------------------------------------
 
Plan hash value: 3126468333
 
--------------------------------------------------------------------------------
 
| Id  | Operation              | Name    | Rows  | Cost (%CPU)| Time    |    T
 
--------------------------------------------------------------------------------
 
|  0 | SELECT STATEMENT      |          |    1 | 41414  (1)| 00:08:17 |
 
|  1 |  SORT AGGREGATE        |          |    1 |            |          |
 
|  2 |  PX COORDINATOR      |          |      |            |          |
 
|  3 |    PX SEND QC (RANDOM) | :TQ10000 |    1 |            |          |  Q1,
 
|  4 |    SORT AGGREGATE    |          |    1 |            |          |  Q1,
 
|  5 |      PX BLOCK ITERATOR |          |    23M| 41414  (1)| 00:08:17 |  Q1,
 
|  6 |      TABLE ACCESS FULL| T        |    23M| 41414  (1)| 00:08:17 |  Q1,
 
--------------------------------------------------------------------------------
 
Note
 
-----
 
  - dynamic sampling used for this statement (level=5)
 
 

17 rows selected
 
 

新创建的数据表,没有显示的进行数据收集动作。所以Oracle会去选择动态采样Dynamic Sampling策略。在使用parallel和动态采样的SQL语句中,我们使用explain plan生成了执行计划,但是在末尾的note中,发现了不确定的部分。
 
当前dynamic sampling设置参数为2,但是在执行计划上显示的是5。取值越高,意味着更高的采样比例,进而意味着更准确地执行计划生成。
 
从其他的场景,比如非并行开启或者小数据表查询,我们都没有看到这样的现象。说明:这个是针对Oracle特殊SQL场景下的一种优化措施。研究CBO行为最好的方式是使用10053等待事件进行跟踪处理。下面利用这种方法进行检查。

相关推荐