Wednesday, May 18, 2016

How do I change the start time and duration for Automatic Optimizer Statistics Collection

Last time we saw how long the Automatic Optimizer Statistics Collection job was running.
Now let's change it to match our environment better.

The normal job starts at 10pm and ends at 2am. A lot of shops will have reports starting at midnight. So this is not the most idle times to run statistics.

In our environment we have identified a period from 6pm to midnight as the best period to run stats. Bonus: We have gained an extra 2 hours for it.

The default window to run Automatic Optimizer Statistics Collection is from 10pm until 2am during week days and 20 hours starting at 6am Saturdays and Sundays.

If the task appears to take the whole default 4 hours, and its status is marked "STOPPED", then it may not have enough time to gather all necessary statistics.


--This will set the time at 6pm (18:00) with "byhour=18"
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',   'repeat_interval','freq=daily;byday=MON;byhour=18;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',  'repeat_interval','freq=daily;byday=TUE;byhour=18;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=18;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW', 'repeat_interval','freq=daily;byday=THU;byhour=18;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',   'repeat_interval','freq=daily;byday=FRI;byhour=18;byminute=0; bysecond=0');
--This will change the duration of the job to 6 hours long.
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',   'duration',interval '6' hour);
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',  'duration',interval '6' hour);
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','duration',interval '6' hour);
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW', 'duration',interval '6' hour);
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',   'duration',interval '6' hour);

No comments :

Post a Comment