DBMS_SCHEDULER.SET_ATTRIBUTE
The default maintenance window for weekdays is from 22:00 to the next day 02:00, which may be still too early for some batch jobs, although ORA$AUTOTASK are always at low priority.
Theoretically, consumers at low priority has little impact to performance, if someone raised some doubts about it, DBA may consider to stop the scheduler job at that moment.
Let's check current maintenance windows for weekdays.
SQL> column window_name format a20;
SQL> column next_start_date format a20;
SQL> column duration format a20;
SQL> alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select window_name, next_start_date, duration from dba_scheduler_windows where enabled='TRUE' order by 2;
WINDOW_NAME NEXT_START_DATE DURATION
-------------------- -------------------- --------------------
WEDNESDAY_WINDOW 2023-05-10 22:00:00 +000 04:00:00
THURSDAY_WINDOW 2023-05-11 22:00:00 +000 04:00:00
FRIDAY_WINDOW 2023-05-12 22:00:00 +000 04:00:00
SATURDAY_WINDOW 2023-05-13 06:00:00 +000 20:00:00
SUNDAY_WINDOW 2023-05-14 06:00:00 +000 20:00:00
MONDAY_WINDOW 2023-05-15 22:00:00 +000 04:00:00
TUESDAY_WINDOW 2023-05-16 22:00:00 +000 04:00:00
7 rows selected.
As you can see, they are triggered at 22:00 on weekdays by default.
In this post, we demonstrate how to change the following 2 attributes of maintenance window by DBMS_SCHEDULER.SET_ATTRIBUTE.
- REPEAT_INTERVAL
- DURATION
The attribute directs how the window should repeat.
We'd like to change the starting time of maintenance window to 00:00 for weekdays, i.e. from Monday to Friday.
The attribute specifies the duration of the window.
We'd like to change the duration of maintenance window from 20 to 18 hours for weekends, i.e. Saturday and Sunday.
REPEAT_INTERVAL
In this case, we'd like to defer them to 00:00 on weekdays, Monday to Friday. So we set the attribute of REPEAT_INTERVAL, in which, we changed BYHOUR from 22 to 0.
SQL> exec sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily; byday=MON; byhour=0; byminute=0; bysecond=0');
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily; byday=TUE; byhour=0; byminute=0; bysecond=0');
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily; byday=WED; byhour=0; byminute=0; bysecond=0');
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily; byday=THU; byhour=0; byminute=0; bysecond=0');
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'REPEAT_INTERVAL', value => 'freq=daily; byday=FRI; byhour=0; byminute=0; bysecond=0');
PL/SQL procedure successfully completed.
As you can see, we set BYHOUR to 0 which is 00:00 in REPEAT_INTERVAL.
Let's check if we have changed the next starting time.
SQL> select window_name, next_start_date, duration from dba_scheduler_windows where enabled='TRUE' order by 2;
WINDOW_NAME NEXT_START_DATE DURATION
-------------------- -------------------- --------------------
THURSDAY_WINDOW 2023-05-11 00:00:00 +000 04:00:00
FRIDAY_WINDOW 2023-05-12 00:00:00 +000 04:00:00
SATURDAY_WINDOW 2023-05-13 06:00:00 +000 20:00:00
SUNDAY_WINDOW 2023-05-14 06:00:00 +000 20:00:00
MONDAY_WINDOW 2023-05-15 00:00:00 +000 04:00:00
TUESDAY_WINDOW 2023-05-16 00:00:00 +000 04:00:00
WEDNESDAY_WINDOW 2023-05-17 00:00:00 +000 04:00:00
7 rows selected.
So far so good.
DURATION
This time, we'd like to shorten the duration from 20 to 18 hours on the weekend, Saturday to Sunday. So we set the attribute of DURATION like this:
SQL> exec sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'DURATION', value => '+000 18:00:00');
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'DURATION', value => '+000 18:00:00');
PL/SQL procedure successfully completed.
In fact, the duration is INTERVAL DAY TO SECOND, an Oracle Built-in Data Type.
Let's check if we have changed the duration.
SQL> select window_name, next_start_date, duration from dba_scheduler_windows where enabled='TRUE' order by 2;
WINDOW_NAME NEXT_START_DATE DURATION
-------------------- -------------------- --------------------
THURSDAY_WINDOW 2023-05-11 00:00:00 +000 04:00:00
FRIDAY_WINDOW 2023-05-12 00:00:00 +000 04:00:00
SATURDAY_WINDOW 2023-05-13 06:00:00 +000 18:00:00
SUNDAY_WINDOW 2023-05-14 06:00:00 +000 18:00:00
MONDAY_WINDOW 2023-05-15 00:00:00 +000 04:00:00
TUESDAY_WINDOW 2023-05-16 00:00:00 +000 04:00:00
WEDNESDAY_WINDOW 2023-05-17 00:00:00 +000 04:00:00
7 rows selected.
The new settings of maintenance windows will take effect next time they start, so if the performance is affected by AUTOTASK right now, you may consider to stop the scheduled job.
Theoretically, overnight batch jobs are not likely impacted by AUTOTASK jobs with such low priorities, but you still have a choice to screen the possibilities by disabling all AUTOTASK jobs.