深入理解Oracle优化器(3)：动态采样

动态采样的统计信息不会记录到视图，有时候分析过的表也会参与评估
对CBO有效，因为RBO不需要统计信息,所以动态采样没意义

㈠ 动态采样的作用

① 10G开始，RBO彻底退出了历史舞台，CBO依赖的是充分的统计分析信息
但是并不是每个用户都会非常认真、及时地去对每个表做分析
为了保证执行计划都尽可能正确，Oracle需要使用动态采样技术来帮助CBO获得尽可能多的信息

② 全局临时表。通常而言，临时表的数据是不做分析的、因为它存放的数据是临时的、可能很快就释放
但是当一个查询关联到这样的临时表时、CBO要想获得临时表上的统计信息，也只能依赖于动态采样

③ 动态采样除了可以在段对象没有分析时，给CBO提供分析数据之外，还有一个独特的能力
它可以对不同列之间的相关性做统计

㈡ 动态采样的缺点

● 采样的数据块有限，对于海量数据的表，难免有偏差
● 采样会消耗系统资源，特别是OLTP尤其不推荐使用动态采样

㈢ 动态采样的级别

① level 0

不做动态采样

② level 1

CBO对没有分析的表进行动态采样，但需要同时满足以下4个条件：

● SQL中至少有一个未分析的表
● 未分析的表出现在关联查询或者子查询中
● 未分析的表没有索引
● 未分析的表占用的数据块要大于动态采样默认的数据块（32个）

③ level 2

对所有的未分析表做分析，动态采样的数据块是默认数据块的2倍

④ level 3

采样的表包含level 2定义的所有表，同时包括，哪些谓词有可能潜在地需要动态采样的表
这些表动态采样的数据块为默认数据块；对没有分析的表，动态采样的数据块为默认数据块的2倍

⑤ level 4

采样的表包含level 3定义的所有表，同时还包括一些表，WHERE字句引用了两个或多个字段的表
这些表动态采样的数据块为默认数据块；对没有分析的表，动态采样的数据块为默认数据块的2倍

⑥ level 5、6、7、8、9

采样的表包含level 4定义的所有表，同时分别使用动态采样默认数据块数的2、4、8、32、128倍的数量来做动态采样

⑦ level 10

采样的表包含level 9定义的所有表，同时对表的所有数据进行动态采样

采样的数据块越多、得到的分析数据就越接近于真实，但同时伴随着资源的消耗也就越大

hr@ORCL> select count(*) from t;

  COUNT(*)
----------
     50385

hr@ORCL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed.

hr@ORCL> select last_analyzed from user_tables where table_name='T';

LAST_ANALYZED
------------------


hr@ORCL> set autot trace exp
hr@ORCL> select /*+ dynamic_sampling(t 1) */ * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 36932 |  6383K|   160   (2)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T    | 36932 |  6383K|   160   (2)| 00:00:02 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

hr@ORCL> select /*+ dynamic_sampling(t 2) */ * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 43387 |  7499K|   160   (2)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T    | 43387 |  7499K|   160   (2)| 00:00:02 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

hr@ORCL> select /*+ dynamic_sampling(t 5) */ * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 52196 |  9022K|   161   (3)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T    | 52196 |  9022K|   161   (3)| 00:00:02 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

㈣ 什么时候使用动态采样

动态采样发生在硬解析时，如果很少有硬解析发生，动态采样的意义不大
在OLAP或者数据仓库环境中，将动态采样的level 设置为3 或 4 比较好
相反地，在OLTP系统，不应该使用动态采样
但是，如果OLTP用到临时表，也可以用动态采样

有一个案例：

XX刚入公司的时候，写了第一个存储过程，结果在生产上把8G的临时表空间给干掉了，跑了1小时没有反馈
后来当时的开发dba看后，做了一些细小的调整，程序马上就跑完了
原来XX使用了一张临时表，往里面插入了大量的中间数据，然后由于临时表是没有统计信息的，SQL也复杂
最后对临时表走了merge carestion ，原因就是认为临时表数据很小，而实际情况恰恰相反
开发DBA当时是这样修改的，首先将查询放到一个子查询中，对子查询使用no_merge提示，然后再关联临时表
同时对临时表收集级别4的统计信息，用的是动态采样，马上就变成hash join了，问题也就解决了