In 11g, statistics are automatically collected at PM (Monday to Friday, 4 hours) and am (Saturday, Sunday, 20 hours) by default ), as follows:
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:00FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
Since many systems are still in the peak business hours at, it is necessary to adjust the time. This should be determined based on their own business. In our system, the adjustment is as follows:
From Monday to Friday, it starts at a.m. and lasts for 5 hours. It starts at a.m. on Saturday and Sunday and lasts for 10 hours.
Run the following statement with the sys User:
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;/
The result after the preceding statement is successfully executed is as follows:
WINDOW_NAME REPEAT_INTERVAL DURATION------------------------------ ------------------------------------------------------------ --------------------WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0 +000 05:00:00FRIDAY_WINDOW freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0 +000 05:00:00SATURDAY_WINDOW freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0 +000 10:00:00THURSDAY_WINDOW freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0 +000 05:00:00TUESDAY_WINDOW freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0 +000 05:00:00SUNDAY_WINDOW freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0 +000 10:00:00MONDAY_WINDOW freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0 +000 05:00:00
After the time adjustment is successful, the next step is to enable the 11g automatic collection statistics job. First, confirm that the current is not enabled:
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
Run the following statement to enable it:
BEGIN dbms_auto_task_admin.enable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);END;/
Make sure that it is enabled:
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
Appendix: Statement for closing the job:
BEGIN dbms_auto_task_admin.disable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; /