Oracle动态采样详解(原创)

动态采样概述
动态采样（Dynamic Sampling）技术的最初提出是在Oracle 9i R2，在段（表，索引，分区）没有分析的情况下，为了使CBO 优化器得到足够的信息以保证做出正确的执行计划而发明的一种技术，可以把它看做分析手段的一种补充。
当段对象没有统计信息时（即没有做分析），动态采样技术可以通过直接从需要分析的对象上收集数据块（采样）来获得CBO需要的统计信息。
一个简单的例子：
创建表：
SQL>  create table t as select  owner,object_type from dba_objects;
Table created.
查看表的记录数：
SQL> select count(*) from t
  COUNT(*)
----------
     50419   -- 记录数
这里创建了一张普通表，没有做分析，我们在hint中用0级来限制动态采样，此时CBO唯一可以使用的信息就是表存储在数据字典中的一些信息，如有多少个extent，有多少个block，但是这些信息是不够的。
SQL> set autotrace trace exp
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  |      | 12007 |   328K|    34   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 12007 |   328K|    34   (0)| 00:00:01 |
--------------------------------------------------------------------------
在没有做动态分析的情况下 ，CBO估计的记录数是 12007条，与真实的 50419相差甚远。
动态分析来后：
SQL> select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 45596 |  1246K|    35   (3)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 45596 |  1246K|    35   (3)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
在Oracle 10g中默认对没有分析的段做动态采样，上面的查询结果显示使用了动态采样，CBO计的结果是 45596与 50419很接近了。 由于动态采样只是对有限的一些数据块做分析，来对整个表做出估算，所以无法和实际值完全吻合也是很正常的。
注意：在没有动态采样的情况下，对于没有分析过的段，CBO也可能错误地将结果判断的程度扩大话 。
见下列
SQL> delete from t;
50419 rows deleted.
SQL> set autotrace trace exp               
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  |      | 12007 |   328K|    34   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 12007 |   328K|    34   (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 |    34   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |    28 |    34   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
可以看到，在没有采用动态分析的情况下，CBO对t表估计的还是12007行记录 ，但是用动态分析就显示1条记录。 而表中的数据在查询之前已经删除掉了，出现这种情况的原因是因为高水位。 由于没有采用动态采样时的表信息来自 前面提到的数据字典中的 extent和block信息， 虽然表的数据已经删除，但是表分配的extent 和block没有被回收，在这种情况下CBO 依然认为有那么多的数据存在。
通过这一点，我们可以看出，此时CBO能够使用的信息非常有限，也就是这个表有几个extent，有几个block。但动态采样之后，Oracle 立即发现，原来数据块中都是空的。
如果是通过设置sql_trace=true来查看执行计划，动态采样会体现出如下信息：
********************************************************************************
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
  NVL(SUM(C2),:"SYS_B_1")
FROM
 (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T1") FULL("T1")
  NO_PARALLEL_INDEX("T1") */ :"SYS_B_2" AS C1, CASE WHEN "T1"."ID"=:"SYS_B_3"
  THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "T1" SAMPLE BLOCK
  (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8") "T1") SAMPLESUB
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.03       0.09        171         70          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.03       0.10        171         70          0           1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55     (recursive depth: 1)
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=70 pr=171 pw=0 time=97308 us)
  14049   TABLE ACCESS SAMPLE T1 (cr=70 pr=171 pw=0 time=720915 us)
********************************************************************************
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled',
   'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE
  */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), NVL(SUM(C3),:"SYS_B_2")
FROM
 (SELECT /*+ NO_PARALLEL("T1") INDEX("T1" T1_INX) NO_PARALLEL_INDEX("T1") */
  :"SYS_B_3" AS C1, :"SYS_B_4" AS C2, :"SYS_B_5" AS C3  FROM "T1" "T1" WHERE
  "T1"."ID"=:"SYS_B_6" AND ROWNUM <= :"SYS_B_7") SAMPLESUB
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55     (recursive depth: 1)
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=2 pr=0 pw=0 time=660 us)
      1   VIEW  (cr=2 pr=0 pw=0 time=521 us)
      1    COUNT STOPKEY (cr=2 pr=0 pw=0 time=368 us)
      1     INDEX RANGE SCAN T1_INX (cr=2 pr=0 pw=0 time=156 us)(object id 52550)
动态采样的作用
1、CBO 依赖的是充分的统计分析信息，但是并不是每个用户都会非常认真，及时地去对每个表做分析。为了保证执行计划都尽可能地正确，Oracle需要使用动态采样技术来帮助CBO 获取尽可能多的信息。
2、全局临时表。通常来讲，临时表的数据是不做分析的，因为它存放的数据是临时性的，可能很快就释放了，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。
3、动态采样除了可以在段对象没有分析时，给CBO提供分析数据之外，还有一个独特的能力，它可以对不同列之间的相关性做统计。这点通常发生在表设计不符合3NF的情况下，这个特性在表 符合3NF设计的 情况下少见。感兴趣的读者可以参看如下链接：
http://fanng.blogspot.com/2008/12/oracle_28.html
动态采样的级别
Level 0：不做动态分析
Level 1：Oracle 对没有分析的表进行动态采样，但需要同时满足以下4个条件。
1、SQL中至少有一个未分析的表
2、未分析的表出现在关联查询或者子查询中
3、未分析的表没有索引
4、未分析的表占用的数据块要大于动态采样默认的数据块（32个）
Level 2：对所有的未分析表做分析，动态采样的数据块是默认数据块的2倍，即64个。
Level 3：采样的表包含满足Level 2定义的所有表，同时包括，那些谓词有可能潜在地需要动态采样的表，这些动态采样的数据块为默认数据块，对没有分析的表，动态采样的默认块为默认数据块的2倍， 即64个 。
Level 4：采样的表包含满足Level 3定义的表，同时还包括一些表，他们包含一个单表的谓词会引用另外的2个列或者更多的列；采样的块数是动态采样默认数据块数；对没有分析的表，动态采样的数据块为默认数据块的2倍。
Level 5，6，7，8，9：采样的表包含满足Level 4定义的表，同时分别使用动态采样默认数据块的2，4，8，32，128 倍的数量来做动态分析。
Level 10：采样的表包含满足Level 9定义的所有表，同时对表的所有数据进行动态采样。
采样的数据块越多，得到的分析数据就越接近与真实，但同时伴随着资源消耗的也越大。
什么时候使用动态采样
尽管看到动态采样的优点，但是它的缺点也是显而易见，否则Oracle一定会一直使用动态采样来取代数据分析：
1、 在OLAP或者数据仓库环境下，SQL执行消耗的资源要远远大于SQL解析，那么让解析在消耗多一点资源做一些动态采样分析，从而做出一个最优的执行计划是非常值得的。 实际上在这样的环境中，硬分析消耗的资源几乎是可以忽略的。但是 采样的数据块有限，对于海量数据的表，结果难免有偏差。 所以一般在OLAP 或者数据仓库环境中，将动态采样的level 设置为3或者4比较好 。
2、 动态采样需要额外的消耗数据库资源，所以，如果 SQL被反复执行，变量被绑定，硬分析很少，在这样一个环境中，是不宜使用动态采样的。 动态采样发生在硬分析时，如果很少有硬分析发生，动态采样的意义就不大。 当然如果没有使用绑定变量，导致频繁进行硬解析和动态采样消耗过多资源也是不可行的，故OLTP系统非常不适宜使用动态采样

参考至:《让Oracle跑得更快》谭怀远著
            http://docs.oracle.com/cd/E11882_01/server.112/e10821/stats.htm#PFGRF94760
            http://blog.csdn.net/tianlesoftware/article/details/5845028
            http://fanng.blogspot.com/2008/12/oracle_28.html
本文原创，转载请注明出处、作者
如有错误，欢迎指正
有限:[e-mail]