[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 国际许可协议