Oracle 11g中dynamic sampling自动调节（auto-adjusted）机制
Oracle 11g中dynamic sampling自动调节（auto-adjusted）机制
对Oracle而言，基于成本优化器CBO工作的基础是系统、对象统计量和CPU成本计算公式。而大多数情况下，收集统计量是一个异步单独的过程。无论是在9i，还是目前主流11g，发挥CBO作用都需要我们有一个统计量收集或者分析的过程。
在9i时代，CBO和RBO是交替工作的。Oracle是不会“主动”的将对象统计量进行收集（那个时期也成为分析analyze）。系统调优人员也大都是解决统计量缺失、CBO优化器缺陷问题，目前能看到的很多hint也大都在9i时期编写系统。
到了10g之后，两个情况让CBO彻底取代RBO。一个是CBO成为Oracle默认的优化器使用类型，SQL语句默认情况下就使用CBO进行解析。另一个是自动统计量夜间收集作业的推出，Oracle可以自动找时间进行统计量收集。应该说，大部分的SQL语句从10g之后都是使用CBO进行执行计划生成。
但是，无论如何，都存在统计量不及时的情况。一个新创建的数据表或者发生大量变化的数据集合，如果没有手工的数据收集，CBO总是工作在统计量缺失或者陈旧统计量的情况下。
RHEL/CentOS下 Oracle 11g客户端配置
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等待事件进行跟踪处理。下面利用这种方法进行检查。
更多详情见请继续阅读下一页的精彩内容：
|
评论暂时关闭