1.ANALYZE和DBMS_STATS包收集统计信息的对比

1.统计信息分类

统计信息分为6类
1).表的统计信息
2).索引的统计信息
3).列的统计信息
4).系统统计信息
5).数据字典统计信息
6).内部对象统计信息

2.什么是系统统计信息

系统统计信息是用于描述Oracle数据库所在的数据库服务器的系统处理能力,它包含了CPU和IO这
两个维度,借助于系统统计信息,Oracle可以更清楚地知道目标数据库服务器的实际处理能力。

X$ 系列表实际上只是Oracle自定义的内存结构，并不实际占用物理存储空间。

oracle数据库里,通常有两种方法可以用来收集统计信息:一种是使用ANALYZE 命令,另一种是使用DBMS_STATS包
收集均可。但系统统计信息和系统内部对象统计信息只能使用DBMS_STATS包来收集。

3.收集系统内部表的统计信息

exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'X$BH');

4.使用ANALYZE收集统计信息

从Oracle7开始,ANALYZE命令可以用来收集表,索引,列的统计信息以及系统统计信息。

create table t2 as select * from dba_objects;
create index idx_t2 on t2(object_id);

从Oracle10g开始,在创建索引后Oracle会自动收集目标索引的统计信息。
analyze index idx_t2 delete statistics;   --删除索引的统计信息

--以估算收集统计信息,采样比例15%
analyze table t2 estimate statistics sample 15 percent for table; 

--计算模式收集统计信息
analyze table t2 compute statistics for table;
--计算模式会扫描目标对象所有数据,所以统计结果和实际结果是匹配的。

--收集列的统计信息
analyze table t2 compute statistics for columns object_name,object_id;
--对于同一个对象而言,新执行的ANALYZE并不会抹掉之前ANALYZE的结果。

--以计算模式对表和列收集统计信息
analyze table t2 compute statistics for table for columns object_name,object_id;


--以计算模式收集索引统计信息。
analyze index idx_t2 compute statistics;

--删除表的统计信息同时删除列的统计信息。
analyze table t2 delete statistics;

--同时收集表,所有列,所有索引的统计信息。
analyze table t2 compute statistics;

5.用DBMS_STATS包收集统计信息

从oracle8.1.5开始,DBMS_STATS包被广泛用于统计信息的收集,用DBMS_STATS包收集统计信息也是Oracle官方
推荐的方式。

DBMS_STATS 包里面最常用的4个存储过程。
GATHER_TABLE_STATS:用于收集目标表,目标表的列和目标表的索引的统计信息。
GATHER_INDEX_STATS:用于收集指定索引的统计信息
GATHER_SCHEMA_STATS:用于收集指定schema下所有对象的统计信息
GATHER_DATABASE_STATS:用于收集全库所有对象的统计信息。

--以估算模式,采样比例15%收集统计信息:只收集表的统计信息。
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>15,method_opt=>'FOR TABLE',cascade=>false);

--如果是以计算模式收集,用DBMS_STATS包实现的方法就是将估算模式采样比例设置100%,或NULL; 
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>false);
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>NULL,method_opt=>'FOR TABLE',cascade=>false);

--对列以计算模式收集统计信息。
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>NULL,method_opt=>'for columns size 1 object_name object_id',cascade=>false);
--收集列时表的统计信息也被收集。ANALYZE可以只收集列的统计信息而不收集表的统计信息。

--以计算模式收集索引的统计信息
exec dbms_stats.gather_index_stats(ownname=>'SCOTT',indname=>'IDX_T2',estimate_percent=>100);

--删除表,所有列,所有索引的统计信息 
exec dbms_stats.delete_table_stats(ownname=>'SCOTT',tabname=>'T2');

--收集表,所有列,所有索引的统计信息。
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true);

6.ANALYZE 和 DBMS_STATS 包的区别

ANALYZE 命令不能正确地收集分区表的统计信息,而 DBMS_STATS 包却可以。
ANALYZE 命令不能并行收集统计信息,DBMS_STATS 包却可以。
DBMS_STATS,并行收集能够减少时间。
--并行收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',cascade=>true,estimate_percent=>100,degree=>4);

与ANALYZE相比,缺陷在于DBMS_STATS包只能收集与CBO相关的统计信息,而与CBO无关的一些额外信息,比如行迁移,行链接的数量
(CHAIN_CNT),校验表和索引的结构信息,DBMS_STATS 是不行的。而ANALYZE 命令可以分析上述信息。

7.总结

收集统计信息建议使用DBMS_STATS包,有很多丰富的存储过程包可以收集各个维度的统计信息。

任何迁移后，或者导入数据后都必须及时收集统计信息，否则可能因为统计信息不准确导致CBO无法选择最优的执行计划，从而导致SQL性能差。