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

Advertisements

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