【DB笔试面试645】在Oracle中，当收集表的统计信息时应该注意哪些问题？

• 2019 年 10 月 10 日
• 筆記

题目部分

在Oracle中，当收集表的统计信息时应该注意哪些问题？

♣

答案部分

关于收集统计信息需要注意以下几点：

①　对于数据量不大的OLTP类型的系统，建议使用自动收集统计信息，并对一些特殊的大表写JOB定时收集统计信息。如果是数据量很大的OLAP或者DSS系统，那么建议DBA自己写JOB脚本来收集统计信息。

②　在导入大量数据后应及时收集统计信息后才能进行相关的后续业务处理（包括查询和修改），否则可能会由于实际数据量和统计信息里记录的数据量存在巨大差异而导致CBO选择错误的执行计划。

③　全局临时表默认不能收集统计信息，在生成执行计划时采用动态采样比较好。

④　对于某些新上线或新迁移的系统，建议进行全库收集一次统计信息。

⑤　建议及时对包含日期型字段的表收集统计信息，避免出现谓词越界现象。

⑥　统计信息收集作业采样比例：对于Oracle 11g及其以上的版本收集统计信息的采样比例建议采用DBMS_STATS.AUTO_SAMPLE SIZE。如果是Oracle 10g，那么建议将采样比例的初始值设为30％，然后根据目标SQL的实际执行情况再做调整。

⑦　系统统计信息：如果系统的硬件环境发生了变化，那么建议要额外收集一次系统统计信息。

⑧　内部对象统计信息：在明确诊断出系统已有的性能问题是因为X$表的内部对象统计信息不准引起的，这个时候就应该收集X$表的内部对象统计信息，其它情形就不要收集了。

⑨　表的大小、是否并行：若表很大，而系统空闲，则可以使用并行来收集统计信息。

⑩　表是否分区：若是分区表则建议收集全局的统计信息并且收集数据量有变更的单个分区（加GRANULARITY和参数并设置属性INCREMENTAL）的统计信息。

⑪　是否收集索引的统计信息：一般情况下都应该收集索引的统计信息。

⑫　是否收集直方图。对直方图统计信息的收集策略是对已经存在直方图统计信息的列才收集直方图统计信息，而目标列的初次直方图统计信息则是由了解系统的DBA手工来收集直方图。设置METHOD_OPT的值为“FOR ALL COLUMNS SIZE REPEAT”。

⑬　是否可以并发收集统计信息：若系统有很多小表，则可以考虑并发收集统计信息。

⑭　系统的负载情况：在手动收集统计信息的时候需要注意系统的负载情况。

⑮　预估多久可以收集完成：对OLAP系统的大表而言，根据平时收集统计信息的经验要预估出收集统计信息要花费多长的时间。

⑯　基于数据库、SCHEMA或是表级别：根据情况判断是否有必要在数据库或SCHEMA级别来收集统计信息。

⑰　是否需要收集扩展列的统计信息。如果表中的数据倾斜度较大，那么收集直方图能最大程度的帮助优化器计算出准确的Cardinality，从而避免产生差的执行计划；再进一步，如果存在倾斜的多个列共同构成了Predicate里的等值连接且这些列间存在较强的列相关性的话，那么生成带有直方图的多列统计信息是一个上佳的选择，能够最大程度的帮助优化器准确预测出Cardinality。

⑱　是否设置NO_INVALIDATE为FALSE。该选项有TRUE、FALSE和DBMS_STATS.AUTO_INVALIDATE这3个值。如果取值为TRUE，那么表示收集统计信息后不进行游标失效动作，原有的Shared Cursor保持原有状态。如果取值为FALSE，那么表示将统计信息对象相关的所有Cursor全部失效，目标SQL语句在下次执行时就会使用硬解析。如果设置为AUTO_INVALIDATE，那么Oracle自己决定Shared Cursor失效动作，当SQL再次执行时间距离上次收集统计信息的时间超过5小时（隐含参数“_OPTIMIZER_INVALIDATION_PERIOD”决定）则对SQL重新做硬解析。AUTO_INVALIDATE为默认选项。有些DBA在收集统计信息时，没有使用NO_INVALIDATE=>FALSE选项，所以，即使收集了统计信息，执行计划也不会立即改变。可以在表级别设置让所有依赖于该表的游标不失效，设置方法为：

EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','NO_INVALIDATE','TRUE');--在收集SH.SALES表上的统计信息时，让所有依赖于该表的游标不失效  

⑲　对于OLTP类型的数据库，需要特别关注DML比较频繁的以及数据加载比较大的表及分区表。

⑳　检查是否有临近统计信息收集窗口的数据加载工作，如果有，是否能在数据库统计信息的窗口时间完成，如果不能在窗口时间完成，那么应该针对这段时间加载的数据，特别是大量的数据，在相关加载脚本完成之后，加入统计信息的收集。

21　如果加载数据量比较大，并且是分区表，每个分区的业务数据呈现的是均匀的，在Oracle 11g可以考虑采用DBMS_STATS.COPY_TABLE_STATS先把统计信息做个快速的设置，然后，再收集该分区的统计信息。

其实，上述几点是没有一个普适性的标准答案的，因为不同的系统其数据量、数据分布情况都不尽相同，甚至可能会有很大的区别，所以适合于某套系统的统计信息收集策略并不一定能适用于另外一套系统。收集统计信息总的原则就是量体裁衣，即要找到适合自己系统的统计信息收集策略，用尽量小的代价收集到能稳定跑出正确执行计划的统计信息即可，也就是说收集到的统计信息不一定要特别准，只要具备代表性，能稳定跑出正确的执行计划就可以了。

本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗