瀟湘隱者

在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.

查看統計信息過期的腳本

/*******************************************************************************************

--刷新數據庫監控信息：
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天沒有更新的表。

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下統計信息過期的對象

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;
/

--找出整個數據庫中統計信息過期的對象

set serveroutput on

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/

--找出沒有統計信息的表

set serveroutput on;

declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'&owner', options=>'LIST EMPTY',objlist=>
mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).ownname || '.'||mystaleobjs(i).objname);
end loop;
end;
/

參考資料:How to List the Objects with Stale Statistics Using dbms_stats.gather_schema_stats options=>'LIST STALE' (Doc ID 457666.1)

4：手工驗證統計信息是否過舊

通過系統視圖DBA_TAB_MODIFICATIONS來判斷統計新是否過期。DBA_TAB_MODIFICATIONS使用來記錄表的DML操作，依靠里面的信息確定統計信息是否陳舊。確定表是否需要統計分析的依據。系統后臺調用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO更新，當然也可以手工調用更新信息。它的原理也是數據變化量是否超過10%。

DBA_TAB_MODIFICATIONS describes modifications to all tables in the database that have been modified since the last time statistics were gathered on the tables. Its columns are the same as those in "ALL_TAB_MODIFICATIONS".

This view is populated only for tables with the MONITORING attribute. It is intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_STATS PL/SQL package to populate this view with the latest information. The ANALYZE_ANY system privilege is required to run this procedure.

DBA_TAB_MODIFICATIONS
ALL_TAB_MODIFICATIONS
USER_TAB_MODIFICATIONS


INSERTS          --插入操作次數
UPDATES          --更新操作次數
DELETES          --刪除操作次數
TRUNCATED	   --自從上次分析之后是否被TRUNCATED過。

SET LINESIZE 1080 PAGESIZE 60;
COL TABLE_OWNER FOR A12;
COL IS_PARTITION FOR A3;
COL NAME FOR A16;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SELECT 	M.TABLE_OWNER,
		     'NO' AS IS_PARTITION,
		     M.TABLE_NAME AS NAME,		
		     M.INSERTS,
		     M.UPDATES,
		     M.DELETES,
		     M.TRUNCATED,
		     M.TIMESTAMP AS LAST_MODIFIED,		
		     ROUND((M.INSERTS+M.UPDATES+M.DELETES)*100/NULLIF(T.NUM_ROWS,0),2) AS EST_PCT_MODIFIED,
		     T.NUM_ROWS AS LAST_KNOWN_ROWS_NUMBER,
		     T.LAST_ANALYZED
FROM 	DBA_TAB_MODIFICATIONS M,
		DBA_TABLES T		
WHERE 	M.TABLE_OWNER=T.OWNER
AND		M.TABLE_NAME=T.TABLE_NAME
AND 	M.TABLE_OWNER NOT IN ('SYS','SYSTEM')
AND 	((M.INSERTS+M.UPDATES+M.DELETES)*100/NULLIF(T.NUM_ROWS,0) > 10 OR T.LAST_ANALYZED IS NULL)
UNION
SELECT 	M.TABLE_OWNER,
		    'YES' AS IS_PARTITION,		
		    M.PARTITION_NAME AS NAME,		
		    M.INSERTS,
		    M.UPDATES,
		    M.DELETES,
		    M.TRUNCATED,
		    M.TIMESTAMP AS LAST_MODIFIED,		
		    ROUND((M.INSERTS+M.UPDATES+M.DELETES)*100/NULLIF(P.NUM_ROWS,0),2) AS EST_PCT_MODIFIED,
		    P.NUM_ROWS AS LAST_KNOWN_ROWS_NUMBER,
		    P.LAST_ANALYZED		
FROM 	DBA_TAB_MODIFICATIONS M,		
		DBA_TAB_PARTITIONS P
WHERE 	M.TABLE_OWNER=P.TABLE_OWNER
AND		M.TABLE_NAME=P.TABLE_NAME
AND 	M.PARTITION_NAME = P.PARTITION_NAME
AND 	M.TABLE_OWNER NOT IN ('SYS','SYSTEM')
AND 	((M.INSERTS+M.UPDATES+M.DELETES)*100/NULLIF(P.NUM_ROWS,0) > 10 OR P.LAST_ANALYZED IS NULL)
ORDER BY 8 DESC;

當然你還可以通過其它方式手工驗證統計信息是否過舊，例如檢查表的行記錄信息

檢查表的行記錄情況

SQL> SELECT OWNER, TABLE_NAME, OBJECT_TYPE, NUM_ROWS      
  2  FROM DBA_TAB_STATISTICS
  3  WHERE OWNER='SCOTT' AND TABLE_NAME='EMP';

OWNER                          TABLE_NAME                     OBJECT_TYPE    NUM_ROWS
------------------------------ ------------------------------ ------------ ----------
SCOTT                          EMP                            TABLE                14

SQL>  SELECT COUNT(*) FROM SCOTT.EMP;

  COUNT(*)
----------
        14

檢查DBA_TAB_COL_STATISTICS中表的列的NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,NUM_NULLS值，然后將其與實際值對比，從而判斷統計信息
是否過期，不過這種方法雖然最準確，但是比較繁瑣耗時，而且有些表的統計信息的采樣比例可能不是100%，所以實際操作實施起來也比較麻煩。

SELECT COUNT(DISTINCT COLUMN_NAME), MIN(COLUMN_NAME), MAX(COLUMN_NAME) FROM OWNER_NAME.TABLE_NAME;

 

SELECT * FROM DBA_TAB_COL_STATISTICS WHERE OWNER='&OWNER' AND TABLE_NAME='&TABLE_NAME';