Oracle动态采样详解

动态采样（Dynamic Sampling）技术的最初提出是在Oracle 9i R2，在段（表，索引，分区）没有分析的情况下，为了使CBO 优化器得到

动态采样概述

动态采样（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 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)