https://blog.csdn.net/indexman/article/details/42324465
https://blog.csdn.net/renfengjun/article/details/21385335
http://www.killdb.com/2012/04/12/in-depth-analysis-of-the-parameter-optimizer_dynamic_sampling.html
1、什么是动态采样？
动态抽样从 oracle 9i第2版引入。它使得优化器（CBO）在硬解析期间有能力抽样一个未分析的表
（any table that has been created and loaded but not yet analyzed）的统计（决定表默认统计），并且可以验证优化器的”猜想“。
因其只在查询硬解析期间为优化器动态生成更好的统计，得名动态采样。
动态采样提供11个设置级别。注意：9i中其默认值为1 到了10g默认值为2
表级别的采样块数，一个简单的公式：
2^(level - 1)*_optimizer_dyn_smp_blks (_optimizer_dyn_smp_blks的默认值为 32)
因此level 1的采样块数为
2^0*32 = 1*32 = 32
level 4的采样块数为
2^3*32 = 8 * 32 = 256
level 9的采样块数为
2^8*32 = 256 * 32 = 8192
level 10再次出现了跳跃，有2的32次方个块，也就是一个表中所有的块。读者可以自己对比下，表级别的采样块数与session/system和cursor level的不同。
2、动态采样如何工作？
有两种使用方式：
△ 设置OPTIMIZER_DYNAMIC_SAMPLING参数，可以再实例和会话级别设置动态采样。
△ 使用DYNAMIC_SAMPLING hint
动态采样默认为启动状态，可以设置 OPTIMIZER_DYNAMIC_SAMPLING=0来禁用掉这一特性。
OPTIMIZER_DYNAMIC_SAMPLING也是和动态采样最重要的参数，它控制着动态采样级别。
3、什么时候适合采用动态采样？
这是一个狡猾的问题，没有一定使用经验，还真不好意思说。
通常：
1）我们使用3和4级进行动态采样，默认数据库是级别2的动态采样。
2）如果我们SQL的解析时间很快但是执行时间巨慢，可以考虑使用动态采样。典型的就是数据仓库系统。
3）OLTP系统中都是一个SQL重复执行，解析和执行都在瞬息之间，所以不建议使用高级别的动态采样。这会给SQL带来硬解析消耗。
4）表分析（使用dbms_Stats）可以提供较高品质的统计信息，而动态采样则是一个折中方案，只能提供粗粒度的统计信息，所以对于一些对统计信息要求较高的查询中，因为统计信息缺失而使用动态采样是无法保证优化器选择最优的执行计划
这个时候可以考虑SQL Profile，你可以理解为“静态采样”。
4、动态采样级别：
现在列出11个级别，详细请参考：http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i43032
1）Level 0: Do not use dynamic sampling.
0级：不使用动态采样。
2）Level 1: Sample all tables that have not been analyzed if the following criteria are met:
(1) there is at least 1 unanalyzed table in the query;
(2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view;
(3) this unanalyzed table has no indexes;
(4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
1级：满足以下条件则采样所有没被分析的表：
（1）查询中至少有一个未分析表；
（2）这个未分析表被关联另外一个表或者出现在子查询或非merge视图中；
（3）这个未分析表有索引；
（4）这个未分析表有多余动态采样默认的数据块数（默认是32块）。
3）Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
2级：对所有未分析表进行动态采样。采样数据块数量是默认数量的2倍。
4）Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
3级：在2级基础上加上那些使用了猜想选择消除表，采样数据块数量等于默认数量。对于未分析表，采样数量2倍于默认数量。
5）Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
4级：在3级基础上加上那些有单表谓词关联2个或多个列，采样数据块数量等于默认数量。对于未分析表，采样数量2倍于默认数量。
6）Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
5,6,7,8,9级在4级基础上分别使用2,4,8,32,128倍于默认动态采样数据块数量。
7）Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
10级：在9级基础上对表中所有数据块进行采样。
5.应用场景
下面列举3个动态采样的典型应用场景
5.1 缺失统计信息
当查询中的一个或者多个表没有统计信息，那么优化器就会收集关于表的基本信息用来执行优化操作。
1）.创建表
SQL> create table tuning8_tab1 nologging as select level as id , 'name'|| level as name from dual connect by level <= 10000 ;
Table created.
2）.创建索引
SQL> create index idx_tuning8_tab1_id on tuning8_tab1(id) ;
Index created.
3）.查询测试
--先看一下参数的设置
SQL> show parameter samp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL>
SQL> set autot trace;
SQL> select * from tuning8_tab1 where id=2 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3712969662
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TUNING8_TAB1 | 1 | 37 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TUNING8_TAB1_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
动态采样已经起作用了。
5.2 缺失extended statistics
oracle提供相关性统计信息，这里简单演示了一种情况，说明动态采样可以使oracle数据库更加智能的选择查询计划，进而提升性能。（其实使用dbms_stats设置特定的参数一样可以达到这种目的）
1）. 基于all_objects视图创建表
SQL> create table tect9_tab2 nologging as select decode( mod(rownum,2), 0, 'D', 'R' )col1,decode( mod(rownum,2), 0, 'R', 'D' ) col2, t.* from all_objects t;
Table created.
2）. 创建复合索引
SQL> create index idx_comp_tect9_tab2_col1_col2 on tect9_tab2(col1,col2) ;
Index created.
3）. 收集统计信息
SQL> exec dbms_stats.gather_table_stats(user,'tect9_tab2',method_opt=>'for all indexed columns size 254' );
PL/SQL procedure successfully completed.
4）. 查询测试
数据总量为85083
SQL> select count(*) from tect9_tab2 ;
COUNT(*)
----------
85083
已经收集了统计信息，所以常规的查询都可以得到正确的执行计划。
SQL> set autot trace;
SQL> select * from tect9_tab2 where col1='D' ;
42541 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3262335044
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43186 | 4301K| 345 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TECT9_TAB2 | 43186 | 4301K| 345 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"='D')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4059 consistent gets
587 physical reads
0 redo size
2196868 bytes sent via SQL*Net to client
31715 bytes received via SQL*Net from client
2838 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
42541 rows processed
--接着如下查询，这里也是正确的：
SQL> select * from tect9_tab2 where col1='D' and col2='R' ;
42541 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3262335044
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21916 | 2183K| 345 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TECT9_TAB2 | 21916 | 2183K| 345 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"='R' AND "COL1"='D')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4059 consistent gets
0 physical reads
0 redo size
2196868 bytes sent via SQL*Net to client
31715 bytes received via SQL*Net from client
2838 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
42541 rows processed
--再次查询，如下：
这里按照常理来说是正确的，但是在本环境中，oracle其实有更智能的一面：
因为col1和col2 之间有关联，col1=’D’的时候col2=’D’的记录是没有的，但是这里预计的条数为21266条，预计错误，oracle是否有更智能的选择呢？
SQL> select * from tect9_tab2 where col1='D' and col2='D' ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3262335044
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21266 | 2118K| 345 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TECT9_TAB2 | 21266 | 2118K| 345 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"='D' AND "COL1"='D')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1266 consistent gets
0 physical reads
0 redo size
1475 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
5.3 使用动态采样：using Dynamic Samping
可以看到，使用动态采样，虽然预计得到的条数为但是已经比较接近真实环境了，这里选择索引扫描作为access path。
--使用动态采样
SQL> select /*+ dynamic_sampling(t 2) */ * from tect9_tab2 t where col1='D' and col2='D' ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1707542874
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1530 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TECT9_TAB2 | 15 | 1530 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_COMP_TECT9_TAB2_COL1_COL2 | 15 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"='D' AND "COL2"='D')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
65 consistent gets
1 physical reads
0 redo size
1475 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
5.4 对于一个经常大规模被修改的表使用动态采样
如果一个表中的数据，经常执行大批量的加载或者更新，那么在每一次加载或者更新操作后都需要手动的执行收集统计信息的操作。
因为如果不手动的收集，因为统计信息过旧，优化器可能会选择效率较差的access path，影响效率。
其实还有另外一种做法可以避免这样的事情发生，那就是使用动态采样特性。
当一个表没有统计信息的时候，oracle数据库就会使用动态采样来协助优化器。所以对于上述的环境中，就可以删除、并且锁定表的统计信息。这样每次执行查询操作的时候，动态采样的特性都会被使用。
下面给出示例：
1）.创建表
SQL> create table tuning8_tab3 nologging as select level as id , 'name'||level as name from dual connect by level <= 10000;
Table created.
2）.创建索引
SQL> create index idx_tuning8_tab3_id on tuning8_tab3(id) ;
Index created.
3）.收集统计信息
SQL> exec dbms_stats.gather_table_stats(user,'tuning8_tab3',method_opt=>'for all indexed columns size 254' );
PL/SQL procedure successfully completed.
4）.查询测试
SQL> set autotrace on
SQL> select * from tuning8_tab3 where id=1 ;
ID NAME
---------- --------------------------------------------
1 name1
Execution Plan
----------------------------------------------------------
Plan hash value: 1647674320
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TUNING8_TAB3 | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TUNING8_TAB3_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
非常正常，优化器选择了最优的accesspath 。
5）.加载数据
SQL> insert into tuning8_tab3 select 10 , 'namess' from dual connect by level <= 50000 ;
50000 rows created.
SQL> commit ;
Commit complete.
6）.查询测试
可以看到，优化器选择了索引扫描作为access path。
SQL> select count(name) from tuning8_tab3 where id=10 ;
COUNT(NAME)
-----------
50001
Execution Plan
----------------------------------------------------------
Plan hash value: 2464990924
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TUNING8_TAB3 | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TUNING8_TAB3_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
451 consistent gets
0 physical reads
13796 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--比较发现，使用全表扫描才是最优的,如下：
SQL> select /*+full(tuning8_tab3) */ count(name) from tuning8_tab3 where id=10 ;
COUNT(NAME)
-----------
50001
Execution Plan
----------------------------------------------------------
Plan hash value: 926892911
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| TUNING8_TAB3 | 1 | 13 | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
135 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
7）.解决方法
7）.1每次加载后收集统计信息
问题已经出现了，当然在每次大规模加载数据后，可以收集一次表的统计信息。
SQL> exec dbms_stats.gather_table_stats(user,'tuning8_tab3',method_opt=>'for all indexed columns size 254' );
PL/SQL procedure successfully completed.
SQL> select count(name) from tuning8_tab3 where id=10 ;
COUNT(NAME)
-----------
50001
Execution Plan
----------------------------------------------------------
Plan hash value: 926892911
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 38 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| TUNING8_TAB3 | 49606 | 484K| 38 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
283 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
7）.2使用动态采样
当然还有另外一种方法，那就是今天要说的，使用动态采样的特性。
BEGIN
DBMS_STATS.DELETE_TABLE_STATS(user,'tuning8_tab3');
DBMS_STATS.LOCK_TABLE_STATS(user,'tuning8_tab3');
END;
/
操作如下：删除统计信息，并且锁定表的统计信息
SQL> BEGIN
2 DBMS_STATS.DELETE_TABLE_STATS(user,'tuning8_tab3');
3 DBMS_STATS.LOCK_TABLE_STATS(user,'tuning8_tab3');
4 END;
5 /
PL/SQL procedure successfully completed.
--查询表tuning8_tab3，由于删除了统计信息，并且锁定表的统计信息，执行时会进行动态采样，这样优化器通常情况下都会产生较正确的查询计划。
SQL> select count(name) from tuning8_tab3 where id=10 ;
COUNT(NAME)
-----------
50001
Execution Plan
----------------------------------------------------------
Plan hash value: 926892911
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 38 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS FULL| TUNING8_TAB3 | 57803 | 2088K| 38 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
225 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
8）.再次加载数据测试，就算再次加载数据，依然会产生最优的查询计划，保证了效率。
SQL> insert into tuning8_tab3 select 9 , 'namess' from dual connect by level <= 100000 ;
100000 rows created.
SQL> commit;
Commit complete.
SQL> set autot trace;
SQL> select count(name) from tuning8_tab3 where id=9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 926892911
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 93 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS FULL| TUNING8_TAB3 | 105K| 3805K| 93 (2)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=9)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
436 consistent gets
0 physical reads
756 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--如下使用index提示（hint)
SQL> select /*+index(tuning8_tab3 idx_tuning8_tab3_id)*/ count(name) from tuning8_tab3 where id=9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2464990924
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1878 (0)| 00:00:23 |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TUNING8_TAB3 | 105K| 3805K| 1878 (0)| 00:00:23 |
|* 3 | INDEX RANGE SCAN | IDX_TUNING8_TAB3_ID | 105K| | 316 (0)| 00:00:04 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=9)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
978 consistent gets
0 physical reads
26880 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
当然对于一些对查询计划要求比较高的地方，不建议使用这种方法，特别是olap系统，因为动态采样只是一个折中的收集统计信息的方法，并不像使用dbms_stats包那样准确，也有可能生成错误的执行计划
https://blog.csdn.net/renfengjun/article/details/21385335
http://www.killdb.com/2012/04/12/in-depth-analysis-of-the-parameter-optimizer_dynamic_sampling.html
1、什么是动态采样？
动态抽样从 oracle 9i第2版引入。它使得优化器（CBO）在硬解析期间有能力抽样一个未分析的表
（any table that has been created and loaded but not yet analyzed）的统计（决定表默认统计），并且可以验证优化器的”猜想“。
因其只在查询硬解析期间为优化器动态生成更好的统计，得名动态采样。
动态采样提供11个设置级别。注意：9i中其默认值为1 到了10g默认值为2
表级别的采样块数，一个简单的公式：
2^(level - 1)*_optimizer_dyn_smp_blks (_optimizer_dyn_smp_blks的默认值为 32)
因此level 1的采样块数为
2^0*32 = 1*32 = 32
level 4的采样块数为
2^3*32 = 8 * 32 = 256
level 9的采样块数为
2^8*32 = 256 * 32 = 8192
level 10再次出现了跳跃，有2的32次方个块，也就是一个表中所有的块。读者可以自己对比下，表级别的采样块数与session/system和cursor level的不同。
2、动态采样如何工作？
有两种使用方式：
△ 设置OPTIMIZER_DYNAMIC_SAMPLING参数，可以再实例和会话级别设置动态采样。
△ 使用DYNAMIC_SAMPLING hint
动态采样默认为启动状态，可以设置 OPTIMIZER_DYNAMIC_SAMPLING=0来禁用掉这一特性。
OPTIMIZER_DYNAMIC_SAMPLING也是和动态采样最重要的参数，它控制着动态采样级别。
3、什么时候适合采用动态采样？
这是一个狡猾的问题，没有一定使用经验，还真不好意思说。
通常：
1）我们使用3和4级进行动态采样，默认数据库是级别2的动态采样。
2）如果我们SQL的解析时间很快但是执行时间巨慢，可以考虑使用动态采样。典型的就是数据仓库系统。
3）OLTP系统中都是一个SQL重复执行，解析和执行都在瞬息之间，所以不建议使用高级别的动态采样。这会给SQL带来硬解析消耗。
4）表分析（使用dbms_Stats）可以提供较高品质的统计信息，而动态采样则是一个折中方案，只能提供粗粒度的统计信息，所以对于一些对统计信息要求较高的查询中，因为统计信息缺失而使用动态采样是无法保证优化器选择最优的执行计划
这个时候可以考虑SQL Profile，你可以理解为“静态采样”。
4、动态采样级别：
现在列出11个级别，详细请参考：http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i43032
1）Level 0: Do not use dynamic sampling.
0级：不使用动态采样。
2）Level 1: Sample all tables that have not been analyzed if the following criteria are met:
(1) there is at least 1 unanalyzed table in the query;
(2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view;
(3) this unanalyzed table has no indexes;
(4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).
1级：满足以下条件则采样所有没被分析的表：
（1）查询中至少有一个未分析表；
（2）这个未分析表被关联另外一个表或者出现在子查询或非merge视图中；
（3）这个未分析表有索引；
（4）这个未分析表有多余动态采样默认的数据块数（默认是32块）。
3）Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
2级：对所有未分析表进行动态采样。采样数据块数量是默认数量的2倍。
4）Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
3级：在2级基础上加上那些使用了猜想选择消除表，采样数据块数量等于默认数量。对于未分析表，采样数量2倍于默认数量。
5）Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
4级：在3级基础上加上那些有单表谓词关联2个或多个列，采样数据块数量等于默认数量。对于未分析表，采样数量2倍于默认数量。
6）Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
5,6,7,8,9级在4级基础上分别使用2,4,8,32,128倍于默认动态采样数据块数量。
7）Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
10级：在9级基础上对表中所有数据块进行采样。
5.应用场景
下面列举3个动态采样的典型应用场景
5.1 缺失统计信息
当查询中的一个或者多个表没有统计信息，那么优化器就会收集关于表的基本信息用来执行优化操作。
1）.创建表
SQL> create table tuning8_tab1 nologging as select level as id , 'name'|| level as name from dual connect by level <= 10000 ;
Table created.
2）.创建索引
SQL> create index idx_tuning8_tab1_id on tuning8_tab1(id) ;
Index created.
3）.查询测试
--先看一下参数的设置
SQL> show parameter samp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL>
SQL> set autot trace;
SQL> select * from tuning8_tab1 where id=2 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3712969662
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TUNING8_TAB1 | 1 | 37 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TUNING8_TAB1_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
动态采样已经起作用了。
5.2 缺失extended statistics
oracle提供相关性统计信息，这里简单演示了一种情况，说明动态采样可以使oracle数据库更加智能的选择查询计划，进而提升性能。（其实使用dbms_stats设置特定的参数一样可以达到这种目的）
1）. 基于all_objects视图创建表
SQL> create table tect9_tab2 nologging as select decode( mod(rownum,2), 0, 'D', 'R' )col1,decode( mod(rownum,2), 0, 'R', 'D' ) col2, t.* from all_objects t;
Table created.
2）. 创建复合索引
SQL> create index idx_comp_tect9_tab2_col1_col2 on tect9_tab2(col1,col2) ;
Index created.
3）. 收集统计信息
SQL> exec dbms_stats.gather_table_stats(user,'tect9_tab2',method_opt=>'for all indexed columns size 254' );
PL/SQL procedure successfully completed.
4）. 查询测试
数据总量为85083
SQL> select count(*) from tect9_tab2 ;
COUNT(*)
----------
85083
已经收集了统计信息，所以常规的查询都可以得到正确的执行计划。
SQL> set autot trace;
SQL> select * from tect9_tab2 where col1='D' ;
42541 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3262335044
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43186 | 4301K| 345 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TECT9_TAB2 | 43186 | 4301K| 345 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1"='D')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4059 consistent gets
587 physical reads
0 redo size
2196868 bytes sent via SQL*Net to client
31715 bytes received via SQL*Net from client
2838 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
42541 rows processed
--接着如下查询，这里也是正确的：
SQL> select * from tect9_tab2 where col1='D' and col2='R' ;
42541 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3262335044
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21916 | 2183K| 345 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TECT9_TAB2 | 21916 | 2183K| 345 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"='R' AND "COL1"='D')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4059 consistent gets
0 physical reads
0 redo size
2196868 bytes sent via SQL*Net to client
31715 bytes received via SQL*Net from client
2838 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
42541 rows processed
--再次查询，如下：
这里按照常理来说是正确的，但是在本环境中，oracle其实有更智能的一面：
因为col1和col2 之间有关联，col1=’D’的时候col2=’D’的记录是没有的，但是这里预计的条数为21266条，预计错误，oracle是否有更智能的选择呢？
SQL> select * from tect9_tab2 where col1='D' and col2='D' ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3262335044
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21266 | 2118K| 345 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TECT9_TAB2 | 21266 | 2118K| 345 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"='D' AND "COL1"='D')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1266 consistent gets
0 physical reads
0 redo size
1475 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
5.3 使用动态采样：using Dynamic Samping
可以看到，使用动态采样，虽然预计得到的条数为但是已经比较接近真实环境了，这里选择索引扫描作为access path。
--使用动态采样
SQL> select /*+ dynamic_sampling(t 2) */ * from tect9_tab2 t where col1='D' and col2='D' ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1707542874
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1530 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TECT9_TAB2 | 15 | 1530 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_COMP_TECT9_TAB2_COL1_COL2 | 15 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"='D' AND "COL2"='D')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
65 consistent gets
1 physical reads
0 redo size
1475 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
5.4 对于一个经常大规模被修改的表使用动态采样
如果一个表中的数据，经常执行大批量的加载或者更新，那么在每一次加载或者更新操作后都需要手动的执行收集统计信息的操作。
因为如果不手动的收集，因为统计信息过旧，优化器可能会选择效率较差的access path，影响效率。
其实还有另外一种做法可以避免这样的事情发生，那就是使用动态采样特性。
当一个表没有统计信息的时候，oracle数据库就会使用动态采样来协助优化器。所以对于上述的环境中，就可以删除、并且锁定表的统计信息。这样每次执行查询操作的时候，动态采样的特性都会被使用。
下面给出示例：
1）.创建表
SQL> create table tuning8_tab3 nologging as select level as id , 'name'||level as name from dual connect by level <= 10000;
Table created.
2）.创建索引
SQL> create index idx_tuning8_tab3_id on tuning8_tab3(id) ;
Index created.
3）.收集统计信息
SQL> exec dbms_stats.gather_table_stats(user,'tuning8_tab3',method_opt=>'for all indexed columns size 254' );
PL/SQL procedure successfully completed.
4）.查询测试
SQL> set autotrace on
SQL> select * from tuning8_tab3 where id=1 ;
ID NAME
---------- --------------------------------------------
1 name1
Execution Plan
----------------------------------------------------------
Plan hash value: 1647674320
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TUNING8_TAB3 | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TUNING8_TAB3_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
非常正常，优化器选择了最优的accesspath 。
5）.加载数据
SQL> insert into tuning8_tab3 select 10 , 'namess' from dual connect by level <= 50000 ;
50000 rows created.
SQL> commit ;
Commit complete.
6）.查询测试
可以看到，优化器选择了索引扫描作为access path。
SQL> select count(name) from tuning8_tab3 where id=10 ;
COUNT(NAME)
-----------
50001
Execution Plan
----------------------------------------------------------
Plan hash value: 2464990924
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TUNING8_TAB3 | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TUNING8_TAB3_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
451 consistent gets
0 physical reads
13796 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--比较发现，使用全表扫描才是最优的,如下：
SQL> select /*+full(tuning8_tab3) */ count(name) from tuning8_tab3 where id=10 ;
COUNT(NAME)
-----------
50001
Execution Plan
----------------------------------------------------------
Plan hash value: 926892911
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| TUNING8_TAB3 | 1 | 13 | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
135 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
7）.解决方法
7）.1每次加载后收集统计信息
问题已经出现了，当然在每次大规模加载数据后，可以收集一次表的统计信息。
SQL> exec dbms_stats.gather_table_stats(user,'tuning8_tab3',method_opt=>'for all indexed columns size 254' );
PL/SQL procedure successfully completed.
SQL> select count(name) from tuning8_tab3 where id=10 ;
COUNT(NAME)
-----------
50001
Execution Plan
----------------------------------------------------------
Plan hash value: 926892911
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 38 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| TUNING8_TAB3 | 49606 | 484K| 38 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
283 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
7）.2使用动态采样
当然还有另外一种方法，那就是今天要说的，使用动态采样的特性。
BEGIN
DBMS_STATS.DELETE_TABLE_STATS(user,'tuning8_tab3');
DBMS_STATS.LOCK_TABLE_STATS(user,'tuning8_tab3');
END;
/
操作如下：删除统计信息，并且锁定表的统计信息
SQL> BEGIN
2 DBMS_STATS.DELETE_TABLE_STATS(user,'tuning8_tab3');
3 DBMS_STATS.LOCK_TABLE_STATS(user,'tuning8_tab3');
4 END;
5 /
PL/SQL procedure successfully completed.
--查询表tuning8_tab3，由于删除了统计信息，并且锁定表的统计信息，执行时会进行动态采样，这样优化器通常情况下都会产生较正确的查询计划。
SQL> select count(name) from tuning8_tab3 where id=10 ;
COUNT(NAME)
-----------
50001
Execution Plan
----------------------------------------------------------
Plan hash value: 926892911
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 38 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS FULL| TUNING8_TAB3 | 57803 | 2088K| 38 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
225 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
8）.再次加载数据测试，就算再次加载数据，依然会产生最优的查询计划，保证了效率。
SQL> insert into tuning8_tab3 select 9 , 'namess' from dual connect by level <= 100000 ;
100000 rows created.
SQL> commit;
Commit complete.
SQL> set autot trace;
SQL> select count(name) from tuning8_tab3 where id=9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 926892911
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 93 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
|* 2 | TABLE ACCESS FULL| TUNING8_TAB3 | 105K| 3805K| 93 (2)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=9)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
436 consistent gets
0 physical reads
756 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--如下使用index提示（hint)
SQL> select /*+index(tuning8_tab3 idx_tuning8_tab3_id)*/ count(name) from tuning8_tab3 where id=9 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2464990924
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1878 (0)| 00:00:23 |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TUNING8_TAB3 | 105K| 3805K| 1878 (0)| 00:00:23 |
|* 3 | INDEX RANGE SCAN | IDX_TUNING8_TAB3_ID | 105K| | 316 (0)| 00:00:04 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=9)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
978 consistent gets
0 physical reads
26880 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
当然对于一些对查询计划要求比较高的地方，不建议使用这种方法，特别是olap系统，因为动态采样只是一个折中的收集统计信息的方法，并不像使用dbms_stats包那样准确，也有可能生成错误的执行计划
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31397003/viewspace-2157156/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
