The default maintenance window is not a good fit for every database, by default the maintenance window start at 10 PM and run for 4 hours during the week and start at 6 AM and last for 20 hours during the week-end. A different window exists for each day of the week and all the windows are grouped inside the MAINTENANCE_WINDOW_GROUP. It is possible to alter each window independently or to create a single window for all days of the week.

The following maintenance tasks are performed during the maintenance window:

• Optimizer statistics (OPTIMIZER_STATS)
• Segment advisor (SEGMENT_ADVISOR)
• SQL Tuning (SQL_TUNE_ADVISOR)
• Health Monitor (HEALTH_MONITOR)

And the default window group named MAINTENANCE_WINDOW_GROUP is associated with a DEFAULT_MAINTENANCE_PLAN which has the following configuration:

Consumer Group/subplan	Level 1	Maximum Utilization Limit
ORA$AUTOTASK	5%	90
OTHER_GROUPS	20%	–
SYS_GROUP	75%	–

In this plan 75% of the resources are affected to sessions created by SYS or SYSTEM user. If these resources are not used by the SYS_GROUP then they are transfered to the other groups. As you can see there is also a restriction for the automatic maintenance tasks, they cannot use more that 90% of the resources.

here is what you is configured by default, independently of the management pack configured and the edition you use. This configuration is from a standard edition database with control_management_pack_access set to NONE.

SQL> select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME          WINDOW_NEXT_TIME                                   WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
-------------------- -------------------------------------------------- ----- -------- -------- -------- -------- --------
SUNDAY_WINDOW        27-MAR-16 06.00.00.000000 AM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
SATURDAY_WINDOW      26-MAR-16 06.00.00.000000 AM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
FRIDAY_WINDOW        25-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
THURSDAY_WINDOW      24-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
WEDNESDAY_WINDOW     23-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
TUESDAY_WINDOW       29-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
MONDAY_WINDOW        28-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

Maintenance example

In my case the database i’m working on is a 24/7 database which has less activity between 10 AM and 12 AM every day. I will create a new maintenance window named MORNING_WINDOW:

-- Create a 2 hours maintenance window every day beginning at 10:00 AM
BEGIN
	dbms_scheduler.create_window(
	window_name=>'MORNING_WINDOW',
	resource_plan=>'DEFAULT_MAINTENANCE_PLAN',
	repeat_interval=>'freq=daily;byhour=10;byminute=0;bysecond=0',
	duration=>interval '2' hour,
	comments=>'Maintenance window');
END;
/

Then we need to add the maintenance window to the MAINTENANCE_WINDOW_GROUP:

-- Add the newly created window to the MAINTENANCE_WINDOW_GROUP
BEGIN
	dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP',
	'MORNING_WINDOW');
END;
/

Let’s remove the default windows from MAINTENANCE_WINDOW_GROUP, we don’t need them anymore.

-- remove default maintenance windows from the MAINTENANCE_WINDOW_GROUP
BEGIN
	dbms_scheduler.remove_window_group_member('MAINTENANCE_WINDOW_GROUP',
	'MONDAY_WINDOW');
	dbms_scheduler.remove_window_group_member('MAINTENANCE_WINDOW_GROUP',
	'TUESDAY_WINDOW');
	dbms_scheduler.remove_window_group_member('MAINTENANCE_WINDOW_GROUP',
	'WEDNESDAY_WINDOW');
	dbms_scheduler.remove_window_group_member('MAINTENANCE_WINDOW_GROUP',
	'THURSDAY_WINDOW');
	dbms_scheduler.remove_window_group_member('MAINTENANCE_WINDOW_GROUP',
	'FRIDAY_WINDOW');
	dbms_scheduler.remove_window_group_member('MAINTENANCE_WINDOW_GROUP',
	'SATURDAY_WINDOW');
	dbms_scheduler.remove_window_group_member('MAINTENANCE_WINDOW_GROUP',
	'SUNDAY_WINDOW');
END;
/

Here is the new configuration:

SQL> select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME          WINDOW_NEXT_TIME                                   WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
-------------------- -------------------------------------------------- ----- -------- -------- -------- -------- --------
MORNING_WINDOW       24-MAR-16 10.00.00.000000 AM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

Deactivating some maintenance tasks

If you, like me and are in Standard Edition you have not use of the SQL Tuning advisor for example, you have no right to use it, but it runs in your maintenance window for nothing, let’s disable it:

-- disable sql tuning advisor
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

If you want to deactivate the task only for a specific window you can use :

-- disable sql tuning advisor on monday
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => 'MONDAY_WINDOW');
END;
/

If you want to deactivate all the tasks, use the following command:

 -- disable all maintenance tasks
BEGIN
  dbms_auto_task_admin.disable;
END;
/

Altering the default maintenance windows

If you choose to keep the default maintenance windows and just change one of them to adapt it to you needs you can use the DBMS_SCHEDULER.SET_ATTRIBUTE prodcedure.

Change the interval :

-- set the MONDAY_WINDOW to run on monday a 10 AM
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=weekly;byday=MON;byhour=10;byminute=0;bysecond=0');

Change the duration of the Window:

-- change duration to 4 hours
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','duration',numtodsinterval(2, 'hour'));

If you need more information you can have a look at the oracle documentation:

Managing Automated Database Maintenance Tasks

Good maintenance 🙂