11G自动收集统计信息

在11g中，默认自动收集统计信息的时间为晚上10点（周一到周五，4个小时），早上6点（周六，周日，20个小时），如下所示：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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用户执行如下语句即可：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

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 ; /

上面语句执行成功后的结果如下：

?

1 2 3 4 5 6 7 8 9

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，首先确认当前未开启：

?

1 2 3 4 5

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

执行如下语句开启：

?

1 2 3 4 5 6 7

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

确认已被开启：

?

1 2 3 4 5

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的语句：

?

1 2 3 4 5 6 7

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