Oracle 11g 修改自动统计信息收集时间

在11g中，自动收集统计信息的时间为晚上10点（周一到周五），早上6点（周六，周日）。
由于某些系统在晚上10点还是高分期，如果在高峰期收集的话会影响系统的性能，所以我们一般会选择业务的低峰期来收集统计信息，这样可以避免性能的损耗； 
当然也可以把自动统计信息关闭，自己写个存储过程自定义的去收集统计信息。 ：


从上图可以看出，在新部署的数据库来说，通过上面的SQL查出的信息都是数据库默认的统计时间。

所以我们通过如下方法对自动收集统计信息的收集时间进行调整。我们将周一至周五的统计收集时间调整到凌晨2点，窗口时间为4个小时，周六和周日的统计收集时间调整到凌晨2点，窗口时间为6小时：

BEGIN
  sys.dbms_scheduler.set_attribute(NAME      => 'SYS.MONDAY_WINDOW',
                                   attribute => 'repeat_interval',
                                   VALUE     => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
  sys.dbms_scheduler.set_attribute(NAME      => 'SYS.MONDAY_WINDOW',
                                   attribute => 'duration',
                                   VALUE     => '0 04:00:00');
END;
/


 BEGIN
   sys.dbms_scheduler.set_attribute(NAME      => 'SYS.TUESDAY_WINDOW',
                                    attribute => 'repeat_interval',
                                    VALUE     => 'FREQ=WEEKLY;BYDAY=TUE;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
   sys.dbms_scheduler.set_attribute(NAME      => 'SYS.TUESDAY_WINDOW',
                                    attribute => 'duration',
                                    VALUE     => '0 04:00:00');
 END;
 /


BEGIN
  sys.dbms_scheduler.set_attribute(NAME      => 'SYS.WEDNESDAY_WINDOW',
                                   attribute => 'repeat_interval',
                                   VALUE     => 'FREQ=WEEKLY;BYDAY=WED;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
  sys.dbms_scheduler.set_attribute(NAME      => 'SYS.WEDNESDAY_WINDOW',
                                   attribute => 'duration',
                                   VALUE     => '0 04:00:00');
END;
/


 BEGIN
   sys.dbms_scheduler.set_attribute(NAME      => 'SYS.THURSDAY_WINDOW',
                                    attribute => 'repeat_interval',
                                    VALUE     => 'FREQ=WEEKLY;BYDAY=THU;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
   sys.dbms_scheduler.set_attribute(NAME      => 'SYS.THURSDAY_WINDOW',
                                    attribute => 'duration',
                                    VALUE     => '0 04:00:00');
 END;
 /


 BEGIN
   sys.dbms_scheduler.set_attribute(NAME      => 'SYS.FRIDAY_WINDOW',
                                    attribute => 'repeat_interval',
                                    VALUE     => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
   sys.dbms_scheduler.set_attribute(NAME      => 'SYS.FRIDAY_WINDOW',
                                    attribute => 'duration',
                                    VALUE     => '0 04:00:00');
 END;
 /


 BEGIN
   sys.dbms_scheduler.set_attribute(NAME      => 'SYS.SATURDAY_WINDOW',
                                    attribute => 'repeat_interval',
                                    VALUE     => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
   sys.dbms_scheduler.set_attribute(NAME      => 'SYS.SATURDAY_WINDOW',
                                    attribute => 'duration',
                                    VALUE     => '0 06:00:00');
 END;
 /


BEGIN
  sys.dbms_scheduler.set_attribute(NAME      => 'SYS.SUNDAY_WINDOW',
                                   attribute => 'repeat_interval',
                                   VALUE     => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
  sys.dbms_scheduler.set_attribute(NAME      => 'SYS.SUNDAY_WINDOW',
                                   attribute => 'duration',
                                   VALUE     => '0 06:00:00');
END;
/

很多业务库不需要数据库来自动收集统计信息，而是自己来写脚本来收集，所以很多时候我们需要关闭这个scheduler调度:

关闭自动统计收集：


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

开启自动统计收集：

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

调整后，我们可以通过dba_autotask_client视图来查看其调度任务状态：

select client_name,status from DBA_AUTOTASK_CLIENT

where client_name='auto optimizer stats collection';