一直以来，我都以为ORACLE的动态采样只有在没有统计信息的时候才有用，今天看了TOM老人家得一篇大作，受益匪浅啊。
dynamic sampling从ORACLE9iR2开始就可以使用了，CBO在执行hard parse的时候使用动态采样来收集相关表的统计信息同时来纠正自己的猜测数据。这个过程只在hard parse中产生，并且被用来生成更加准确的统计信息以供CBO使用，因此被称作动态采样。
优化器使用很多输入参数来产生合适的执行计划，比如它使用表上的约束，系统统计信息，查询涉及的相关对象的统计信息。优化器使用相应的统计信息来估算基数，而基数正是成本计算的最重要的变量。也可以说基数估算的正确与否，直接影响到执行计划的选择。这就是引入动态采样的直接动机--帮助优化器估算正确的基数，从而得到正确的执行计划。
动态采样提供了11个级别可以供使用（从0到10），后面我会详细解释每一个级别。ORACLE 9i R2的默认动态采样级别是1，而10g 11g的默认级别是2。
1 使用动态采样的方法
数据库级别可以调整optimizer_dynamic_sampling参数，会话级别可以使用alter session
在查询里可以使用dynamic_sampling提示
2 几个例子
针对没有统计信息的表：
SQL> create table t
2 as
3 select owner, object_type
4 from all_objects
5 /
Table created.
SQL> select count(*) from t;
COUNT(*)
------------------------
68076
下面的查询禁用了动态采样：
SQL> set autotrace traceonly explain
SQL> select /*+ dynamic_sampling(t 0) */ * from t;
Execution Plan
------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16010 | 437K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 16010 | 437K| 55 (0)| 00:00:01 |
--------------------------------------------------------------------------
下面的查询启用了动态采样：
SQL> select * from t;
Execution Plan
------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77871 | 2129K| 56 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 77871 | 2129K| 56 (2)| 00:00:01 |
--------------------------------------------------------------------------
Note
------------------------------------------
- dynamic sampling used for this statement
请注意，启用动态采样后的执行计划的基数（77871）更加接近实际的行数（68076），因此这个执行计划更可靠一点。
SQL>set autot off
当然也可能估算出差别很大的基数：
SQL> delete from t;
68076 rows deleted.
SQL> commit;
Commit complete.
SQL> set autotrace traceonly explain
禁用动态采样：
SQL> select /*+ dynamic_sampling(t 0) */ * from t;
Execution Plan
------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16010 | 437K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 16010 | 437K| 55 (0)| 00:00:01 |
--------------------------------------------------------------------------
启用动态采样：
SQL> select * from t;
Execution Plan
-----------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 1 | 28 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
---------------------------------------
- dynamic sampling used for this statement
实际上，表里现在没有数据，但是由于使用了delete而不是truncate,数据库并没有重置HWM，因此数据库猜错了结果，启用动态采样的基数要远远强于没有采样的。
以上两个例子，都是在没有对表进行统计的时候得到的，那么在有统计信息的情况下，动态采样还能有作用么？请考虑以下的例子，假设EMP表里有两个字段，birth_day date 和星座（varchar2）,假设其中有1440000行数据(当然数据库表设计没有遵循第二范式)。在这种情况下，查询出生在1月份，并且星座是天平座的人，CBO会估算出多少行呢？答案是1W，但是实际上一行都不会返回，在这种数据库表设计有严重问题的时候，动态采样再一次向我们展示了其魅力所在。
SQL> create table t
2 as select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1,
3 decode( mod(rownum,2), 0, 'Y', 'N' ) flag2, a.*
4 from all_objects a
5 /
Table created.
SQL > create index t_idx on t(flag1,flag2);
Index created.
SQL > begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=>'for all indexed columns size 254' );
5 end;
6 /
PL/SQL procedure successfully completed.
这里做了统计，有直方图了，以下是一些统计信息：
SQL> select num_rows, num_rows/2,
num_rows/2/2 from user_tables
where table_name = 'T';
NUM_ROWS NUM_ROWS/2 NUM_ROWS/2/2
-------- ---------- ------------
68076 34038 17019
SQL> set autotrace traceonly explain
SQL> select * from t where flag1='N';
Execution Plan
------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33479 | 3432K| 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 33479 | 3432K| 292 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG1"='N')
SQL> select * from t where flag2='N';
Execution Plan
----------------------------
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34597 | 3547K| 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 34597 | 3547K| 292 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG2"='N')
前两个执行计划都是准确的，会返回34597行数据，大概是表里数据的一半。接着执行以下查询：
SQL> select * from t where flag1 = 'N' and flag2 = 'N';
Execution Plan
----------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17014 | 1744K| 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 17014 | 1744K| 292 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------
1 - filter("FLAG1" = 'N' AND "FLAG2" = 'N')
这时会返回表里四分之一左右的数据，但是实际上应该是一行数据都没有，并且由于错误的基数估算导致错误的执行计划。再看看下面的这个查询，强制CBO进行动态采样。
SQL> select /*+ dynamic_sampling(t 3) */ * from t where flag1 = 'N' and flag2 = 'N';
Execution Plan
-----------------------------
Plan hash value: 470836197
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 630 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 630 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------
2 - access("FLAG1"='N' AND "FLAG2"='N')
CBO估计会返回6行数据，并且进行了索引范围扫描，成本也很低，总体来说这个执行计划还令人满意。至于CBO估算出来的基数为什么是6，而不是0，这个解释起来比较复杂，在此就不详述了。
3 CBO动态采样级别：
level 0:不使用动态采样
level 1:在以下情况进行采样所有查询涉及的表:(1)查询语句里至少有一个未anylze过的表;(2)unanalyzed table连接到其他表或者出现在子查询中或者在不可合并的视图中；(3)这个表上没索引；(4)表数据块比进行动态采样的数据块多。
level 2:应用动态采样到所有unanalyzed table，表数据块至少是动态采样块的2倍。
level 3:满足level2的所有条件，并且外加所有表的标准选择率可以使用动态采样的谓词来计算
level 4:满足level3的所有标准，并且表上有参照到多个列的单个谓词。
level 5,6,7,8 and 9:满足前一个级别的所有标准，使用使用2，4，8，32，128倍的默认动态采样率。
level 10:满足level9的所有标注，并且对表里的所有块进行动态采样。
当然，前面的关于11个level的论述我可能翻译的不是很准确，有兴趣的话可以参考oracle database performance tuning.
4 何时使用动态采样以及如何使用：
(1) OLAP系统中可以大量使用
(2) 尽量避免在OLTP系统中使用
(3) 如果想在OLTP系统中使用的话，建议通过sql profiles来使用，遗憾的是从10g以后才能使用这个功能。原理上来说，SQL profiles有点像收集统计数据的查询和存储信息的字典，因此降低动态抽样的时间和hard parse的时间。
欢迎有问题一起讨论。tom关于这个问题有更加详细的论述，如果有兴趣的话，可以参考。
异星工厂太空时代游戏总结
3 天前
没有评论:
发表评论