程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> [Oracle]11G自動收集統計信息

[Oracle]11G自動收集統計信息

編輯:Oracle教程

在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

由於很多系統晚上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: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;  
/

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved