数据库使用中的陷阱之统计信息(三)

作者韩涛
文章来自微信公众号平台人生

介绍完统计信息自动任务失效的一些复杂的特殊场景，这期我们来看下一些相对简单的场景，在这些场景中执行相关操作后应该立即执行手工收集。

1. 在日常的维护中，新建、修改对象等操作是不可避免的。如果在程序中或手动执行了新增对象（新增表、分区、索引、表字段等）、对象结构定义修改（ALTER TABLE操作、ALTER INDEX等）等操作后，需要立即对受影响对象进行统计信息收集。对于数据库中对象已锁定统计信息等特殊情况，需要添加FORCE参数，见语句1。
2. 不同对象之间如果存在关联，那么统计信息收集时必须同
   时收集，例如：表和索引必须同时收集，避免表和索引信息不对称导致性能问题，需要在收集语句中添加cascade参数，见语句1。
3. ORACLE 11g数据库新增了很多新特性，柱状图就是其中之一。但从实践中的使用效果来看新引入的特性还不够稳定，所以在统计信息收集时应该指定method_opt参数，禁止收集对象字段上的柱状图，见语句1。
4. 如果在你的数据库中，一些对象（例如：表、索引等）统计信息已经被锁定，那么你需要注意了，后续在对这些对象进行字段定义修改、新增字段、新增索引、新增分区等动作之后必须要手工收集统计信息了。因为在ORACLE中，如果对象被锁定，与之相关的新建对象是不会自动收集统计信息的，也就是说新建对象的统计信息当前为空！后果很严重！所以为了避免因为统计信息缺失导致严重后果，我们在对数据库结构、对象定义修改后应该针对性进行统计信息收集（已锁定的须进行强制收集，强制收集增加使用参数force=>true）。查看已被锁定对象的语句如下见语句2.

语句1：
exec dbms_stats.gather_table_stats(ownname=>'',tabname=>'',cascade=>true,estimate_percent=>XXX,method_opt=>’for all columns size 1’,force=>true);
注：estimate_percent为采样率，XXX为[10-100]的数字，一般情况下建议200GB以下的表均选用XXX=100的采样率；Method_opt选项中for all columns size 1表示对所有字段不收集直方图。

语句2：
SELECT TABLE_NAME,STATTYPE_LOCKED
FROM USER_TAB_STATISTICS
WHERE STATTYPE_LOCKED IS NOT NULL;

SELECT INDEX_NAME,STATTYPE_LOCKED
FROM USER_IND_STATISTICS
WHERE STATTYPE_LOCKED IS NOT NULL;