收集统计信息(三)

动态采样
    动态采样(Dynamic Sampling)技术的最初提出是9iR2，在段对象(表、索引、分区)没有分析的情况下，为了使CBO得到足够的信息以保证做出正确的执行计划而发明的一种技术，
可以把它看做分析手段的一种补充。
     当段上没有统计信息时(即使没有做分析)，动态采样技术可以通过直接从需要分析的对象上采集一些数据块（采样）来后的CBO所需要的统计信息。
     下面用例子来演示效果：
一个简单的例子：
创建表：
SQL> create table t
  2  as
  3  select owner,object_type from all_objects;
 
Table created

查看表的记录数：

SQL> select count(*) from t;
 
  COUNT(*)
----------
     50205-- 记录数

这里创建了一张普通表，没有做分析，我们在hint中用0级来限制动态采样，此时CBO 唯一可以使用的信息就是表存储在数据字典中的一些信息，如有多少个extent，有多少个block，但是这些信息是不够的。

SQL> SET AUTOT TRACE EXP

SQL> SET AUTOT TRACE EXP
SQL> select /*+dynamic_sampling(t 0) */ * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12007 |   328K|    42   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 12007 |   328K|    42   (0)| 00:00:01 |
--------------------------------------------------------------------------

在没有做动态分析的情况下，CBO 估计的记录数是12007条，与真实的50205相差甚远。

我们用动态分析来查看一下：

SQL> select * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 46034 |  1258K|    43   (3)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 46034 |  1258K|    43   (3)| 00:00:01 |
--------------------------------------------------------------------------

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

在Oracle 10g中默认对没有分析的段做动态采样，上面的查询结果显示使用了Level 2级的动态采样，CBO 估计的结果是46034 与50205很接近了。

注意一点：
         在没有动态采样的情况下，对于没有分析过的段，CBO也可能错误地将结果判断的程度扩大话。 如：
SQL> delete from t;
 
50205 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> select /*+dynamic_sampling(t 0) */ * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12007 |   328K|    42   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 12007 |   328K|    42   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    28 |    42   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |    28 |    42   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

如果细心一点，可能看出2个执行计划的差别。 在没有采用动态分析的情况下，CBO 对t表估计的还是12007行记录，但是用动态分析就显示1条记录。 而表中的数据在查询之前已经删除掉了。  出现这种情况的原因是因为高水位。 虽然表的数据已经删除，但是表分配的extent 和block 没有被回收，所以在这种情况下CBO 依然认为有那么多的数据在那。
通过这一点，我们可以看出，此时CBO能够使用的信息非常有限，也就是这个表有几个extent，有几个block。 但动态采样之后，Oracle 立即发现，原来数据块中都是空的。

动态采样有两方面的作用：
（1）CBO 依赖的是充分的统计分析信息，但是并不是每个用户都会非常认真，及时地去对每个表做分析。 为了保证执行计划都尽可能地正确，Oracle 需要使用动态采样技术来帮助CBO 获取尽可能多的信息。
（2）全局临时表。 通常来讲，临时表的数据是不做分析的，因为它存放的数据是临时性的，可能很快就释放了，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。

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

相对的，表分析的信息是独立的。如：
（1）表的行数，平均行长。
（2）表的每个列的最大值，最小值，重复率，也可能包含直方图。
（3）索引的聚合因子，索引叶的块数目，索引的高度等。

 尽管看到动态采样的优点，但是它的缺点也是显而易见，否则Oracle 一定会一直使用动态采样来取代数据分析：

（1）采样的数据块有限，对于海量数据的表，结果难免有偏差。

（2）采样会消耗系统资源，特别是OLTP数据库，尤其不推荐使用动态采样。

2 动态采样的级别
Oracle 为动态采样划分了11个级别，在Oracle 的官网上详细的介绍。
                   13.5.7.4 Dynamic Sampling Levels
              http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#PFGRF94760
The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter:
Level 0: Do not use dynamic sampling.
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).
Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
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 a 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 twice the default number of dynamic sampling blocks.
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.
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.
Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
The sampling levels are as follows if the dynamic sampling level for a table is set using the DYNAMIC_SAMPLING optimizer hint:
Level 0: Do not use dynamic sampling.
Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).
Levels 2, 3, 4, 5, 6, 7, 8, and 9: The number of blocks sampled is 2, 4, 8, 16, 32, 64, 128, or 256 times the default number of dynamic sampling blocks respectively.
Level 10: Read all blocks in the table.
2.1 Level 0
         不做动态分析
2.2 Level 1
         Oracle 对没有分析的表进行动态采样，但需要同时满足以下4个条件。
（1）SQL中至少有一个未分析的表
（2）未分析的表出现在关联查询或者子查询中
（3）未分析的表没有索引
（4）未分析的表占用的数据块要大于动态采样默认的数据块（32个）

2.3 Level 2
         对所有的未分析表做分析，动态采样的数据块是默认数据块的2倍。
2.4 Level 3
         采样的表包含满足Level 2定义的所有表，同时包括，那些谓词有可能潜在地需要动态采样的表，这些动态采样的数据块为默认数据块，对没有分析的表，动态采样的默认块为默认数据块的2倍。
2.5 Level 4
         采样的表包含满足Level 3定义的表，同时还包括一些表，他们包含一个单表的谓词会引用另外的2个列或者更多的列；采样的块数是动态采样默认数据块数；对没有分析的表，动态采样的数据块为默认数据块的2倍。
2.6 Level 5，6，7，8，9
         采样的表包含满足Level 4定义的表，同时分别使用动态采样默认数据块的2，4，8，32，128 倍的数量来做动态分析。
2.7 Level 10
         采样的表包含满足Level 9定义的所有表，同时对表的所有数据进行动态采样。
采样的数据块越多，得到的分析数据就越接近与真实，但同时伴随着资源消耗的也越大。
3 什么时候使用动态采样
动态采样也需要额外的消耗数据库资源，所以，如果 SQL 被反复执行，变量被绑定，硬分析很少，在这样一个环境中，是不宜使用动态采样的，就像OLTP系统。 动态采样发生在硬分析时，如果很少有硬分析发生，动态采样的意义就不大。
而在OLAP或者数据仓库环境下，SQL执行消耗的资源要远远大于SQL解析，那么让解析在消耗多一点资源做一些动态采样分析，从而做出一个最优的执行计划是非常值得的。 实际上在这样的环境中，硬分析消耗的资源几乎是可以忽略的。
所以，一般在OLAP 或者数据仓库环境中，将动态采样的level 设置为3或者4 比较好。 相反，在OLTP系统下，不应该使用动态采样。