23 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 o f those maintenance tasks. When the designated time period ends, the database can switch to a different resource plan that lowers the resource allocation for any remaining maintenance tasks.

This chapter consists of the following sections:

• Maintenance Windows

• Automatic Statistics Collectio n Job

• Resource Management

Maintenance Windows

The Scheduler is a collection of functions and procedures packages in the DBMS_SCHEDULER package. The fully described beginning in Chapter 26, " Overview of Scheduler Concepts".

Two Scheduler windows are predefined upon installation of Oracle Database:

• WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.

• WEEKEND_WINDOW covers whole days Satu rday and Sunday.

Together these windows constitute the MAINTENANCE_WINDOW_GROUP in which all system mai ntenance tasks are scheduled. Oracle Database uses the maintenance windows for automatic statistics collection and for some other int ernal system maintenance jobs. If you are using the Resource Manager, you can also assign resource plans to these windows.

You can adjust the predefined maintenance windows to a time suitable to your database environment using the DBMS_SCHEDULER.SET_ATT RIBUTE procedure. For example, the following script moves the WEEKNIGHT_WINDOW to midnight to 8 a.m. every weekda y morning:

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(
   'WEEKNIGHT_WINDOW', 
   'repeat_interval',
   'freq
=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');

You can also use the SET_ATTRIBUTE pr ocedure to adjust any other property of a window. For example, the following script sets resource plan DEFAULT_MAINTENANCE_PLAN for the WEEKNIGHT_WINDOW:

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE (
   'WEEKNIGHT_WIN
DOW',
   'resource_0plan',
   'DEFAULT_MAINTENANCE_PLAN');

If you have already enabled a different resource plan, Oracle Database will make the DEFAULT_MAINTENANCE_PLAN when the WEEKNIGHT_WINDOW opens, and will reactivate the or iginal resource plan when the WEEKNIGHT_WINDOW closes.

See Also:

Chapter 27, " Using the Scheduler" for more information on the DBMS_SCHEDULER package and the SET_ATTRIBUTE procedure and PL/SQL Packages and Types Reference for reference information on the DBMS_SCHEDULER package

Automatic Statistics Collection Job

A Scheduler program GATHER_STATS_PROG and Scheduler job GATHER_STATS_JOB are predefined on installation of Oracle Database. GATHER_STATS_PROG collects optimizer statistics for all objects in t he database for which there are no statistics or only stale statistics. GATHER_STATS_JOB is defined on GATHER_STAT S_PROG and is scheduled to run in the MAINTENANCE_WINDOW_GROUP.

If you prefer to manage statistics collect ion manually, you can disable the job as follows:

EXECUTE DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
<
/pre>








See Also:
Oracle Database Performance Tuning Guide for
 more information on automatic statistics collection





 
 
Resource Management 
A Resource Manager consumer group, AUTO_TASK_CO
NSUMER_GROUP, is predefined on installation of Oracle Database, and a Scheduler job class AUTO_TASKS_JOB_CLASS is
 defined based on this consumer group. The GATHER_STATS_JOB is defined to run in the AUTO_TASKS_JOB_CLASS j
ob class.
When a resource plan is activated in the system, GATHER_STATS_JOB and any internal system tasks conform
 to the resource consumption specified for AUTO_TASK_CONSUMER_GROUP in this resource plan.










See Also:
Chapter 24
, " Using the Database Resource Manager" for more information about creating and modifying resource plans.












Previous


Next


Copyright © 2001, 2003 Oracle Corporation

All Rights Reserved.





Home


Book List



Contents


Index


Master
 Index


Feedback