oracle统计信息收集之dbms_stats

dbms_stats包收集统计信息：

dbms_stats 包含很多存储过程，为收集、删除、导出、导入统计信息提供了存储过程常见如下，详细的请参见官方文档PL/SQL Packages and Types Reference
dbms_stats包收集统计信息是oracle推荐使用的

DBMS_STATS包中用于收集统计信息常用的过程：
EXPORT_COLUMN_STATS：导出列的分析信息
EXPORT_INDEX_STATS：导出索引分析信息
EXPORT_SYSTEM_STATS：导出系统分析信息
EXPORT_TABLE_STATS：导出表分析信息
EXPORT_SCHEMA_STATS：导出方案分析信息
EXPORT_DATABASE_STATS：导出数据库分析信息
IMPORT_COLUMN_STATS：导入列分析信息
IMPORT_INDEX_STATS：导入索引分析信息
IMPORT_SYSTEM_STATS：导入系统分析信息
IMPORT_TABLE_STATS：导入表分析信息
IMPORT_SCHEMA_STATS：导入方案分析信息
IMPORT_DATABASE_STATS：导入数据库分析信息
GATHER_INDEX_STATS：分析索引信息
GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息
GATHER_SCHEMA_STATS：分析方案信息
GET_COLUMN_STATS：获取字段的统计信息
GET_SYSTEM_STATS：获取系统的统计信息
GET_INDEX_STATS：获取索引的统计信息
GET_TABLE_STATS：获取表的统计信息
SET_COLUMN_STATS：设置字段的统计信息。通常应用在测试环境，也不排除在极端情况下起到奇效。
SET_SYSTEM_STATS：设置系统的统计信息
SET_INDEX_STATS：设置索引的统计信息
SET_TABLE_STATS：设置表的统计信息
DELETE_COLUMN_STATS：删除字段的统计信息
DELETE_SYSTEM_STATS：删除系统的统计信息
DELETE_INDEX_STATS：删除索引的统计信息
DELETE_TABLE_STATS：删除表的统计信息
DELETE_DATABASE_STATS：删除数据库的统计信息
DELETE_DICTIONARY_STATS：删除数据字典的统计信息
DELETE_SCHEMA_STATS：删除用户方案的统计信息
DELETE_FIXED_OBJECTS_STATS：删除固定对象的统计信息
GATHER_SCHEMA_STATS：分析方案信息
GATHER_DATABASE_STATS：分析数据库信息
GATHER_SYSTEM_STATS：分析系统信息
CREATE_STAT_TABLE：建立存放统计信息的表
DROP_STAT_TABLE：删除存放统计信息的表
LOCK_TABLE_STATS：锁定表的统计信息。当觉得当前统计信息非常好，且表数据几乎不变化时，可以考虑锁定统计信息，锁定之后相关的所有数据分析，包括表级，列级，直方图、索引的分析都将被锁定，不允许被更新。
LOCK_SCHEMA_STATS：锁定用户方案的统计信息
UNLOCK_TABLE_STATS：解锁表的统计信息
UNLOCK_SCHEMA_STATS：解锁用户方案的统计信息
RESTORE_SYSTEM_STATS：还原系统的统计信息
RESTORE_INDEX_STATS：还原索引的统计信息
RESTORE_TABLE_STATS：还原表的统计信息
RESTORE_DATABASE_STATS：还原数据库的统计信息
RESTORE_DICTIONARY_STATS：还原数据字典的统计信息
RESTORE_SCHEMA_STATS：还原用户方案的统计信息
RESTORE_FIXED_OBJECTS_STATS：还原固定对象的统计信息

DBMS_STATS.GATHER_TABLE_STATS的语法如下:
DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2,
   tabname          VARCHAR2,
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type
                                                (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);

参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的
通过设置 method_opt参数可以智能地生成直方图,具体取值如下:
for all columns:10g默认值(根据版本的不同，默认值也会有所差异)，统计所有列的histograms.
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254];
REPEAT上次统计过的histograms;
AUTO由oracle决定N的大小;
SKEWONLY选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。
假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。
degree:决定并行度.默认值为null.
granularity:设置分区表收集统计信息的粒度，分别有
all：对表全局，分区，子分区的数据都做分析
auto：Oracle根据分区的类型，自动决定做哪一种粒度的分析
global：只做全局级别的分析
global and partition：只对全局和分区级别做分析，对子分区不做分析，这是和all的一个区别
partition：只做分区级别做分析
subpartition：只做子分区做分析
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T3', GRANULARITY => 'SUBPARTITION', CASCADE => TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T2', GRANULARITY => 'PARTITION', CASCADE => TRUE);   
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T1', GRANULARITY => 'GLOBAL', CASCADE => TRUE);
其中，T1为全表，T2为分区，T3为子分区
cascace:是收集索引的信息.默认为falase.
stattab指定要存储统计信息的表；
statid如果多个表的统计信息存储在同一个stattab中用于进行区分；
statown存储统计信息表的拥有者。以上三个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表锁住了也收集统计信息.

其他过程参数说明参见oracle官方文档
例子：
BEGIN
   DBMS_STATS.CREATE_STAT_TABLE ('hr', 'savestats'); ---创建存储统计信息的统计信息表
   DBMS_STATS.GATHER_TABLE_STATS ('hr', 'employees', stattab => 'savestats');  --收集表的统计信息并导出到统计信息表里
END;

BEGIN
   DBMS_STATS.DELETE_TABLE_STATS ('hr', 'employees'); ---删除表的统计信息
   DBMS_STATS.IMPORT_TABLE_STATS ('hr', 'employees', stattab => 'savestats'); --从一张统计信息表把统计信息导入到当前的模式下
END;

收集整个模式的统计信息：
exec dbms_stats.gather_schema_stats(ownname=>'hr');
收集模式中任何“过时”的表的统计信息
exec dbms_stats.gather_schema_stats(ownname=>'hr'，options=>'gather stale');