Oracle數(shù)據(jù)庫(kù)里提供了自動(dòng)維護(hù)任務(wù)，以保證Oracle數(shù)據(jù)庫(kù)可以更高效的運(yùn)行。在不同的Oracle版本中，自動(dòng)任務(wù)的種類和執(zhí)行方式也有區(qū)別。本文以10g和11g為主來認(rèn)識(shí)Oracle里的自動(dòng)任務(wù)。
創(chuàng)新互聯(lián)主營(yíng)若羌網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都APP應(yīng)用開發(fā),若羌h5微信小程序開發(fā)搭建,若羌網(wǎng)站營(yíng)銷推廣歡迎若羌等地區(qū)企業(yè)咨詢
1、種類
10g里的自動(dòng)任務(wù)有兩種：
AUTO_SPACE_ADVISOR_JOB，自動(dòng)空間顧問JOB
GATHER_STATS_JOB，自動(dòng)收集統(tǒng)計(jì)信息JOB
11g里的自動(dòng)任務(wù)有三種：
auto optimizer stats collection，自動(dòng)統(tǒng)計(jì)信息收集顧問
auto space advisor，自動(dòng)空間顧問
sql tuning advisor，sql調(diào)優(yōu)顧問
2、實(shí)現(xiàn)方式
10g里的自動(dòng)任務(wù)是以SCHEDULER的JOB形式運(yùn)行的，可以在dba_scheduler_jobs視圖里查到相關(guān)的JOB。
SQL> set linesize 200 SQL> col owner for a30 SQL> col job_name for a30 SQL> select owner,job_name from dba_scheduler_jobs where job_class='AUTO_TASKS_JOB_CLASS'; OWNER JOB_NAME ------------------------------ ------------------------------ SYS AUTO_SPACE_ADVISOR_JOB SYS GATHER_STATS_JOB
11g里的自動(dòng)任務(wù)使用的是11g的新特性AUTOTASK的方式實(shí)現(xiàn)的?？梢詮膁ba_autotask_client視圖中查到相關(guān)信息。
sys@TEST>SELECT client_name,status FROM dba_autotask_client; CLIENT_NAME STATUS ---------------------------------------- ------------------------ auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED
3、維護(hù)窗口時(shí)間
兩個(gè)版本中自動(dòng)任務(wù)的執(zhí)行時(shí)間是不同的。
10g中分為兩個(gè)時(shí)間段WEEKNIGHT_WINDOW和WEEKEND_WINDOW，可以從dba_scheduler_windows視圖中查到。它們分別的運(yùn)行開始時(shí)間和持續(xù)時(shí)間為：
WEEKNIGHT_WINDOW，每周一至周五晚22:00:00開始，持續(xù)8小時(shí)
WEEKEND_WINDOW，第周六00:00:00，持續(xù)2天
11g中維護(hù)窗口分得更細(xì)，分為7個(gè)，即第一天個(gè)維護(hù)窗口，可以針對(duì)不同的需求做出更細(xì)粒度的調(diào)整，dba_scheduler_windows視圖中查到，分別為MONDAY_WINDOW,TUESDAY_WINDOW,WEDNESDAY_WINDOW,THURSDAY_WINDOW,FRIDAY_WINDOW,SATURDAY_WINDOW,SUNDAY_WINDOW。開始時(shí)間和持續(xù)時(shí)間分別為：
周一到周五晚22:00:00開始，持續(xù)4小時(shí)，周六和周日早6:00:00開始，執(zhí)行20小時(shí)。
4、維護(hù)方式
由于10g和11g中實(shí)現(xiàn)方式的不同，維護(hù)方式自然就不一樣了。
10g的維護(hù)方式與普通的SCHEDULER的JOB一樣。
重慶分公司
重慶分公司
--停用JOB
SQL> exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');
PL/SQL procedure successfully completed.
SQL> select enabled from dba_scheduler_jobs where job_name='AUTO_SPACE_ADVISOR_JOB';
ENABLED
---------------
FALSE
--啟用JOB
SQL> exec dbms_scheduler.ENABLE('AUTO_SPACE_ADVISOR_JOB');
PL/SQL procedure successfully completed.
SQL> select enabled from dba_scheduler_jobs where job_name='AUTO_SPACE_ADVISOR_JOB';
ENABLED
---------------
TRUE下面重點(diǎn)介紹11g里自動(dòng)任務(wù)的維護(hù)。
1)停用和啟用自動(dòng)維護(hù)任務(wù)
--停用sql tuning advisor sys@TEST>BEGIN 2 dbms_auto_task_admin.disable( 3 client_name => 'sql tuning advisor', 4 operation => NULL, 5 window_name => NULL); 6 END; 7 / PL/SQL procedure successfully completed. sys@TEST>select client_name,status from dba_autotask_client; CLIENT_NAME STATUS ---------------------------------------- ------------------------ auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor DISABLED --啟用sql tuning advisor sys@TEST>BEGIN 2 dbms_auto_task_admin.enable( 3 client_name => 'sql tuning advisor', 4 operation => NULL, 5 window_name => NULL); 6 END; 7 / PL/SQL procedure successfully completed. sys@TEST>select client_name,status from dba_autotask_client; CLIENT_NAME STATUS ---------------------------------------- ------------------------ auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED --停用所有維護(hù)窗口下的所有的自動(dòng)任務(wù) sys@TEST>exec dbms_auto_task_admin.disable; PL/SQL procedure successfully completed. sys@TEST>select window_name,autotask_status from dba_autotask_window_clients; WINDOW_NAME AUTOTASK_STATUS ------------------------------ ------------------------ MONDAY_WINDOW DISABLED TUESDAY_WINDOW DISABLED WEDNESDAY_WINDOW DISABLED THURSDAY_WINDOW DISABLED FRIDAY_WINDOW DISABLED SATURDAY_WINDOW DISABLED SUNDAY_WINDOW DISABLED 7 rows selected. --啟用所有維護(hù)窗口下的所有自動(dòng)任務(wù) sys@TEST>exec dbms_auto_task_admin.enable; PL/SQL procedure successfully completed. sys@TEST>select window_name,autotask_status from dba_autotask_window_clients; WINDOW_NAME AUTOTASK_STATUS ------------------------------ ------------------------ WEDNESDAY_WINDOW ENABLED FRIDAY_WINDOW ENABLED SATURDAY_WINDOW ENABLED THURSDAY_WINDOW ENABLED TUESDAY_WINDOW ENABLED SUNDAY_WINDOW ENABLED MONDAY_WINDOW ENABLED 7 rows selected. --停用針對(duì)某個(gè)維護(hù)窗口的自動(dòng)任務(wù) sys@TEST>BEGIN 2 dbms_auto_task_admin.disable( 3 client_name => 'sql tuning advisor', 4 operation => NULL, 5 window_name => 'MONDAY_WINDOW'); 6 END; 7 / PL/SQL procedure successfully completed. sys@TEST>select window_name,autotask_status,sql_tune_advisor from dba_autotask_window_clients; WINDOW_NAME AUTOTASK_STATUS SQL_TUNE_ADVISOR ------------------------------ ------------------------ ------------------------ MONDAY_WINDOW ENABLED DISABLED TUESDAY_WINDOW ENABLED ENABLED WEDNESDAY_WINDOW ENABLED ENABLED THURSDAY_WINDOW ENABLED ENABLED FRIDAY_WINDOW ENABLED ENABLED SATURDAY_WINDOW ENABLED ENABLED SUNDAY_WINDOW ENABLED ENABLED 7 rows selected.
2、配置維護(hù)窗口
這個(gè)與10g的類似，都是使用dbms_scheduler包來操作
--修改維護(hù)窗口的屬性，把SATURDAY_WINDOW的持續(xù)時(shí)間修改為4小時(shí) sys@TEST>BEGIN 2 dbms_scheduler.disable( 3 name => 'SATURDAY_WINDOW'); 4 dbms_scheduler.set_attribute( 5 name => 'SATURDAY_WINDOW', 6 attribute => 'DURATION', 7 value => numtodsinterval(4, 'hour')); 8 dbms_scheduler.enable( 9 name => 'SATURDAY_WINDOW'); 10 END; 11 / PL/SQL procedure successfully completed. sys@TEST>SELECT duration FROM dba_scheduler_windows WHERE window_name='SATURDAY_WINDOW'; DURATION --------------------------------------------------------------------------- +000 04:00:00
11g自動(dòng)維護(hù)任務(wù)參考官方文檔：http://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN12344
