[Oracle] 11G自己主動收集統計資訊

標籤：blog   io   2014   art   re   c   cti   div   

在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:00FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00

因為非常多系統晚上10點還是處於業務高峰期，因此有必要調整下時間，這個要依據各自的業務自己推斷，在我們系統調為：

周一到周五，淩晨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:00FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0         +000 05:00:00SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0         +000 10:00:00THURSDAY_WINDOW                freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0         +000 05:00:00TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0         +000 05:00:00SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0         +000 10:00:00MONDAY_WINDOW                  freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0         +000 05:00:00

時間調整成功後，下一步就是開啟11G的自己主動收集統計資訊job，首先確認當前未開啟：

[email protected]> 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;/

確認已被開啟：

[email protected]> 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;  /