如果一个表从来没收集过统计信息，默认情况下 Oracle 会对表进行动态采样（Level=2）以便优化器估算出较为准确的 Rows，动态采样的最终目的就是为了让优化器能够评估出较为准确的 Rows。

    现在我们创建一个测试表 T_DYNA。

    1. create table t_dyna as select * from dba_objects;
    1. Table created.

    我们执行下面 SQL 并且查看执行计划。

    1. select count(*) from t_dyna;
    1. Execution Plan
    2. ----------------------------------------------------------
    3. Plan hash value: 3809964769
    5. ---------------------------------------------------------------------
    6. | Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
    7. ---------------------------------------------------------------------
    8. |   0 | SELECT STATEMENT   |        |     1 |   187   (1)| 00:00:03 |
    9. |   1 |  SORT AGGREGATE    |        |     1 |            |          |
    10. |   2 |   TABLE ACCESS FULL| T_DYNA | 65305 |   187   (1)| 00:00:03 |
    11. ---------------------------------------------------------------------
    13. Note
    14. -----
    15.    - dynamic sampling used for this statement (level=2)

    因为表 T_DYNA 是才创建的新表，没有收集过统计信息，所以会启用动态采样。执行计划中dynamic sampling used for this statement (level=2)表示启用了动态采样，level 表示采样级别，默认情况下采样级别为 2。

    动态采样的级别分为 11 级。

    • level 0：不启用动态采样。

    • level 1：当表（非分区表）没有收集过统计信息并且这个表要与另外的表进行关联（不能是单表访问），同时该表没有索引，表的数据块必须大于 32 个，满足这些条件的时候，Oracle 会随机扫描表中 32 个数据块，然后评估返回的 Rows。

    • level 2：对没有收集过统计信息的表启用动态采样，采样的块数为 64 个，如果表的块数小于 64 个，表有多少个块就会采样多少个块。

    • level 3：对没有收集过统计信息的表启用动态采样，采样的块数为 64 个。如果表已经收集过统计信息，但是优化器不能准确地估算出返回的 Rows，而是靠猜，比如WHERE SUBSTR(owner,1,3)，这时会随机扫描 64 个数据块进行采样。

    • level 4：对没有收集过统计信息的表启用动态采样，采样的块数为 64 个。如果表已经收集过统计信息，但是表有两个或者两个以上过滤条件（AND/OR），这时会随机扫描 64 个数据块进行采样，相关列问题就必须启用至少 level 4 进行动态采样。level4 采样包含了 level 3 的采样数据。

    • level 5：收集满足 level 4 采样条件的数据，采样的块数为 128 个。

    • level 6：收集满足 level 4 采样条件的数据，采样的块数为 256 个。

    • level 7：收集满足 level 4 采样条件的数据，采样的块数为 512 个。

    • level 8：收集满足 level 4 采样条件的数据，采样的块数为 1 024 个。

    • level 9：收集满足 level 4 采样条件的数据，采样的块数为 4 086 个。

    • level 10：收集满足 level 4 采样条件的数据，采样表中所有的数据块。

    • level 11：Oracle 自动判断如何采样，采样的块数由 Oracle 自动决定。

    在 2.4 节中我们已经演示过动态采样 level 4 的用途，现在将为各位读者演示动态采样 level 3 的用途。

    我们执行下面 SQL 并且查看执行计划。

    1. select * from t_dyna where substr(owner,4,3)='LIC';
    1. 27699 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 1744410282
    7. ----------------------------------------------------------------------------
    8. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    9. ----------------------------------------------------------------------------
    10. |   0 | SELECT STATEMENT  |        | 23044 |  4658K|   190   (3)| 00:00:03 |
    11. |*  1 |  TABLE ACCESS FULL| T_DYNA | 23044 |  4658K|   190   (3)| 00:00:03 |
    12. ----------------------------------------------------------------------------
    14. Predicate Information (identified by operation id):
    15. ---------------------------------------------------
    17.    1 - filter(SUBSTR("OWNER",4,3)='LIC')
    19. Note
    20. -----
    21.    - dynamic sampling used for this statement (level=2)

    因为 T_DYNA 没有收集过统计信息，启用了动态采样，采样级别默认为 level 2，动态采样估算的 Rows(23 044)与真实的 Rows(27 699)比较接近。

    现在我们对表 T_DYNA 收集统计信息。

    1. BEGIN
    2.     DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
    3.                                   tabname          => 'T_DYNA',
    4.                                   estimate_percent => 100,
    5.                                   method_opt       => 'for all columns size skewonly',
    6.                                   no_invalidate    => FALSE,
    7.                                   degree           => 1,
    8.                                   cascade          => TRUE);
    9.   END;
    1. PL/SQL procedure successfully completed.

    我们再次查看执行计划。

    1. select * from t_dyna where substr(owner,4,3)='LIC';
    1. 27699 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 1744410282
    7. ----------------------------------------------------------------------------
    8. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    9. ----------------------------------------------------------------------------
    10. |   0 | SELECT STATEMENT  |        |   728 | 70616 |   190   (3)| 00:00:03 |
    11. |*  1 |  TABLE ACCESS FULL| T_DYNA |   728 | 70616 |   190   (3)| 00:00:03 |
    12. ----------------------------------------------------------------------------
    14. Predicate Information (identified by operation id):
    15. ---------------------------------------------------
    17.    1 - filter(SUBSTR("OWNER",4,3)='LIC')

    对表 T_DYNA 收集了统计信息之后，因为统计信息中没有包含**substr(owner,4,3)**的统计，所以优化器无法估算出较为准确的 Rows，优化器估算返回了 728 行数据，而实际上返回了 27 699 行数据。现在我们将动态采样 level 设置为 3。

    1. alter session set optimizer_dynamic_sampling=3;
    1. Session altered.

    我们执行 SQL 并且查看执行计划。

    1. select * from t_dyna where substr(owner,4,3)='LIC';
    1. 27699 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 1744410282
    7. ----------------------------------------------------------------------------
    8. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    9. ----------------------------------------------------------------------------
    10. |   0 | SELECT STATEMENT  |        | 28795 |  2727K|   191   (3)| 00:00:03 |
    11. |*  1 |  TABLE ACCESS FULL| T_DYNA | 28795 |  2727K|   191   (3)| 00:00:03 |
    12. ----------------------------------------------------------------------------
    14. Predicate Information (identified by operation id):
    15. ---------------------------------------------------
    17.    1 - filter(SUBSTR("OWNER",4,3)='LIC')
    19. Note
    20. -----
    21.    - dynamic sampling used for this statement (level=3)

    将动态采样设置为 level 3 之后，优化器发现 where 条件中有substr(owner,4,3)，无法估算出准确的 Rows，因此对 SQL 启用了动态采样，动态采样估算返回了 28 795 行数据，接近于真实的行数 27 699。

    除了设置参数 optimizer_dynamic_sampling 启用动态采样外，我们还可以添加 HINT 启用动态采样。

    1. alter session set optimizer_dynamic_sampling=2;
    1. Session altered.
    1. select /*+ dynamic_sampling(3) */ * from t_dyna where substr(owner,4,3)='LIC';
    1. 27699 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 1744410282
    7. ----------------------------------------------------------------------------
    8. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    9. ----------------------------------------------------------------------------
    10. |   0 | SELECT STATEMENT  |        | 28795 |  2727K|   191   (3)| 00:00:03 |
    11. |*  1 |  TABLE ACCESS FULL| T_DYNA | 28795 |  2727K|   191   (3)| 00:00:03 |
    12. ----------------------------------------------------------------------------
    14. Predicate Information (identified by operation id):
    15. ---------------------------------------------------
    17.    1 - filter(SUBSTR("OWNER",4,3)='LIC')
    19. Note
    20. -----
    21.    - dynamic sampling used for this statement (level=3)

    如果表已经收集过统计信息并且优化器能够准确地估算出返回的 Rows，即使添加了动态采样的 HINT 或者是设置了动态采样的参数为 level 3，也不会启用动态采样。

    1. SQL> select /*+ dynamic_sampling(3) */ * from t_dyna where owner='SYS';
    3. 30928 rows selected.
    5. Execution Plan
    6. ----------------------------------------------------------
    7. Plan hash value: 1744410282
    9. ----------------------------------------------------------------------------
    10. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    11. ----------------------------------------------------------------------------
    12. |   0 | SELECT STATEMENT  |        | 30928 |  2929K|   188   (2)| 00:00:03 |
    13. |*  1 |  TABLE ACCESS FULL| T_DYNA | 30928 |  2929K|   188   (2)| 00:00:03 |
    14. ----------------------------------------------------------------------------
    16. Predicate Information (identified by operation id):
    17. ---------------------------------------------------
    19.    1 - filter("OWNER"='SYS')

    因为表 T_DYNA 收集过统计信息，优化器能够直接根据统计信息估算出较为准确的 Rows，所以，即使添加了 HINT：/*+ dynamic_sampling(3) */，也没有启用动态采样。

    什么时候需要启用动态采样呢？

    当系统中有全局临时表，就需要使用动态采样，因为全局临时表无法收集统计信息，我们建议对全局临时表至少启用 level 4 进行采样。

    当执行计划中表的 Rows 估算有严重偏差的时候，例如相关列问题，或者两表关联有多个连接列，关联之后 Rows 算少，或者是 where 过滤条件中对列使用了 substr、instr、like，又或者是 where 过滤条件中有非等值过滤，或者 group by 之后导致 Rows 估算错误，此时我们可以考虑使用动态采样，同样，我们建议动态采样至少设置为 level 4。

    在数据仓库系统中，有些报表 SQL 是采用 Obiee/SAP BO/Congnos 自动生成的，此类 SQL 一般都有几十行甚至几百行，SQL 的过滤条件一般也比较复杂，有大量的 AND 和 OR 过滤条件，同时也可能有大量的 where 子查询过滤条件，SQL 最终返回的数据量其实并不多。对于此类 SQL，如果 SQL 执行缓慢，有可能是因为 SQL 的过滤条件太复杂，从而导致优化器不能估算出较为准确的 Rows 而产生了错误的执行计划。我们可以考虑启用动态采样 level 6 观察性能是否有所改善，我们曾利用该方法优化了大量的报表 SQL。

    最后，需要注意的是，不要在系统级更改动态采样级别，默认为 2 就行，如果某个表需要启用动态采样，直接在 SQL 语句中添加 HINT 即可。