orale 10g和11g中的自動統計任務

xulongxc發表於2014-08-07
1)  先來看下oracle 10g中的自動統計任務的問題。 
從Oracle Database 10g開始,Oracle在建庫後就預設建立了一個名為GATHER_STATS_JOB的定時任務,用於自動收集CBO的統計資訊。 

這個自動任務預設情況下在工作日晚上10:00-6:00和週末全天開啟。呼叫DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統計資訊。該過程首先檢測統計資訊缺失和陳舊的物件。然後確定優先順序,再開始進行統計資訊。 

可以透過以下查詢這個JOB的執行情況: 
select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB' 
其實同在10點執行的Job還有一個AUTO_SPACE_ADVISOR_JOB: 
SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs; 
JOB_NAME                      LAST_START_DATE 
------------------------------ ---------------------------------------- 
AUTO_SPACE_ADVISOR_JOB        04-DEC-07 10.00.00.692269 PM +08:00 
GATHER_STATS_JOB              04-DEC-07 10.00.00.701152 PM +08:00 
FGR$AUTOPURGE_JOB 
PURGE_LOG                      05-DEC-07 03.00.00.169059 AM PRC 

然而這個自動化功能已經影響了很多系統的正常執行,晚上10點對於大部分生產系統也並非空閒時段。 
而自動分析可能導致極為嚴重的閂鎖競爭,進而可能導致資料庫Hang或者Crash。 

所以建議最好關閉這個自動統計資訊收集功能: 
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); 


2)oracle 11g的系統自動job 
   
SQL> select job_name,comments from dba_scheduler_jobs; 


3)10g關閉自動收集job 
select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB' 
所以建議最好關閉自動統計資訊收集功能: 
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); 
啟動自動統計資訊收集功能 
exec DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB'); 

手工收集統計資訊: 
SQL> exec dbms_stats.gather_schema_stats('detail',options=>'gather stale',estimate_percent =>10); 

--收集沒有分析過的表的統計資訊 
begin 
dbms_stats.gather_schema_stats(ownname=>scott,options=>'gather empty'); 
end; 
--重新分析修改量超過10%的表(這些修改包括插入、更新和刪除) 
begin 
dbms_stats.gather_schema_stats(ownname=>scott,options=>'gather stale'); 
end; 

4)11G關係自動收集job 
   對於11g版本的oracle 有三個自動維護定時任務。 
1 自動最佳化器統計收集:為所有方案物件收集陳舊的或缺少的統計資料,所收集的統計資訊將被用來提高sql的執行的效能,任務名是“auto optimizer stats collection” 
2 自動分段顧問:標識資料庫中的段是否有可以回收的空間,並以此資訊統計為基礎做出怎樣整理段的碎片以節約空間。你也可以手動的執行此job來獲取最新的建議資訊,或者獲取自動段advisor 不檢測的但又可以回收的段的資訊,任務名是“auto space advisor” 
3 自動SQL調整顧問:自動標識並嘗試調整高負載的SQL,任務名是“sql tuning advisor” 
sys@RAC> select client_name ,status from DBA_AUTOTASK_CLIENT; 
CLIENT_NAME                                                      STATUS 
---------------------------------------------------------------- -------- 
auto optimizer stats collection                                  ENABLED 
auto space advisor                                               ENABLED 
sql tuning advisor                                               ENABLED 
管理自動維護的job 
在oracle 10g中這些job被分別建立並且以DBA_SCHEDULER_JOBS.JOB_NAME的名稱出現 
然而在11g中則有所改變,透過檢視DBA_AUTOTASK_WINDOW_CLIENTS可以檢視他們一週七天的執行情況,包括 
時間視窗,下次執行時間,job的名稱,健康檢查 
sys@RAC> select * from DBA_AUTOTASK_WINDOW_CLIENTS; 
WINDOW_NAME        WINDOW_NEXT_TIME                   WINDO AUTOTASK OPTIMIZE SEGMENT_ADVISOR SQL_TUNE HEALTH_M 
------------------ ---------------------------------- ----- -------- -------- --------------- -------- -------- 
WEDNESDAY_WINDOW   28-SEP-11 10.00.00.000000 PM PRC   FALSE DISABLED ENABLED  ENABLED         ENABLED  DISABLED 
SATURDAY_WINDOW    01-OCT-11 06.00.00.000000 AM PRC   FALSE DISABLED ENABLED  ENABLED         ENABLED  DISABLED 
THURSDAY_WINDOW    29-SEP-11 10.00.00.000000 PM PRC   FALSE DISABLED ENABLED  ENABLED         ENABLED  DISABLED 
TUESDAY_WINDOW     27-SEP-11 10.00.00.000000 PM PRC   FALSE DISABLED ENABLED  ENABLED         ENABLED  DISABLED 
SUNDAY_WINDOW      02-OCT-11 06.00.00.000000 AM PRC   FALSE DISABLED ENABLED  ENABLED         ENABLED  DISABLED 
MONDAY_WINDOW      03-OCT-11 10.00.00.000000 PM PRC   FALSE DISABLED ENABLED  ENABLED         ENABLED  DISABLED 
FRIDAY_WINDOW      30-SEP-11 10.00.00.000000 PM PRC   FALSE DISABLED ENABLED  ENABLED         ENABLED  DISABLED 
7 rows selected. 

DBMS_AUTO_TASK_ADMIN包的ENABLE和DISABLE儲存過程能夠實現關閉或者開啟的三種job(不要任何引數): 
execute DBMS_AUTO_TASK_ADMIN.DISABLE; 
execute DBMS_AUTO_TASK_ADMIN.ENABLE; 


關閉指定的job 
BEGIN 
  dbms_auto_task_admin.disable( 
  client_name => 'sql tuning advisor', 
  peration => NULL, 
  window_name => NULL); 
END;  

開啟指定的job: 
BEGIN 
  dbms_auto_task_admin.enable( 
  client_name => 'sql tuning advisor', 
  peration => NULL, 
  window_name => NULL); 
END;  

關閉週三sql最佳化器顧問的執行視窗 
sys@RAC> BEGIN 
  2         dbms_auto_task_admin.disable( 
  3         client_name => 'sql tuning advisor', 
  4         peration => NULL, 
  5         window_name => 'MONDAY_WINDOW'); 
  6       END;  
  7      / 
PL/SQL procedure successfully completed. 
sys@RAC> select * from DBA_AUTOTASK_WINDOW_CLIENTS; 

WINDOW_NAME        WINDOW_NEXT_TIME                   WINDO AUTOTASK OPTIMIZE SEGMENT_ADVISOR      SQL_TUNE HEALTH_M 
------------------ ---------------------------------- ----- -------- -------- -------------------- -------- -------- 
WEDNESDAY_WINDOW   28-SEP-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
SATURDAY_WINDOW    01-OCT-11 06.00.00.000000 AM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
THURSDAY_WINDOW    29-SEP-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
TUESDAY_WINDOW     27-SEP-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
SUNDAY_WINDOW      02-OCT-11 06.00.00.000000 AM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
MONDAY_WINDOW      03-OCT-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              DISABLED DISABLED 
FRIDAY_WINDOW      30-SEP-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
7 rows selected. 
關閉星期天的自動段顧問job的時間視窗: 
sys@RAC> BEGIN 
  2         dbms_auto_task_admin.disable( 
  3         client_name => 'auto space advisor', 
  4         peration => NULL, 
  5         window_name => 'SUNDAY_WINDOW'); 
  6       END;  
  7      / 
PL/SQL procedure successfully completed. 

sys@RAC> select * from DBA_AUTOTASK_WINDOW_CLIENTS; 

WINDOW_NAME        WINDOW_NEXT_TIME                   WINDO AUTOTASK OPTIMIZE SEGMENT_ADVISOR      SQL_TUNE HEALTH_M 
------------------ ---------------------------------- ----- -------- -------- -------------------- -------- -------- 
WEDNESDAY_WINDOW   28-SEP-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
SATURDAY_WINDOW    01-OCT-11 06.00.00.000000 AM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
THURSDAY_WINDOW    29-SEP-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
TUESDAY_WINDOW     27-SEP-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 
SUNDAY_WINDOW      02-OCT-11 06.00.00.000000 AM PRC   FALSE ENABLED  ENABLED  DISABLED             ENABLED  DISABLED 
MONDAY_WINDOW      03-OCT-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              DISABLED DISABLED 
FRIDAY_WINDOW      30-SEP-11 10.00.00.000000 PM PRC   FALSE ENABLED  ENABLED  ENABLED              ENABLED  DISABLED 


5) 11G關閉自動job 
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 

begin 
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection', 
operation => NULL, 
window_name => NULL); 
end; 

PL/SQL procedure successfully completed. 

SQL>  select client_name,status from DBA_AUTOTASK_CLIENT; 

CLIENT_NAME                                                      STATUS 
---------------------------------------------------------------- -------- 
auto optimizer stats collection                                  DISABLED 
auto space advisor                                               ENABLED 
sql tuning advisor                                               ENABLED 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29440247/viewspace-1247308/,如需轉載,請註明出處,否則將追究法律責任。

相關文章