合 Oracle中如何找出统计信息过期的表
在ORACLE数据库当中，我们如何判断当前对象的统计信息是否过期/过时（Stale）的呢？从哪些维度去判断呢？我们有那些方法呢？下面介绍ORACLE数据库中一些找出过期统计信息的方法，主要有下面几种方法：
1：DBA_TAB_STATISTICS告诉你统计信息过期
主要通过DBA_STATISTICS中的STALE_STATS列来判断统计信息是否过期，当STALE_STATS字段值为YES时，表示统计信息过期了，NO表示统计信息未过期。这个是最简单的方法。那么ORACLE是如何判断统计信息过期的呢？其实数据库判断统计信息是否过期（stale）的依据是当前表的DML操作的记录数是否超过表数据量的10%，如果超过10%这个阈值，统计信息就被认为是过期了。Oracle就认为这些表应该需要重新收集一次统计信息了。
下面是官方文档的部分介绍：
stale_stats says whether the stats are considered fresh or stale, or if the stats will be gathered automatically next time or not. The default settings is 10 percent. If you gather table statistics and then insert/update/delete less than 10 percent of rows the statistics is considered fresh. When you reach 10 percent of modified rows they become stale.
查看统计信息过期的脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | /******************************************************************************************* --刷新数据库监控信息： exec dbms_stats.flush_database_monitoring_info; For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate these views with the latest information. --通俗的解释 We don't *immediately* up date the staleness, because if we did, we would have to do it every time someone ran a DML statement. So we track it in memory, and flush it to the dictionary from time to time. ********************************************************************************************/ ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; SET LINESIZE 1080 SET PAGESIZE 1000 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; SELECT S.OWNER , S.TABLE_NAME , S.PARTITION_NAME , S.OBJECT_TYPE , S.STALE_STATS , S.LAST_ANALYZED FROM DBA_TAB_STATISTICS S INNER JOIN DBA_TABLES T ON S.OWNER= T.OWNER AND S.TABLE_NAME =T.TABLE_NAME WHERE (S.STALE_STATS = 'YES' OR S.LAST_ANALYZED IS NULL) -- STALE_STATS = 'YES' 表示统计信息过期：当对象有超过10%的ROWS被修改时 -- LAST_ANALYZED IS NULL 表示该对象从未进行过统计信息收集 AND T.TEMPORARY ='N' --排除临时表 AND S.OWNER NOT IN ('MDDATA', 'MDSYS', 'ORDSYS', 'CTXSYS', 'ANONYMOUS', 'EXFSYS', 'OUTLN', 'DIP', 'DMSYS', 'WMSYS', 'XDB', 'ORACLE_OCM', 'TSMSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'OLAPSYS', 'SYSTEM', 'SYS', 'SYSMAN', 'DBSNMP', 'SCOTT', 'PERFSTAT', 'PUBLIC', 'MGMT_VIEW', 'WK_TEST', 'WKPROXY', 'WKSYS') -- 系统用户表的统计信息状态不做统计，根据需求打开或关闭 AND S.TABLE_NAME NOT LIKE 'BIN%' -- 回收站中的表不做统计 ORDER BY OWNER,TABLE_NAME; |
2：通过DBA_TABLES的LAST_ANALYZED列判断
这里主要是通过上一次的收集统计信息的时间来判断。其实根据收集统计信息的时间来判断统计信息是否过时，其实有一定的局限性和不合理性。时间维度来判断太过粗糙。例如对于维表，或者一些数据长期没有DML操作的表来说，以LAST_ANALYZED来判断的话，就有失公允。所以这种方法仅供参考，在有些场合完全是无效的。也就是说从统计信息采集的时间维度来判断，根本是不可靠的，仅供参考。
下面例子，统计信息超过7天没有更新的表。
1 2 3 4 5 6 7 | SET LINESIZE 1080; ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; SELECT OWNER ,TABLE_NAME ,LAST_ANALYZED FROM DBA_TABLES WHERE LAST_ANALYZED < SYSDATE-7; |
3：使用DBMS_STATS.GATHER_SCHEMA_STATS找出统计信息过期的表
使用dbms_stats.gather_schema_stats包找出统计信息过期的对象，如下所示：
找出某个SCHEMA下统计信息过期的对象
1 2 3 4 5 6 7 8 9 10 11 12 13 | set serveroutput on; declare mystaleobjs dbms_stats.objecttab; begin -- check whether there is any stale objects dbms_stats.gather_schema_stats(ownname=>U'&owner', options=>'LIST STALE',objlist=>mystaleobjs); for i in 1 .. mystaleobjs.count loop dbms_output.put_line(mystaleobjs(i).ownname || '.'||mystaleobjs(i).objname); end loop; end; / |
找出整个数据库中统计信息过期的对象
