Oracle动态采样深入理解

动态采样(Dynamic Sampling)是在Oracle 9i Release 2中开始引入的一个技术，引入它的目的是为了应对数据库对象没有分析（统计信息缺失）的情况下，优化器生成更好的执行计划。简单的说，在数据库段（表、索引、分区）对象没有分析的情况下，为了使CBO优化器得到足够多的信息以保证优化器做出正确执行计划而发明的一种技术。它会分析一定数量段对象上的数据块获取CBO需要的统计信息。动态采样技术仅仅是统计信息的一种补充，它不能完全替代统计信息分析。

注意：动态采样在Oracle 11g之前称为 Dynamic Sampling, ORACLE 12c之后改名为Dynamic Statistic.
Oracle11G R2 默认的采样级别：
SQL> show parameter optimizer_dynamic_sampling
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling          integer    2

SQL> show parameter Dynamic Statistic
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling          integer    2

动态采样的级别有11个级别：请自行查看官方文档
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30101

动态采样实验：

1、创建测试表test
SQL> create table test as select * from dba_objects;         

Table created.

SQL> select count(1) from test;

  COUNT(1)
----------
    86259

2、不使用动态采样，查看执行计划
SQL> set autotrace traceonly explain;
SQL> select /*+ dynamic_sampling(test 0) */ * from test; 

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |  100K|    19M|  336  (1)| 00:00:05 |
|  1 |  TABLE ACCESS FULL| TEST |  100K|    19M|  336  (1)| 00:00:05 |
--------------------------------------------------------------------------

Note
-----
  - dynamic sampling used for this statement (level=2)

从上面可以看出，次数优化器估计表test的行数显示为100K，我们再看下面使用动态采样的执行计划，优化器会估算多少行：

3、使用动态采样，查看执行计划（下面是直接查询的，因为在11G 是默认启用动态采样的）
SQL> select * from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      | 72258 |    14M|  336  (1)| 00:00:05 |
|  1 |  TABLE ACCESS FULL| TEST | 72258 |    14M|  336  (1)| 00:00:05 |
--------------------------------------------------------------------------

Note
-----
  - dynamic sampling used for this statement (level=2)
 
 
如果启用动态采样(默认情况下，动态采样级别为2)，优化器根据动态采样得到一些数据信息猜测、估计表TEST的记录行数为86259，已经接近实际记录行数72258了。比不做动态采样分析要好很多了。

如果我们将动态采样的级别提高为3，如下所示，发现优化器根据动态采样得到的信息比默认（默认情况下，动态采样级别为2）情况获得的信息更准确。优化器估计表TEST的行数为92364，比72258又接近实际情况一步了。

SQL> select /*+ dynamic_sampling(test 3) */ * from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      | 92364 |    18M|  336  (1)| 00:00:05 |
|  1 |  TABLE ACCESS FULL| TEST | 92364 |    18M|  336  (1)| 00:00:05 |
--------------------------------------------------------------------------

Note
-----
  - dynamic sampling used for this statement (level=2)

4、在Tom大师的这篇文章中提到，在没有动态采样的情况下，如果删除了该表数据，CBO优化器估算的结果集和没有删除之前是一样的。
    这是因为当一个表的数据被删除后，这个表所分配的extent和block是不会自动回收的（高水位线不变），所以CBO如果没有采样数据块做分析，只是从数据字典中获取extend等信息，就会误认为任然还有那么多数据。下面我们把test表数据清空，看看执行计划如何

SQL> delete from test;

86259 rows deleted.

SQL> commit;

SQL> select /*+ dynamic_sampling(test 0) */ * from test;    ----不使用动态采样
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |  100K|    19M|  336  (1)| 00:00:05 |
|  1 |  TABLE ACCESS FULL| TEST |  100K|    19M|  336  (1)| 00:00:05 |
--------------------------------------------------------------------------

SQL> select * from test;                  -----使用动态采样

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    1 |  207 |  335  (0)| 00:00:05 |
|  1 |  TABLE ACCESS FULL| TEST |    1 |  207 |  335  (0)| 00:00:05 |
--------------------------------------------------------------------------

Note
-----
  - dynamic sampling used for this statement (level=2)

从上面的查看可以看出，不采用动态采样和采用动态采样的区别；

更多详情见请继续阅读下一页的精彩内容： 2017-05/143401p2.htm