Oracle 11g無法自動收集統計資訊
從Oracle 11G開始，資料庫統計資訊的自動收集被整合到自動維護任務中，滿足大多數情形下的執行需求。但是今天看到一篇博文：
。博文的主要內容是無法自動收集統計資訊，並且給出了一些解決思路。
SQL> select client_name,task_name,operation_name,status from dba_autotask_task; -- 查詢dba_autotask_task檢視根本沒有結果返回。
no rows selected
SQL> select job_name,actual_start_date,status from (select * from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT%' order by log_date desc ) where rownum <4; --最近一次執行成功的自動收集統計資訊的時間如下
JOB_NAME ACTUAL_START_DATE STATUS
---------------------------- ---------------------------------------- -------------
ORA$AT_OS_OPT_SY_2606 26-SEP-12 10.00.07.902100 PM PRC SUCCEEDED
ORA$AT_OS_OPT_SY_2586 25-SEP-12 10.00.07.829792 PM PRC SUCCEEDED
ORA$AT_OS_OPT_SY_2566 24-SEP-12 10.00.07.154019 PM PRC SUCCEEDED
SQL> select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME ; --各個視窗的狀態也都一切正常。
WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE
------------------------------ ------------------------------------- ----- --------
TUESDAY_WINDOW 30-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED
WEDNESDAY_WINDOW 31-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED
THURSDAY_WINDOW 01-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED
FRIDAY_WINDOW 02-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED
SATURDAY_WINDOW 03-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED
SUNDAY_WINDOW 04-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED
MONDAY_WINDOW 05-FEB-18 03.00.00.000000 PM PRC FALSE ENABLED
SQL> select client_name,status from dba_autotask_client; --奇怪的是查詢這個檢視，確實是自動開啟收集統計資訊的設定。
CLIENT_NAME STATUS
--------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
SQL> select window_name,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS；--自動呼叫視窗也是正常的。
WINDOW_NAME AUTOTASK
------------------------------ --------
MONDAY_WINDOW ENABLED
TUESDAY_WINDOW ENABLED
WEDNESDAY_WINDOW ENABLED
THURSDAY_WINDOW ENABLED
FRIDAY_WINDOW ENABLED
SATURDAY_WINDOW ENABLED
SUNDAY_WINDOW ENABLED
至此，問題較為清晰。11g中我的自動收集統計資訊的相關設定都正常，但為什麼不會自動收集統計資訊了呢？也就是說為什麼查詢dba_autotask_task這個檢視沒有task在執行呢？
最後，透過各方求助終於解決了問題。原因如下：
SQL> select window_name,active from DBA_SCHEDULER_WINDOWS;
WINDOW_NAME ACTIV
------------------------------ -----
MONDAY_WINDOW FALSE
TUESDAY_WINDOW FALSE
WEDNESDAY_WINDOW FALSE
THURSDAY_WINDOW TRUE
FRIDAY_WINDOW FALSE
SATURDAY_WINDOW FALSE
SUNDAY_WINDOW FALSE
WEEKNIGHT_WINDOW FALSE
WEEKEND_WINDOW FALSE
正常來說所有的視窗都應該是 false的狀態。但是 我們現有有一個是true，或許有可能是因為這個原因。這個視窗是週四的視窗，然後找一下最近一次自動收集統計資訊的時間。
SQL> select job_name,actual_start_date,status from (select * from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT%' order by log_date desc ) where rownum <4;
JOB_NAME ACTUAL_START_DATE STATUS
---------------------------- ---------------------------------------- -------------
ORA$AT_OS_OPT_SY_2606 26-SEP-12 10.00.07.902100 PM PRC SUCCEEDED
ORA$AT_OS_OPT_SY_2586 25-SEP-12 10.00.07.829792 PM PRC SUCCEEDED
ORA$AT_OS_OPT_SY_2566 24-SEP-12 10.00.07.154019 PM PRC SUCCEEDED
發現是12年9月26號。查詢了一下萬年曆。26號正好是週三。而週四變成了true，可能是因為在收集統計資訊的時候資料庫不正常關閉導致的吧！現在將這個true改變為false。
這裡注意，如果你的生產環境很久很久沒有自動收集統計資訊了的狀態下，請先在測試環境收集統計資訊最好做個spa報告，不然很有可能影響生產。
SQL> EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW ('THURSDAY_WINDOW');
再次查詢
SQL> select window_name,active from DBA_SCHEDULER_WINDOWS;
WINDOW_NAME ACTIV
------------------------------ -----
MONDAY_WINDOW FALSE
TUESDAY_WINDOW FALSE
WEDNESDAY_WINDOW FALSE
THURSDAY_WINDOW FALSE
FRIDAY_WINDOW FALSE
SATURDAY_WINDOW FALSE
SUNDAY_WINDOW FALSE
WEEKNIGHT_WINDOW FALSE
WEEKEND_WINDOW FALSE
都變為flase了。
今天是週一。現在將週一的收集統計資訊的時間變更一下。
SQL> select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME ;
WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE
------------------------------ -------------------------------------- ----- --------
MONDAY_WINDOW 29-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED
TUESDAY_WINDOW 30-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED
WEDNESDAY_WINDOW 31-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED
THURSDAY_WINDOW 01-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED
FRIDAY_WINDOW 02-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED
SATURDAY_WINDOW 03-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED
SUNDAY_WINDOW 04-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED
7 rows selected.
修改自動收集時間視窗。
SQL> begin
2 dbms_scheduler.disable( name => '"SYS"."MONDAY_WINDOW"', force => true);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_scheduler.set_attribute( name=> '"SYS"."MONDAY_WINDOW"',attribute => 'repeat_interval',value=>'freq=daily;byday=mon;byhour=15;byminute=0;bysecond=0');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_scheduler.enable( name => '"SYS"."MONDAY_WINDOW"');
3 end;
4 /
PL/SQL procedure successfully completed.
修改成功
SQL> select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME ;
WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE
------------------------------ --------------------------------------- ----- --------
MONDAY_WINDOW 29-JAN-18 03.00.00.000000 PM PRC FALSE ENABLED
TUESDAY_WINDOW 30-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED
WEDNESDAY_WINDOW 31-JAN-18 10.00.00.000000 PM PRC FALSE ENABLED
THURSDAY_WINDOW 01-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED
FRIDAY_WINDOW 02-FEB-18 10.00.00.000000 PM PRC FALSE ENABLED
SATURDAY_WINDOW 03-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED
SUNDAY_WINDOW 04-FEB-18 06.00.00.000000 AM PRC FALSE ENABLED
7 rows selected.
跑完之後再次查詢。
SQL> select job_name,actual_start_date,status from (select * from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT%' order by log_date desc ) where rownum <4;
JOB_NAME ACTUAL_START_DATE STATUS
---------------------------- ------------------------------------------ --------------
ORA$AT_OS_OPT_SY_2626 29-JAN-18 03.00.00.732062 PM PRC STOPPED
ORA$AT_OS_OPT_SY_2606 26-SEP-12 10.00.07.902100 PM PRC SUCCEEDED
ORA$AT_OS_OPT_SY_2586 25-SEP-12 10.00.07.829792 PM PRC SUCCEEDED
果然，是因為這個視窗的問題。這個DBA_SCHEDULER_WINDOWS 正常的狀態應該是false的狀態，當系統呼叫時變為true的狀態，現在因為資料庫不正常關閉，導致了這個視窗並沒有改變過來。所以資料庫所有的收集作業都斷了。
再次查詢 ：
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 space advisor auto_space_advisor_prog auto space advisor job ENABLED
auto optimizer stats collection gather_stats_prog auto optimizer stats job ENABLED
透過在網上查詢相關資料【http://blog.itpub.net/235507/viewspace-1137629/】，寫到Oracle 11g統計資訊自動收集任務執行任務呼叫的流程：
1、首先是dba_autotask_task-->dba_autotask_client建立自動執行任務
2、再根據時間視窗及資源組建立自動執行作業
dba_autotask_client-->dba_scheduler_window_groups-->dba_scheduler_windows-->dba_scheduler_jobs
dba_autotask_client-->dba_scheduler_job_classes
相關的檢視：
dba_autotask_task
dba_autotask_client
dba_autotask_client_job
dba_autotask_window_clients
dba_autotask_client_history
dba_scheduler_jobs
dba_scheduler_job_classes
dba_scheduler_window_groups
dba_scheduler_windows
dba_scheduler_wingroup_members
關於詳細的Oracle 11g統計資訊的收集與調整請參看博文：Oracle
11G統計資訊自動收集及調整【http://blog.csdn.net/leshami/article/details/65935325】
作者：SEian.G（苦練七十二變，笑對八十一難）
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31015730/viewspace-2150708/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Oracle】11g Oracle自動收集統計資訊Oracle
- ORACLE 11g 自動收集統計資訊Oracle
- Oracle 11g 之自動收集統計資訊Oracle
- oracle 11g自動收集統計資訊介紹Oracle
- Oracle統計資訊自動收集Oracle
- [統計資訊系列7] Oracle 11g的自動統計資訊收集Oracle
- 關於oracle自動收集統計資訊Oracle
- oracle 11g統計資訊收集Oracle
- Oracle 11g 修改表級別的自動收集統計資訊比率Oracle
- Oracle 11g系統自動收集統計資訊的一些知識Oracle
- Oracle11g 統計資訊-----統計資訊自動收集任務Oracle
- Oracle11g 統計資訊——統計資訊自動收集任務Oracle
- Oracle10g 統計資訊的自動收集Oracle
- ORACLE10g自動收集CBO統計資訊Oracle
- Oracle 11g手工收集表統計資訊Oracle
- Oracle 11g 統計資訊收集指令碼Oracle指令碼
- Oracle的自動統計資訊不收集直方圖的資訊Oracle直方圖
- oracle 統計資訊過期判斷和自動收集Oracle
- [zt] Oracle10g 自動收集收集CBO統計資訊設定Oracle
- ORACLE 11G 自動收集Oracle
- 開啟oracle10g統計資訊自動收集功能Oracle
- Oracle收集統計資訊Oracle
- Oracle 統計資訊收集Oracle
- 收集oracle統計資訊Oracle
- ORACLE 收集統計資訊Oracle
- 雞肋 -- ORACLE10g自動收集CBO統計資訊(ZT)Oracle
- oracle10g如何啟用禁用自動統計資訊收集Oracle
- Oracle 11g新特性之收集多列統計資訊Oracle
- 關閉特定物件統計資訊自動收集物件
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- 關閉及開啟oracle10g統計資訊自動收集功能Oracle
- 禁用11g的統計資料自動收集功能
- oracle收集統計資訊job停止Oracle
- 關閉及開啟oracle10g統計資訊自動收集功能（轉）Oracle
- 關於ORACLE自動統計CBO統計資訊Oracle
- 【Statistics】禁用11g的統計資料自動收集功能
- Oracle 判斷 並 手動收集 統計資訊 指令碼Oracle指令碼
- oracle 統計資訊檢視與收集Oracle