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