Managing Automatic System Tasks Using the Maintenance Window
Oracle Database is preconfigured to perform some routine database maintenance tasks so that you can run them at times when the system load is expected to be light. You can specify for such a time period a resource plan that controls the resource consumption of those maintenance tasks.
Oracle Scheduler enables you to create time windows during which jobs are automatically run. A typical Scheduler window defines a start time, a duration, and optionally a resource plan to activate. A Scheduler job can then name a window as its schedule. (When the window "opens," the job begins to run.) In addition, windows can be combined into window groups, and if a job names a window group as its schedule instead of naming a window, the job runs whenever any of the windows in the window group opens.
Two Scheduler windows are predefined upon installation of Oracle Database:
WEEKNIGHT_WINDOWstarts at 10 p.m. and ends at 6 a.m. every Monday through Friday.
WEEKEND_WINDOWcovers whole days Saturday and Sunday.
Together these windows constitute the
MAINTENANCE_WINDOW_GROUP in which all system maintenance tasks are scheduled. Oracle Database uses the maintenance windows for automatic statistics collection, for space management, and for some other internal system maintenance jobs.
You can adjust the predefined maintenance windows to a time suitable to your database environment using the
DBMS_SCHEDULER.SET_ATTRIBUTE procedure. For example, the following script moves the
WEEKNIGHT_WINDOW to midnight to 8 a.m. every weekday morning:
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE( 'WEEKNIGHT_WINDOW', 'repeat_interval', 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');
Note that the duration of the window was already eight hours, so the script did not need to change it.
You can also use the
SET_ATTRIBUTE procedure to adjust any other property of a window. For example, the following script sets resource plan
DEFAULT_MAINTENANCE_PLAN for the
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE ( 'WEEKNIGHT_WINDOW', 'resource_plan', 'DEFAULT_MAINTENANCE_PLAN');
this case, if you have already enabled a different resource plan, Oracle Database will make the
DEFAULT_MAINTENANCE_PLAN active when the
WEEKNIGHT_WINDOW opens, and will reactivate the original resource plan when the
The following jobs are among the automatic system tasks that are predefined to run in the maintenance windows:
Automatic Statistics Collection Job
Automatic Segment Advisor Job
A Scheduler job
GATHER_STATS_JOB is predefined upon installation of Oracle Database.
GATHER_STATS_JOB collects optimizer statistics for all objects in the database for which there are no statistics or only stale statistics.
If you prefer to manage statistics collection manually, you can disable the job as follows:
A Scheduler job
AUTO_SPACE_ADVISOR_JOB is also predefined upon installation.
AUTO_SPACE_ADVISOR_JOB runs the Automatic Segment Advisor, which identifies segments that have space available for reclamation, and then makes recommendations that you can view with Enterprise Manager or a set of PL/SQL package procedures.
You can run the Segment Advisor manually to obtain more up-to-the-minute recommendations or to obtain recommendations on segments that the Automatic Segment Advisor did not examine for possible space reclamation.
If you want to control how CPU resources are allocated to the automatic system tasks so that these tasks do not significantly impact the performance of other database applications, you can create a resource plan and assign the plan to the maintenance windows. (These windows initially have no resource plan assigned.) This resource plan must include directives for the predefined consumer group
AUTO_TASK_CONSUMER_GROUP, which is the Resource Manager consumer group under which the automatic system tasks run.
The connection between the automatic system tasks and the
AUTO_TASK_CONSUMER_GROUP is made as follows: A Scheduler job class
AUTO_TASKS_JOB_CLASS is predefined and names
AUTO_TASK_CONSUMER_GROUP as its
resource_consumer_group attribute. The automatic system tasks
AUTO_SPACE_ADVISOR_JOB are defined to run in the
AUTO_TASKS_JOB_CLASS job class.