在11g中,默認自動收集統計信息的時間為晚上10點(周一到周五,4個小時),早上6點(周六,周日,20個小時),如下所示:
select a.window_name, a.repeat_interval,a.duration from dba_scheduler_windows a, dba_scheduler_wingroup_members b where a.window_name = b.window_name and b.window_group_name = 'MAINTENANCE_WINDOW_GROUP'; WINDOW_NAME REPEAT_INTERVAL DURATION ------------------------------ ------------------------------------------------------------ -------------------- WEDNESDAY_WINDOW freq=daily;byday=WED;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 THURSDAY_WINDOW freq=daily;byday=THU;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 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
周一到周五,凌晨1點開始,持續5個小時; 周六、周日,凌晨1點開始,持續10個小時。
用sys用戶執行如下語句即可:
begin sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 10:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 10:00:00'); end; /上面語句執行成功後的結果如下:
WINDOW_NAME REPEAT_INTERVAL DURATION ------------------------------ ------------------------------------------------------------ -------------------- WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0 +000 05:00:00 FRIDAY_WINDOW freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0 +000 05:00:00 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0 +000 10:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0 +000 05:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0 +000 05:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0 +000 10:00:00 MONDAY_WINDOW freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0 +000 05:00:00時間調整成功後,下一步就是開啟11G的自動收集統計信息job,首先確認當前未開啟:
SYS@PROD> select client_name,status from DBA_AUTOTASK_CLIENT where client_name='auto optimizer stats collection'; CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection DISABLED執行如下語句開啟:
BEGIN dbms_auto_task_admin.enable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; /確認已被開啟:
SYS@PROD> select client_name,status from DBA_AUTOTASK_CLIENT where client_name='auto optimizer stats collection'; CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED附:關閉這個job的語句:
BEGIN dbms_auto_task_admin.disable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; /