Oracle教程：DBA任務---確保統計資訊準確性

最近忙得不可開交，項目進入了cut over階段，壓力之大，前所未有。我的工作就是，負責最佳化long running的SQL，讓其可以在3小時以內完成。昨天就出現一個Long running 的SQL，它跑了16小時，經過2小時的奮鬥，終於把它最佳化到了2小時10分鐘。

雖然那個Long running SQL 與統計資訊無關，但是我還是提出要確保統計資訊的準確性。作為DBA，我必須定製出收集統計資訊的策略，以及相關指令碼，下面就是一個關於確保統計資訊準確性的指令碼，拿出來分享一下。

注意：該指令碼適用於資料倉儲，Oracle11g，如果你是OLTP，Oracle10g，請自己修改某些條件。

DECLARE
  CURSOR STALE_TABLE IS
    SELECT OWNER,
           SEGMENT_NAME,
           CASE
             WHEN SIZE_GB < 0.5 THEN
              30
             WHEN SIZE_GB >= 0.5 AND SIZE_GB < 1 THEN
              20
             WHEN SIZE_GB >= 1 AND SIZE_GB < 5 THEN
              10
             WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN
              5
             WHEN SIZE_GB >= 10 THEN
              1
           END AS PERCENT,
           8 AS DEGREE
      FROM (SELECT OWNER,
                   SEGMENT_NAME,
                   SUM(BYTES / 1024 / 1024 / 1024) SIZE_GB
              FROM DBA_SEGMENTS
             WHERE OWNER = 'ADWU_OPTIMA_AP11'
               AND SEGMENT_NAME IN
                   (SELECT /*+ UNNEST */ DISTINCT TABLE_NAME
                      FROM DBA_TAB_STATISTICS
                     WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES')
                       AND OWNER = 'ADWU_OPTIMA_AP11')
             GROUP BY OWNER, SEGMENT_NAME);

BEGIN
  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  FOR STALE IN STALE_TABLE LOOP
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => STALE.OWNER,
                                  TABNAME          => STALE.SEGMENT_NAME,
                                  ESTIMATE_PERCENT => STALE.PERCENT,
                                  METHOD_OPT       => 'for all columns size auto',
                                  DEGREE           => 8,
                                  GRANULARITY      => 'ALL',
                                  CASCADE          => TRUE);
  END LOOP;
END;
/

根據實際情況，可以選擇每天晚上在資料庫不繁忙的時候運行上述指令碼。