[toc]

概念描述

Oracle收集失效的统计信息的策略：自上次自动统计信息收集作业完成之后，如果DBA_TAB_MODIFICATIONS中记录的INSERT+UPDATE+DELETE所影响的行记录之和超过了DBA_TABLES中目标表记录数的10%，或者是自上次统计信息收集完成之后目标表执行过truncate操作，那么 Oracle 会认为目标表的统计信息已经失效，自动统计信息收集作业就会对目标表重新收集统计信息。

但是对于一些超大表，可能数据变化超过10%需要很长时间，这就导致统计信息很长时间搜集一次，可能导致统计信息不是最新，从而产生执行计划不准的情况。

可以通过EXEC dbms_stats.set_table_prefs(ownname => 'MES',tabname => 'METABLEXXX',pname => 'STALE_PERCENT',pvalue => 5); 来修改这个10%的阈值为5%

测试验证

查看初始的阈值：

1
2
3
4
5
6
7
SQL> SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'MES',tabname => 'METABLEXXX') FROM dual;

DBMS_STATS.GET_PREFS(PNAME=>'S
 -----------------------------------------------
 10
                     
SQL>

修改阈值为5：

1
2
3
SQL> EXEC dbms_stats.set_table_prefs(ownname => 'MES',tabname => 'METABLEXXX',pname   => 'STALE_PERCENT',pvalue  => 5);

PL/SQL procedure successfully completed.

确认修改后的阈值：

1
2
3
4
5
6
7
SQL>  SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'MES',tabname => 'METABLEXXX') FROM dual;

DBMS_STATS.GET_PREFS(PNAME=>'S
--------------------------------------------------------------------------------
5

SQL>

注意：当阈值为0时，不管数据如何变化，每天都会自动收集统计信息。

知识总结

对于大表统计信息的阈值设置为10%，可能导致表的信息搜集不及时，产生执行计划不准确的情况，通过 EXEC dbms_stats.set_table_prefs(ownname => 'MES',tabname => 'METABLEXXX',pname => 'STALE_PERCENT',pvalue => 5) 语句可以手工设置其阈值，甚至可以设置为0，这样就可以每天都能进行自动收集统计信息了。

参考文档

无

原文作者: liups.com

原文链接: http://liups.com/posts/987d11d6/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议