Oracle统计信息（四）—— 常用统计信息 及 统计信息收集原则与建议

包含以下类别统计信息：

• 全局统计信息
• 系统统计信息
• 数据字典统计信息
• 内部对象统计信息

 

一、 全局统计信息

1. 定义

全局统计信息是指直接从对象这一级收集到的统计信息，而不是由其下一级对象“推导”或“汇总”得到的信息。例如分区表的全局统计信息是指直接从表这一级收集到的统计信息，而非由其分区统计信息“推导”或“汇总”得到。

从全局统计信息的定义可以看出，它只对分区表及分区索引有意义。

 

2. 全局统计信息收集

dbms_stats.gather_table_stats中的granularity参数专用于指定全局统计信息的收集策略。

可选值如下，这些值指定了哪个级别上的统计信息会被收集：

• global（表）
• partition（分区）
• subpartition（子分区）
• global and partition（表+分区）
• all（表+分区+子分区）
• auto（默认，由oracle根据分区表类型自动选择）

exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',estimate_percent => 100,granularity=>'all',degree=>4);
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',estimate_percent => 100,granularity=>'partition');

分区及统计信息相关数据字典中的global_stats列可用于判断相关对象是否收集了全局统计信息

 

3. 注意事项

使用dbms_stats收集全局统计信息时，如果将granularity指定为较低级别，更低级的统计信息会保持不变。如果高一级的统计信息之前global_stats=yes，则保持不变；如果为no，会由收集的这一级进行推导汇总得出。推导汇总不能跨级别，例如收集子分区统计信息只可能推导至分区级，无法推导至表级。

analyze命令只能在最低级别收集统计信息，高级别统计信息只能由推导汇总得出，推导规则与dbms_stats相同。

应该使用dbms_stats收集和删除全局统计信息，收集时应该使用一致的granularity参数。

 

二、 系统统计信息

系统统计信息是用于描述oracle数据库服务器系统处理能力的辅助统计信息。主要包括：cpu主频、单块读平均耗时、多块读平均耗时、单块多次读所能读取的数据块平均值等。借助系统统计信息，oracle可以更清楚目标服务器的实际处理能力。

收集方法

• 执行dbms_stats.gather_system_stats('start') 开始收集系统统计信息
• 实际使用系统，最好能以系统典型负载运行一段时间
• 执行dbms_stats.gather_system_stats('stop') 结束

由上述收集方法可以看出，系统统计信息实际代表了数据库服务器在指定负载下的cpu和io处理能力，从此CBO将包括IO和CPU cost，另外对原有全表扫描的IO cost计算方法也发生了变化。

 

三、 数据字典统计信息

数据字典统计信息用于描述数据字典基表（例如TAB$,IND$）、索引、列的信息，其实与普通表没有太大区别。

dbms_stats.gather_dictionary_stats和dbms_stats.delete_dictionary_stats用于收集和删除数据库的数据字典统计信息。

当然也可以用dbms_stats.gather_table_stats等收集单独的统计信息

exec dbms_stats.gather_table_stats(ownname='SYS',tabname=>'TAB$',cascade=>true);

 

四、 内部对象统计信息

内部对象统计信息用于描述内部表（例如X$系列表）的详细信息。X$系列表实际上oracle自定义的内存结构，存储于实例相关的各种信息，它们是数据库运行的基础。

如果内部表的统计信息不准，轻则相关操作变慢，重则hang住整个数据库。

dbms_stats.gather_fixed_objects_stats和dbms_stats.delete_fixed_objects_stats用于收集和删除数据库的内部表统计信息。

当然也可以用dbms_stats.gather_table_stats等收集单独的统计信息

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

oracle默认不会收集内部对象统计信息，包括默认的自动统计信息收集作业。建议仅在明确诊断出系统出现性能问题是因为内部对象统计信息不准时才收集。

注意事项

• gather_fixed_objects_stats执行耗时可能比较长，特别是在系统处于高负载状态时，收集可能会加剧cpu使用甚至导致系统hang住。所以，应该在系统有一定负载但又不是很忙时进行收集，在完全空闲时收集的内部对象统计信息没有代表性。
• 由于X$表实际是内存结构，因此RAC环境下如果要收集，应该对每个节点都执行
• 如果能准确定位到是哪个内部对象统计信息有误，建议使用gather_table_stats单独收集此对象统计信息。

 

五、 统计信息收集原则与建议

量体裁衣，找到适合自己系统的统计信息收集策略，用尽量小的代价收集到能稳定跑出正确执行计划的统计信息即可。也就是说统计信息不一定要特别准，只要有代表性，足够跑出正确执行计划就可以了。

• 表中导入大量数据后，如果业务马上会查询相关表，建议在业务查询前先收集对应表统计信息。对于新建或迁移的空表，导入少量数据后也应该及时收集相关统计信息
• 及时对包含日期类型字段的表收集统计信息，避免谓词越界
• 对于采样比例，11g开始建议用dbms_stats.auto_sample_size，10g及以下建议为30%，当然主要根据实际情况而定
• 应该使用dbms_stats收集和删除分区表全局统计信息，收集时应该使用一致的granularity参数。
• 建议额外收集系统统计信息，除非系统硬件环境发生变化，否则收集一次就够了
• 仅在明确诊断出系统出现性能问题是因为内部对象统计信息不准时才收集内部对象统计信息
• 只对已存在直方图的列重复收集直方图信息，初次直方图信息收集由了解系统的DBA手工操作

 

参考：《基于oracle的sql优化》