Changing the Oracle database default maintenance window time
I’ve been working recently on a system that has been a performance nightmare. I’ve been able to do a lot to get it running well, but I often noticed that load on the system would always increase in the afternoon even though it didn’t appear the workload was increasing. I’d notice this often around 3-4PM (Mountain Time) and thought maybe it was because people in the East and Central time zones were returning home and using it. I had observed however, that the traffic pattern would rise in the morning, stay high during the day, and then lower in the afternoon and into the evening. This made my theory not make sense. It didn’t make sense the load would increase when traffic on the system should be going down, until one day I did “date” on the Linux command line and got reminded the system runs on UTC and this system supports customers in North America. Ah-hah!
Some quick time math revealed that 3PM (Mountain Time) was 10PM UTC. What did that matter? Well, I recalled from OCP study that the default Oracle Database maintenance windows start at 10PM during the week. That meant that the Oracle maintenance windows would be starting during the normal user time for this product. I wanted to modify these to get them to start during the North American night.
My recollection was that Oracle had weeknight and weekend windows and that is what I’d need to change. I searched for these to verify the start time:
select window_name, repeat_interval, duration from dba_scheduler_windows where window_name like 'WEEK%'; WINDOW_NAME REPEAT_INTERVAL DURATION -------------------- -------------------------------------------------------------------------- -------------------- WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00 WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00
Good, I’d found the windows as figured I just needed to change them. So, I modified them in the scheduler. To move them on a server in UTC to the “normal” time in Mountain Time Zone I did:
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW','repeat_interval',' freq=daily;byday=SAT;byhour=07;byminute=0;bysecond=0');
The next day I checked the scheduler to see if the jobs ran at the new correct time. I checked in dba_scheduler_job_details but couldn’t find what I wanted. Some research reminded me that in 11g the DBMS_AUTO_TASK_ADMIN functionality is used. To find the information I needed to check another view:
SQL> select client_name, job_status, job_start_time, job_duration from dba_autotask_job_history order by job_start_time … auto optimizer stats collection SUCCEEDED 22-FEB-12 10.00.07.951870 PM UTC +000 03:31:40 auto space advisor STOPPED 22-FEB-12 10.00.07.971702 PM UTC +000 03:59:52 sql tuning advisor SUCCEEDED 22-FEB-12 10.00.08.004047 PM UTC +000 00:02:00
Still 10PM UTC! That wasn’t what I expected, so I decided to check on the Auto Task Admin settings:
SQL> select client_name, status, window_group from dba_autotask_client; CLIENT_NAME STATUS WINDOW_GROUP ---------------------------------------- ---------- ---------------------------------------------------------------- auto optimizer stats collection ENABLED ORA$AT_WGRP_OS auto space advisor ENABLED ORA$AT_WGRP_SA sql tuning advisor ENABLED ORA$AT_WGRP_SQ
OK, so I’ve got the Window Groups, now I had to dig in further to find out what window’s they using:
SQL> select * from dba_scheduler_window_groups; WINDOW_GROUP_NAME ENABL NUMBER_OF_WINDOWS NEXT_START_DATE COMMENTS ------------------------------ ----- ----------------- ------------------------------------ ---------------------------------------- MAINTENANCE_WINDOW_GROUP TRUE 7 24-FEB-12 05.00.00.000000 AM UTC Window group for Automated Maintenance ORA$AT_WGRP_OS TRUE 7 24-FEB-12 05.00.00.000000 AM UTC auto optimizer stats collection ORA$AT_WGRP_SA TRUE 7 24-FEB-12 05.00.00.000000 AM UTC auto space advisor ORA$AT_WGRP_SQ TRUE 7 24-FEB-12 05.00.00.000000 AM UTC sql tuning advisor SQL> select * from dba_scheduler_wingroup_members order by 1,2; WINDOW_GROUP_NAME WINDOW_NAME ------------------------------ ------------------------------ … ORA$AT_WGRP_OS FRIDAY_WINDOW ORA$AT_WGRP_OS MONDAY_WINDOW ORA$AT_WGRP_OS SATURDAY_WINDOW ORA$AT_WGRP_OS SUNDAY_WINDOW ORA$AT_WGRP_OS THURSDAY_WINDOW ORA$AT_WGRP_OS TUESDAY_WINDOW ORA$AT_WGRP_OS WEDNESDAY_WINDOW ORA$AT_WGRP_SA FRIDAY_WINDOW ORA$AT_WGRP_SA MONDAY_WINDOW ORA$AT_WGRP_SA SATURDAY_WINDOW ORA$AT_WGRP_SA SUNDAY_WINDOW ORA$AT_WGRP_SA THURSDAY_WINDOW ORA$AT_WGRP_SA TUESDAY_WINDOW ORA$AT_WGRP_SA WEDNESDAY_WINDOW ORA$AT_WGRP_SQ FRIDAY_WINDOW ORA$AT_WGRP_SQ MONDAY_WINDOW ORA$AT_WGRP_SQ SATURDAY_WINDOW ORA$AT_WGRP_SQ SUNDAY_WINDOW ORA$AT_WGRP_SQ THURSDAY_WINDOW ORA$AT_WGRP_SQ TUESDAY_WINDOW ORA$AT_WGRP_SQ WEDNESDAY_WINDOW
So, it appeared that the Auto Task Admin does not use the parent WEEKNIGHT and WEEKEND window groups, but instead the daily windows.
SQL> select window_name, repeat_interval, duration from dba_scheduler_windows order by window_name; WINDOW_NAME REPEAT_INTERVAL DURATION ------------------------------ ---------------------------------------------------------------------- ------------------ FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 MONDAY_WINDOW freq=daily;byday=MON;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 SUNDAY_WINDOW freq=daily;byday=SUN;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 WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 WEEKEND_WINDOW freq=daily;byday=SAT;byhour=07;byminute=0;bysecond=0 +002 00:00:00 WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=05;byminute=0; bysecond=0 +000 08:00:00
So, I needed to change the regular weekly windows too:
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'repeat_interval','freq=daily;byday=MON;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW', 'repeat_interval','freq=daily;byday=TUE;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW', 'repeat_interval','freq=daily;byday=THU;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW', 'repeat_interval','freq=daily;byday=FRI;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'repeat_interval','freq=daily;byday=SAT;byhour=13;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'repeat_interval','freq=daily;byday=SUN;byhour=13;byminute=0; bysecond=0');
Voila! The next day the jobs were running when I wanted them to, not during the North American day, but during the night.
SQL> select client_name, job_status, job_start_time, job_duration from dba_autotask_job_history order by job_start_time; CLIENT_NAME JOB_STATUS JOB_START_TIME JOB_DURATION ---------------------------------------- ------------------------------ ----------------------------------- ------------------ auto optimizer stats collection SUCCEEDED 24-FEB-12 05.00.02.586962 AM UTC +000 03:23:57 auto space advisor STOPPED 24-FEB-12 05.00.07.957369 AM UTC +000 03:59:52 sql tuning advisor SUCCEEDED 24-FEB-12 05.00.07.968124 AM UTC +000 00:01:58
So, whatever timezone you’re in, if it isn’t UTC or close to it and you prefer these to run during the evening (or whatever time really), you now will know how to change it.
