Oracle Scheduler maintenance windows: How long they can run?

With previous post you see the duration of stats job, but how long they can run?

Well the answer for that would be depends on the scheduler windows.

To understand scheduler windows, according to documentation:-

A maintenance window is a contiguous time interval during which automated maintenance tasks are run. Maintenance windows are Oracle Scheduler windows that belong to the window group named MAINTENANCE_WINDOW_GROUP. A Scheduler window can be a simple repeating interval (such as "between midnight and 6 a.m., every Saturday"), or a more complex interval (such as "between midnight and 6 a.m., on the last workday of every month, excluding company holidays").

When a maintenance window opens, Oracle Database creates an Oracle Scheduler job for each maintenance task that is scheduled to run in that window. Each job is assigned a job name that is generated at runtime. All automated maintenance task job names begin with ORA$AT. For example, the job for the Automatic Segment Advisor might be called ORA$AT_SA_SPC_SY_26. When an automated maintenance task job finishes, it is deleted from the Oracle Scheduler job system. However, the job can still be found in the Scheduler job history.

Each window is assigned to the resource plan DEFAULT_MAINTENANCE_PLAN which is enabled automatically when the maintenance windows are opened. The DEFAULT_MAINTENANCE_PLAN resource plan has a number of consumer groups assigned to it and various associated tasks including:

• ORA$AUTOTASK_SQL_GROUP - Automatic SQL Tuning tasks are assigned to this consumer group.
• ORA$AUTOTASK_SPACE_GROUP - Segment Advisor tasks are assigned to this group.
• ORA$AUTOTASK_STATS_GROUP - Automatic statistics gathering is assigned to this group.

Oracle provides a view that makes it easy to see each of the maintenance windows. This is the DBA_AUTOTASK_SCHEDULE view. The DBA_AUTOTASK_SCHEDULE view contains a 31 day record of all the autotask windows to be executed. This example shows the various autotask schedules:

SQL> SELECT * FROM dba_autotask_schedule ORDER BY start_time;

WINDOW_NAME      START_TIME                          DURATION
---------------- ----------------------------------- -------------
SUNDAY_WINDOW    30-DEC-07 06.00.00.300000 AM -07:00 +000 20:00:00
MONDAY_WINDOW    31-DEC-07 10.00.00.300000 PM -07:00 +000 04:00:00
TUESDAY_WINDOW   01-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
WEDNESDAY_WINDOW 02-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
THURSDAY_WINDOW  03-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
FRIDAY_WINDOW    04-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
SATURDAY_WINDOW  05-JAN-08 06.00.00.300000 AM -07:00 +000 20:00:00
SUNDAY_WINDOW    06-JAN-08 06.00.00.300000 AM -07:00 +000 20:00:00
MONDAY_WINDOW    07-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
TUESDAY_WINDOW   08-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00
WEDNESDAY_WINDOW 09-JAN-08 10.00.00.300000 PM -07:00 +000 04:00:00

Note:- in the output of the query that the weekday windows start at 10PM, and run for 4 hours. The weekend windows start at 6am and run for 20 hours.

Let’s see what window my stats job has been assigned

SQL> select job_name, enabled, state, program_name, schedule_name from dba_scheduler_jobs where job_name like 'GATHER_STATS_JOB';

This query produces a result similar to the following:

So the schedule name is MAINTENANCE_WINDOW_GROUP, and this maintenance window will be active on weekdays and weekends runs as above timings and closed in that duration.