分类： Oracle
2016-01-05 17:14:43
原文地址：Oracle收集统计信息与动态采样 作者：icybay
当表没有做分析的时候，Oracle 会使用动态采样来收集统计信息。 获取准确的段对象（表，表分区，索引等）的分析数据，是CBO存在的基石，CBO的机制就是收集尽可能多的对象信息和系统信息，通过对这些信息进行计算，分析，评估，最终得出一个成本最低的执行计划。 所以对于CBO，数据段的分析就非常重要。
一． 先演示一个示例，来理解分析的作用
1.1创建表
SQL> create table t as select object_id,object_name from dba_objects where 1=2;
表已创建。
SQL> create index index_t on t(object_id);
索引已创建。
SQL> insert into t select object_id,object_name from dba_objects;
已创建72926行。
SQL> commit;
提交完成。
1.2查看分的分析及执行计划
SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- --------------
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- --------------
0 0 0 25-8月 -10
从查询结果看出，表的行数，行长，占用的数据块数及最后的分析时间都是空。 索引的相关信息也没有，说明这个表和说因都没有被分析，如果此时有一条SQL 对表做查询，CBO 由于无法获取这些信息，很可能生成错误的执行计划，如：
SQL> set linesize 200
SQL> set autot trace exp;
SQL> select /*+dynamic_sampling(t 0) */ * from t where object_id>30;
执行计划
----------------------------------------------------------
Plan hash value: 80339723
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 316 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 316 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_T | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">30)
SQL>
在Oracle 10g以后，如果一个表没有做分析，数据库将自动对它做动态采样分析，所以这里采用hint的方式将动态采样的级别设置为0，即不使用动态采样。 从这个执行计划，看书CBO 估计出表中满足条件的记录为4条，索引使用了索引。 我们对表做一下分析，用结果比较一下。
1.3 分析表及查看分析之后的执行计划
分析可以通过两中方式：
一种是analyze 命令，如：
analyze table tablename compute statistics for all indexes;
还有一种就是通过DBMS_STATS包来分析，从9i 开始，Oracle 推荐使用DBMS_STATS包对表进行分析操作，因为DBMS_STATS 提供了更多的功能，以及灵活的操作方式。
SQL> exec dbms_stats.gather_table_stats('SYS','T');
PL/SQL 过程已成功完成。
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED
---------- ----------- ------------- --------------
1 263 72926 25-8月 -10
SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- --------------
72926 29 345 25-8月 -10
从上面的结果，可以看出DBMS_STATS.gather_table_stats已经对表和索引都做了分析。 现在我们在来看一下执行计划。
SQL> set autot trace exp;
SQL> select * from t where object_id>30;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72899 | 2064K| 96 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 72899 | 2064K| 96 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">30)
从这个计划，我们看出CBO 估算出的结果是72899 条记录，与实际的72926很近。 此时选择全表扫描更优。 通过这个例子，我们也看出了分析对执行计划的重要性。
二． DBMS_STATS包
DBMS_STAS包不仅能够对表进行分析，它还可以对数据库分析进行管理。 按照功能可以分一下几类：
（1） 性能数据的收集2.1 DBMS_STATS包的几个常用功能：性能的收集，设定 和删除
性能数据的收集包含这样几个存储过程：
GATHER_DATABASE_STATS Procedures
GATHER_DICTIONARY_STATS Procedure
GATHER_FIXED_OBJECTS_STATS Procedure
GATHER_INDEX_STATS Procedure
GATHER_SCHEMA_STATS Procedures
GATHER_SYSTEM_STATS Procedure
GATHER_TABLE_STATS Procedure
2.2 DBMS_STATS包管理功能
2.2.1 获取分析数据
GET_COLUMN_STATS Procedures2.2.2 设置分析数据
SET_COLUMN_STATS Procedures2.2.3 删除分析数据
DELETE_COLUMN_STATS Procedure2.2.4 保存分析数据
CREATE_STAT_TABLE Procedure2.2.5 导入和导出分析数据
EXPORT_COLUMN_STATS Procedure2.2.6 锁定分析数据
LOCK_SCHEMA_STATS Procedure2.2.7 分析数据的恢复
RESET_PARAM_DEFAULTS Procedure三． 动态采样
3.1 什么是动态采样
动态采样（Dynamic Sampling）技术的最初提出是在Oracle 9i R2，在段（表，索引，分区）没有分析的情况下，为了使CBO 优化器得到足够的信息以保证做出正确的执行计划而发明的一种技术，可以把它看做分析手段的一种补充。 当段对象没有统计信息时（即没有做分析），动态采样技术可以通过直接从需要分析的对象上收集数据块（采样）来获得CBO需要的统计信息。
一个简单的例子：
创建表：
SQL> create table t
2 as
3 select owner,object_type from all_objects;
表已创建。
查看表的记录数：
SQL> select count(*) from t;
COUNT(*)
----------
72236 -- 记录数
这里创建了一张普通表，没有做分析，我们在hint中用0级来限制动态采样，此时CBO唯一可以使用的信息就是表存储在数据字典中的一些信息，如有多少个extent，有多少个block，但是这些信息是不够的。
SQL> set auto traceonly explain
SQL> select /*+dynamic_sampling(t 0) */ * from t;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15928 | 435K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 15928 | 435K| 55 (0)| 00:00:01 |
在没有做动态分析的情况下，CBO 估计的记录数是15928条，与真实的72236相差甚远。
我们用动态分析来查看一下：
SQL> select * from t;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80232 | 2193K| 56 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 80232 | 2193K| 56 (2)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
在Oracle 10g中默认对没有分析的段做动态采样，上面的查询结果显示使用了Level 2级的动态采样，CBO 估计的结果是80232 与72236 很接近了。
注意一点：
在没有动态采样的情况下，对于没有分析过的段，CBO也可能错误地将结果判断的程度扩大化。 如：
SQL> delete from t;
已删除72236行。
SQL> commit;
提交完成。
SQL> select /*+dynamic_sampling(t 0) */ * from t;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15928 | 435K| 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 15928 | 435K| 55 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from t;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 55 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 1 | 28 | 55 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
如果细心一点，可能看出2个执行计划的差别。 在没有采用动态分析的情况下，CBO 对t表估计的还是15928行记录，但是用动态分析就显示1条记录。 而表中的数据在查询之前已经删除掉了。 出现这种情况的原因是因为高水位。 虽然表的数据已经删除，但是表分配的extent 和block 没有被回收，所以在这种情况下CBO 依然认为有那么多的数据在那。
通过这一点，我们可以看出，此时CBO能够使用的信息非常有限，也就是这个表有几个extent，有几个block。 但动态采样之后，Oracle 立即发现，原来数据块中都是空的。
动态采样有两方面的作用：
（1） CBO 依赖的是充分的统计分析信息，但是并不是每个用户都会非常认真，及时地去对每个表做分析。 为了保证执行计划都尽可能地正确，Oracle 需要使用动态采样技术来帮助CBO 获取尽可能多的信息。
（2） 全局临时表。 通常来讲，临时表的数据是不做分析的，因为它存放的数据是临时性的，可能很快就释放了，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。
动态采样除了可以在段对象没有分析时，给CBO提供分析数据之外，还有一个独特的能力，它可以对不同列之间的相关性做统计。相对的，表分析的信息是独立的。 如：
（1） 表的行数，平均行长。
（2） 表的每个列的最大值，最小值，重复率，也可能包含直方图。
（3） 索引的聚合因子，索引叶的块数目，索引的高度等。
尽管看到动态采样的优点，但是它的缺点也是显而易见，否则Oracle 一定会一直使用动态采样来取代数据分析：
（1） 采样的数据块有限，对于海量数据的表，结果难免有偏差。
（2） 采样会消耗系统资源，特别是OLTP数据库，尤其不推荐使用动态采样。
3.2 动态采样的级别
3.2.1 Level 0
不做动态分析
3.2.2 Level 1
Oracle 对没有分析的表进行动态采样，但需要同时满足以下4个条件。
（1） SQL中至少有一个未分析的表
（2） 未分析的表出现在关联查询或者子查询中
（3） 未分析的表没有索引
（4） 未分析的表占用的数据块要大于动态采样默认的数据块（32个）
3.2.3 Level 2
对所有的未分析表做分析，动态采样的数据块是默认数据块的2倍。
3.2.4 Level 3
采样的表包含满足Level 2定义的所有表，同时包括，那些谓词有可能潜在地需要动态采样的表，这些动态采样的数据块为默认数据块，对没有分析的表，动态采样的默认块为默认数据块的2倍。
3.2.5 Level 4
采样的表包含满足Level 3定义的表，同时还包括一些表，他们包含一个单表的谓词会引用另外的2个列或者更多的列；采样的块数是动态采样默认数据块数；对没有分析的表，动态采样的数据块为默认数据块的2倍。
3.2.6 Level 5，6，7，8，9
采样的表包含满足Level 4定义的表，同时分别使用动态采样默认数据块的2，4，8，32，128 倍的数量来做动态分析。
3.2.7 Level 10
采样的表包含满足Level 9定义的所有表，同时对表的所有数据进行动态采样。
采样的数据块越多，得到的分析数据就越接近与真实，但同时伴随着资源消耗的也越大。
3.3 什么时候使用动态采样
动态采样也需要额外的消耗数据库资源，所以，如果 SQL被反复执行，变量被绑定，硬分析很少，在这样一个环境中，是不宜使用动态采样的，就像OLTP系统。 动态采样发生在硬分析时，如果很少有硬分析发生，动态采样的意义就不大。
而在OLAP或者数据仓库环境下，SQL执行消耗的资源要远远大于SQL解析，那么让解析在消耗多一点资源做一些动态采样分析，从而做出一个最优的执行计划是非常值得的。 实际上在这样的环境中，硬分析消耗的资源几乎是可以忽略的。
所以，一般在OLAP或者数据仓库环境中，将动态采样的level 设置为3或者4 比较好。 相反，在OLTP系统下，不应该使用动态采样。
转自: http://blog.csdn.net/tianlesoftware/article/details/5845028
