dynamic sampling used for this statement

用set autotrace on 跟踪SQL执行计划时，发现执行计划中使用了动态采样。
- dynamic sampling used for this statement (level=2)

在收集该表的统计信息后，动态采样从执行计划中消失：

SQL> show parameter optimizer_dynamic_sampling;

NAME                                            TYPE        VALUE
------------------------------------       -----------   ------
optimizer_dynamic_sampling        integer          2

SQL> set autotrace on;
SQL> SELECT COUNT(1) FROM test;
  COUNT(1)
----------
     30916

已用时间:  00: 00: 00.01

 

执行计划
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   124   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 34747 |   124   (0)| 00:00:02 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
-- 在没有收集统计信息的情况下，执行计划中有动态采样的相关信息

-- 验证test表的统计信息是否被收集，在user_tables的num_rows和LAST_ANALYZED列都为null，

    说明test表的统计信息未被收集
SQL> SELECT t.table_name, t.num_rows, t.last_analyzed
  2    FROM USER_TABLES t
  3   WHERE t.table_name = 'TEST';

TABLE_NAME    NUM_ROWS LAST_ANALYZED
-----------           ----------       -------------
TEST
--实际test表中有30916条记录
SQL> SELECT COUNT(1) FROM test;
  COUNT(1)
----------
     30916
--对test表进行统计信息收集
SQL>  analyze    table     TEST  estimate    statistics;
表已分析。
已用时间:  00: 00: 00.14

 

--收集统计信息后，重复执行上述SQL，发现动态采用从执行计划中消失。
SQL> SELECT COUNT(1) FROM test;
  COUNT(1)
----------
     30916

已用时间:  00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   124   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 32051 |   124   (0)| 00:00:02 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        440  consistent gets
          0  physical reads
          0  redo size
        432  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

oracle 官方文档中关于动态采样条件的描述

• When to Use Dynamic Sampling
  For a query that normally completes quickly (in less than a few seconds), you will not want to incur the cost of dynamic sampling. However, dynamic sampling can be beneficial under any of the following conditions:
  A better plan can be found using dynamic sampling.
  The sampling time is a small fraction of total execution time for the query.
  The query will be executed many times.
  Dynamic sampling can be applied to a subset of a single table's  predicates and combined with standard selectivity estimates of predicates for which dynamic sampling is not done.

 

• How to Use Dynamic Sampling to Improve Performance
  You control dynamic sampling with the OPTIMIZER_DYNAMIC_SAMPLING parameter, which can be set to a value from 0 to 10. The default is 2.
  A value of 0 means dynamic sampling will not be done.
  Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling.
  Dynamic sampling is repeatable if no rows have been inserted, deleted, or updated in the table being sampled. The parameter OPTIMIZER_FEATURES_ENABLE turns off dynamic sampling if set to a version prior to 9.2.0.