http://blog.csdn.net/weiwangsisoftstone/article/details/8554055
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');
查看统计信息状态
SELECT /*+ UNNEST */
DISTINCT TABLE_NAME, LAST_ANALYZED, STALE_STATS
FROM DBA_TAB_STATISTICS
WHERE LAST_ANALYZED IS NULL OR STALE_STATS = 'YES' AND OWNER = 'SCOTT';