（一）統計資訊收集概述
在Oracle 11g中，預設有3個自動任務，分別是：自動統計資訊收集、SQL調優顧問、段空間調整顧問，檢視方法如下：
SQL> SELECT CLIENT_NAME,TASK_NAME,OPERATION_NAME,STATUS FROM dba_autotask_task; CLIENT_NAME TASK_NAME OPERATION_NAME STATUS -------------------------------- -------------------------- -------------------------- -------- sql tuning advisor AUTO_SQL_TUNING_PROG automatic sql tuning task ENABLED auto optimizer stats collection gather_stats_prog auto optimizer stats job ENABLED auto space advisor auto_space_advisor_prog auto space advisor job ENABLED
灰色背景行代表自動統計資訊收集，使用的任務為gather_stats_prog。gather_stats_prog呼叫了DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC儲存過程
SQL> SELECT PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION FROM dba_scheduler_programs WHERE PROGRAM_NAME = 'GATHER_STATS_PROG'; PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION ------------------------------ ---------------- -------------------------------------------------------------------------------- GATHER_STATS_PROG STORED_PROCEDURE dbms_stats.gather_database_stats_job_proc
在Oracle 11g中，一共配置了7個自動維護視窗，每天一個視窗
SQL> SELECT WINDOW_NAME,AUTOTASK_STATUS FROM dba_autotask_window_clients ; WINDOW_NAME AUTOTASK_STATUS ------------------------------ --------------- MONDAY_WINDOW ENABLED TUESDAY_WINDOW ENABLED WEDNESDAY_WINDOW ENABLED THURSDAY_WINDOW ENABLED FRIDAY_WINDOW ENABLED SATURDAY_WINDOW ENABLED SUNDAY_WINDOW ENABLED
每個視窗的執行時間如下：
SQL> SELECT a.WINDOW_NAME,a.REPEAT_INTERVAL,a.duration FROM dba_scheduler_windows a WHERE ENABLED = 'TRUE'; WINDOW_NAME REPEAT_INTERVAL DURATION ------------------ ------------------------------------------------------- ----------------- MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
可以看到，從週一到週五，視窗執行時間為晚上22點開始，最多執行4個小時，週六週日從早上6點開始，最多執行20個小時。
在視窗任務啟動時，自動任務GATHER_STATS_PROG每次執行時會先生成ORA$AT_OS_OPT_xxx的作業，然後再執行這個作業。
SQL> SELECT a.JOB_NAME,a.ACTUAL_START_DATE,a.RUN_DURATION,a.STATUS 2 FROM dba_scheduler_job_run_details a 3 WHERE a.JOB_NAME LIKE 'ORA$AT_OS_OPT%'; JOB_NAME ACTUAL_START_DATE RUN_DURATION STATUS --------------------- ---------------------------------- ---------------- ------------ ORA$AT_OS_OPT_SY_1 25-MAY-20 10.00.02.042065 PM PRC +000 00:01:24 SUCCEEDED ORA$AT_OS_OPT_SY_21 30-MAY-20 09.25.57.005710 AM PRC +000 00:00:37 SUCCEEDED ORA$AT_OS_OPT_SY_41 30-MAY-20 01.26.30.842460 PM PRC +000 00:00:43 SUCCEEDED ORA$AT_OS_OPT_SY_61 30-MAY-20 05.26.41.292037 PM PRC +000 00:00:31 SUCCEEDED
總結：Oracle 11g自動統計資訊收集是通過每天執行自動任務gather_stats_prog來實現的，它每天會自動生成ORA$AT_OS_OPT_xxx的作業，然後執行作業來收集統計資訊，其本質也是執行了DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC儲存過程。
（二）統計資訊收集策略
每次自動收集統計資訊，並不是對所有表都進行收集，Oracle只對那些已經統計資訊失效的物件進行收集，那麼Oracle如何判斷哪些物件的統計資訊失效了呢？
在Oracle 11g中，如果引數STATISTICS_LEVEL的值為TYPICAL(預設)或者ALL，則DBA_TAB_MODIFICATIONS會記錄自上次自動統計資訊收集完成之後對目標表的insert、update、delete的操作影響行數，並且還會記錄自從上次自動收集統計資訊之後是否發生過truncate。需要注意的是DBA_TAB_MODIFICATIONS並不會實時更新，如果需要檢視最新資訊，可以手動更新該表的資訊：
EXEC dbms_stats.flush_database_monitoring_info();
Oracle收集失效的統計資訊的策略：自上次自動統計資訊收集作業完成之後，如果DBA_TAB_MODIFICATIONS中記錄的INSERT+UPDATE+DELETE所影響的行記錄之和超過了DBA_TABLES中目標表記錄數的10%，或者是自上次統計資訊收集完成之後目標表執行過truncate操作，那麼Oracle會認為目標表的統計資訊已經失效，自動統計資訊收集作業就會對目標表重新收集統計資訊。
（三）禁用/啟用自動統計資訊收集
在某些情況下，需要禁用自動統計資訊的收集，可以使用以下3種方法，每種方法禁用範圍不同。
（3.1）使用以下方法可以禁用/啟用自動統計資訊收集
SQL> EXEC dbms_auto_task_admin.disable(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);
確認是否已經關閉：
SELECT WINDOW_NAME,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR FROM DBA_AUTOTASK_WINDOW_CLIENTS
如果要啟用，可以使用如下方法重新開啟自動統計資訊收集：
SQL> EXEC dbms_auto_task_admin.enable(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);
再次查詢，確認已經開啟：
（3.2）使用DBMS_SCHEDULER.DISABLE可以禁用維護視窗，從而禁用統計資訊收集
例子1：禁掉週一的自動維護作業，包括統計資訊收集、段顧問、sql調優顧問
EXEC dbms_scheduler.disable(NAME=> 'SYS.MONDAY_WINDOW',FORCE=> TRUE)
結果如下：
SELECT a.WINDOW_NAME,a.enabled FROM dba_scheduler_windows a where a.window_name = 'MONDAY_WINDOW';
啟用週一的自動維護作業，包括統計資訊收集、段顧問、sql調優顧問
EXEC dbms_scheduler.enable(NAME=>'SYS.MONDAY_WINDOW');
（3.3）使用DBMS_SCHEDULER.DISABLE可以禁用維護視窗中的統計資訊收集
例子2：禁掉週二的自動統計資訊收集，段顧問、sql調優顧問保持開啟
EXEC dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>'TUESDAY_WINDOW');
查詢結果如下：
SELECT WINDOW_NAME,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR FROM DBA_AUTOTASK_WINDOW_CLIENTS ;
再次開啟：
EXEC dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>'TUESDAY_WINDOW');
（四）調整自動統計資訊收集
預設的統計資訊如下，從週一到週五，視窗執行時間為晚上22點開始，最多執行4個小時，週六週日從早上6點開始，最多執行20個小時。
我們可以對其進行修改，修改的方法如下：
1.先禁用視窗：DBMS_SCHEDULER.DISABLE()
2.修改視窗的屬性：DBMS_SCHEDULER.SET_ATTRIBUTE()
3.啟用視窗：DBMS_SCHEDULER.ENABLE()
例子1：將週二的起始執行時間調整到23點
-- 1.禁用視窗 EXEC dbms_scheduler.disable(NAME=> 'SYS.MONDAY_WINDOW',FORCE=> TRUE) -- 2.修改啟動時間為23點 EXEC dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW',attribute => 'REPEAT_INTERVAL',value => 'freq=daily;byday=TUE;byhour=23;byminute=0; bysecond=0'); -- 3.啟用視窗 EXEC dbms_scheduler.enable(NAME=>'SYS.MONDAY_WINDOW');
檢視結果：
（五）列的直方圖統計資訊收集方式修改
在Oracle 11g中，Oracle預設直方圖的統計資訊收集方式是AUTO，即Oracle會根據負載以及列的使用情況來確定對哪些列收集直方圖資訊，為了更好地利用直方圖統計資訊的同時保持執行計劃的穩定，推薦對直方圖統計資訊的收集策略是對已經存在直方圖的列才收集直方圖統計資訊，即以REPEAT方式收集。
檢視預設的直方圖收集策略：
SQL> SELECT dbms_stats.get_prefs('METHOD_OPT') FROM dual; DBMS_STATS.GET_PREFS('METHOD_O -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO
修改直方圖策略：
SQL> EXEC dbms_stats.set_global_prefs(pname => 'METHOD_OPT',pvalue => 'FOR ALL COLUMNS SIZE REPEAT'); PL/SQL procedure successfully completed
檢視修改後的預設的直方圖收集策略：
SQL> SELECT dbms_stats.get_prefs('METHOD_OPT') FROM dual; DBMS_STATS.GET_PREFS('METHOD_O -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE REPEAT
（六）統計資訊閾值修改
在Oracle 11g中，預設統計資訊的收集閾值為10%，即10%的行資料發生變化或者執行了truncate，才會再次收集統計資訊。我們可以使用下面的方法針對單個表修改閾值。
例子1：修改test01表的統計資訊收集閾值為5%。
檢視初始的閾值：
SQL> SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'LIJIAMAN',tabname => 'TEST01') FROM dual; DBMS_STATS.GET_PREFS(PNAME=>'S -------------------------------------------------------------------------------- 10
修改閾值為5：
SQL> EXEC dbms_stats.set_table_prefs(ownname => 'LIJIAMAN',tabname => 'TEST01',pname => 'STALE_PERCENT',pvalue => 5); PL/SQL procedure successfully completed
確認修改後的閾值：
SQL> SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'LIJIAMAN',tabname => 'TEST01') FROM dual; DBMS_STATS.GET_PREFS(PNAME=>'S -------------------------------------------------------------------------------- 5
需要注意的是：當閾值為0時，不管資料如何變化，每天都會自動收集統計資訊。
【完】