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.

6 responses to “Changing the Oracle database default maintenance window time

  1. Pingback: Changing the Oracle database default maintenance window time | Jed's

  2. Mike Holden

    Copy & paste error there. You set byday=FRI for all your daily jobs!!!

  3. Huh! Dude, you just saved my Job. I had the same nightmare, a couple of weeks back!

    Many thanks!

    -Dan

  4. elhajjaji youssef

    good thing

  5. Explained and nailed :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s