在11g中,自动收集统计信息的时间为晚上10点(周一到周五),早上6点(周六,周日)

由于有些系统在晚上10点还是高分期。会影响系统的性能,所以简单的方法就是把时间调整为晚上3点;

当然也可以把自动统计信息关闭,自己写个存储过程自定义的去收集统计信息。

把时间调整为晚上3点的方法:

 sql:
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';

 

MONDAY_WINDOW	freq=daily;byday=MON;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
WEDNESDAY_WINDOW	freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0	+000 04:00:00
THURSDAY_WINDOW	freq=daily;byday=THU;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
SUNDAY_WINDOW	freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0	+000 20:00:00

 

用sys执行

 

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

查看一下是否修改成功

select t1.window_name, t1.repeat_interval, t1.duration
  from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
 where t1.window_name = t2.window_name
   and t2.window_group_name in
       ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');




MONDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
TUESDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
WEDNESDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
THURSDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
FRIDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
SATURDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
SUNDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00

 

已修改成功。

 

另附怎么关闭这个scheduler调度:

To disable a specific maintenance task, use the DISABLE procedure, as follows:

SQL> BEGIN
       dbms_auto_task_admin.disable(
       client_name => 'auto optimizer stats collection',
       operation => NULL,
       window_name => NULL);
     END;  
    /


To enable this specific maintenance task again, use the ENABLE procedure, as follows:
 

SQL> BEGIN
       dbms_auto_task_admin.enable(
       client_name => 'auto optimizer stats collection',
       operation => NULL,
       window_name => NULL);
     END;
     /

The task names to use for the client_name argument are listed in the DBA_AUTOTASK_CLIENT database dictionary view.

select client_name,status from DBA_AUTOTASK_CLIENT;