3.11 自動維護作業

從 Oracle 11g 開始，資料庫會安排自動維護作業 (Automatic Maintenance Plan) 來執行每天所需的例行性事務，自動維護作業總共包含三個項目:

收集統計資料 (Automatic Optimizer Statistics Collection) : 用來收集 Table、Index 的統計資訊。

區段建議程式 (Automatic Segment Advisor) : 用來執行 Segment Advisor。

自動 SQL 調整 (Automatic SQL Tuning Advisor) : 用來執行 SQL Tuning Advisor。

這三項作業每天都會依照 Maintenance Window 所排程的時段來執行，預設的排程時段為每周 一 ~ 五 PM 10:00，六日為 AM 6:00。

自動維護作業的管理可以藉由 Database Control 來進行，一般來說，這三項作業除了收集統計資料是必須要執行之外，其餘兩項對資料庫來說不是那麼需要每日執行。透過 Database Control 裡面的伺服器頁簽便可以用來管理自動維護作業，可以設定某些項目為 停用/啟用 :

透過 dbms_auto_task_admin 也可以將某個項目 停用/啟用，例如停用 SQL Tuning Advisor 的自動維護作業:

BEGIN  dbms_auto_task_admin.disable(client_name => 'sql tuning advisor',operation=> NULL, window_name => NULL); END;

透過 dba_autotask_operation 可以查詢目前自動維護作業狀態:

SQL> select client_name,operation_name,status  from dba_autotask_operation;     CLIENT_NAME            OPERATION_NAME   STATUS ----------------------------------------  ---------------------------------------- ---------------- auto optimizer stats collection      auto optimizer stats job   ENABLED auto space advisor                             auto space advisor job   ENABLED sql tuning advisor                            automatic sql tuning task  DISABLED

對於統計資料的收集，預設並不會將資料庫所有的 Table 都做收集，而是異動量大於 10% 的 Table 才會將統計資訊更新，透過 dbms_stats.get_prefs 可以得知預設異動量設定為 10%:

SQL> select dbms_stats.get_prefs(pname=>'STALE_PERCENT') sp from dual;

使用 dbms_stats.set_global_prefs 可以變更異動量設定，例如將異動量設定為 5%:

SQL> exec dbms_stats.set_global_prefs('STALE_PERCENT','5'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs(pname=>'STALE_PERCENT') sp from dual; SP ------------------ 5

Maintenance Window 所排程的時段也是可以透過 Database Control 進行修改，同樣透過伺服器頁簽裡面的自動維護作業選項進行修改，點選其中一個時段便可以進行修改:

透過 dbms_scheduler 也可以手動修改 Maintenance Window 的時間，步驟上需先將選定的 Maintenance Window Disable，修改完之後再將其 Enable，例如將周二的 Maintenance Window 從 PM 10:00 修改為 PM 11:00:

BEGIN DBMS_SCHEDULER.DISABLE(name=>'TUESDAY_WINDOW', force=>TRUE); END; BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'TUESDAY_WINDOW',attribute=>'REPEAT_INTERVAL', value=>'FREQ=WEEKLY;BYDAY=TUE;BYHOUR=23;BYMINUTE=0;BYSECOND=0'); END; BEGIN DBMS_SCHEDULER.ENABLE(name=>'TUESDAY_WINDOW'); END;

由 dba_autotask_scheduler 可以查詢目前 Maintenance Window 所排程的時段:

SQL> select * from DBA_AUTOTASK_SCHEDULE;      WINDOW_NAME                   START_TIME      DURATION  -----------------------------      -------------------------------------------------------------- --------------  MONDAY_WINDOW 29-MAY-10 10.00.00.400000 PM +08:00 +000       04:00:00  TUESDAY_WINDOW       30-MAY-10 11.00.00.400000 PM +08:00 +000       04:00:00  WEDNESDAY_WINDOW 31-MAY-10 10.00.00.400000 PM +08:00 +000      04:00:00  THURSDAY_WINDOW   01-JUN-10 10.00.00.400000 PM +08:00 +000        04:00:00  FRIDAY_WINDOW         02-JUN-10 10.00.00.400000 PM +08:00 +000         04:00:00  SATURDAY_WINDOW    03-JUN-10 06.00.00.400000 AM +08:00 +000         20:00:00   SUNDAY_WINDOW        04-JUN-10 06.00.00.400000 AM +08:00 +000         20:00:00

自動維護作業這個功能提供了資料庫每日例行性需執行的作業，DBA 可以依據自己管理上的需求將其中的項目 停用/啟用，或是更改 Maintenance Window 的排程時段，增加了 DBA 在管理資料庫上的便利性。