DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC儲存過程

Automatic Statistics Gathering
在Oracle10g中引入的最佳化器統計資訊（Optimizer Statistics）自動收集，是一個看上去很不錯的功能，但是在實際應用中卻往往沒有起到相應的效果，甚至在某些系統中我們會建議禁用這個功能。

闡述一些該功能的相關知識點。

1. Automatic Statistics Gathering是由Scheduler排程GATHER_STATS_JOB作業來完成的，在GATHER_STATS_JOB作業中則呼叫DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC儲存過程。

2. 該作業在建立資料庫的自動建立，並且設定為每天晚上10點到第二天早上6點和週六週日的全天為執行視窗期。在執行視窗期內，該作業都會執行，根據stop_on_window_close屬性來決定，如在視窗期結束以後，該作業如果還沒有執行完畢，是繼續執行還是結束執行。

3. GATHER_DATABASE_STATS_JOB_PROC是內部的儲存過程，基本上跟DBMS_STATS.GATHER_DATABASE_STATS的功能一樣，但是有內部的優先順序考慮，更新越多的表將會越優先收集統計資訊。

4. 收集統計資訊的表物件是，之前從來沒有收集過的或者是更新的（包括insert,update,delete,truncate）記錄數超過當前總記錄數10%的表。記錄數的更改量由Oracle資料庫自動監控，在初始化引數statistics_level設定為TYPICAL或者ALL時，自動監控即會生效。

5. 在USER_TAB_MODIFICATIONS表中記錄了所有被監控的表的資料量更改資訊。該資訊的更新將會稍微滯後於真實的修改，可以透過DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO儲存過程來立刻將更改的資訊更新到USER_TAB_MODIFICATIONS表中。對於更新之後再rollback的記錄，仍然算為已經受影響的記錄，Oracle不會在rollback之後再去更新USER_TAB_MODIFICATIONS表。

SQL> select * from user_tab_modifications where table_name='EMP';
 
no rows selected
 
SQL> select count(*) from emp;
 
  COUNT(*)
----------
        14
 
SQL> update emp set sal=sal+100;
 
14 rows updated.
 
SQL> select * from user_tab_modifications where table_name='EMP';
 
no rows selected
 
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
 
PL/SQL procedure successfully completed.
 
SQL> select inserts,updates,deletes from user_tab_modifications where table_name
='EMP';
 
   INSERTS    UPDATES    DELETES
---------- ---------- ----------
         0         14          0
 
SQL> rollback;
 
Rollback complete.
 
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
 
PL/SQL procedure successfully completed.
 
SQL> select inserts,updates,deletes from user_tab_modifications where table_name
='EMP';
 
   INSERTS    UPDATES    DELETES
---------- ---------- ----------
         0         14          0
 
SQL>
6. 在Oracle10g版本（包括最新的10.2.0.4）中沒有已知的修改10%這個閥值的方法。但是在Oracle11g中則提供了SET_TABLE_PREFS等函式。

以下命令將指定表的STALE預設值從10%改為5%，該值可以從新的dba_tab_stat_prefs資料字典中查詢獲得。

--僅限於Oracle11g版本
BEGIN
  DBMS_STATS.SET_TABLE_PREFS ( wnname =>'KAMUS', tabname =>'T1', pname =>'STALE_PERCENT', pvalue =>'5');
END;
/
 
SQL> select * from dba_tab_stat_prefs;
 
OWNER      TABLE_NAME PREFERENCE_NAME      PREFE
---------- ---------- -------------------- -----
KAMUS      T1         STALE_PERCENT        5
7. 執行以下命令，可以禁用統計資訊自動收集功能。

BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/