Database Automated Maintenance Tasks Schedule in Middle East

In the Middle East, weekends are Friday & Saturday in most countries. However, your Oracle database doesn’t know that until you tell her.

In 11g, there are 3 main automated maintenance tasks in every database enabled by default:

  1. Automatic Optimizer Statistics Collection – Gathers stale or missing statistics for all schema objects. The task name is ‘auto optimizer stats collection’.
  2. Automatic Segment Advisor – Identifies segments that could be reorganized to save space. The task name is ‘auto space advisor’.
  3. Automatic SQL Tuning Advisor – Identifies high load SQL The task name is ‘sql tuning advisor’.

These tasks can run up to 20 hours on Saturdays & Sundays utilizing a good portion of your hardware.

Oracle uses the daily windows (FRIDAY_WINDOW, SATURDAY_WINDOW, etc…) to schedule these tasks

SELECT window_name, repeat_interval, duration FROM DBA_SCHEDULER_WINDOWS;

windows maintenance default

I recommend limiting the duration for Sunday to 4 hours like the rest of the working days and increase FRIDAY_WINDOW to 20 hours.

Here are the commands to do that:

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.SUNDAY_WINDOW','duration','+00 04:00:00.000000');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.FRIDAY_WINDOW','duration','+00 20:00:00.000000');

Also note that jobs during working days start at 10 PM, it might not be suitable to start jobs around that time especially in Ramadan, Here is an example to change the starting time of the schedule for WEDNESAY_WINDOW to 1 AM

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=01;byminute=0; bysecond=0');

Hope this helps