Use Oracle 10g's DBMS_SCHEDULER to control batch job resources

by Bob Watkins | Apr 10, 2007 7:00:00 AM

Tags: Recruitment & Selection, Resource Manager, Bob Watkins, Oracle Application Server 10g, job, job class, Oracle Corp., DBMS_SCHEDULER, Resource Plan, Consumer Resource Group, window, Oracle Tips Newsletter

Takeaway: Unlike the older DBMS_JOB package, DBMS_SCHEDULER in Oracle 10g is integrated with Resource Manager to give you precise control over the resources consumed by batch jobs.

Oracle 10g introduced a replacement for the venerable DBMS_JOB package. DBMS_SCHEDULER has many benefits over DBMS_JOB, such as easier specification of start and interval times, and the ability to schedule operating system executables as well as PL/SQL. One of the best new features in DBMS_SCHEDULER is its tight integration with Resource Manager.

Resource Manager is the part of Oracle that enables you to divide up resources, such as CPU time, among the various sessions logged on to Oracle. Sessions can be assigned to a Consumer Resource Group, and a Resource Plan divides available resources among Consumer Resource Groups.

Two DBMS_SCHEDULER features are used to integrate with Resource Manager: job classes and scheduler windows.

Job classes
A job class is a category of scheduler job. A job class can have multiple jobs assigned to it, but each job can be associated with only one job class at a time. One of the attributes of a job class is that it can include the name of a Consumer Resource Group to associate with all jobs in the class. This means that jobs in the job class get the resources given to the specified Consumer Resource Group but no more.

For example, you could create a job class named BATCH_REPORTS and associate it with a Consumer Resource Group named REPORTS_CONSUMER_GROUP. When you create a new report job, simply assign the BATCH_REPORTS job class, and it will run as a member of REPORTS_CONSUMER_GROUP.

Scheduler windows
Scheduler windows are pre-defined time periods that have a name, a start time, and a duration. A scheduler window "opens" when its start time arrives and "closes" after its duration is reached. Scheduler windows have a Resource Plan as an attribute: When the window opens, Oracle switches the current Resource Plan automatically to start using the one specified in the window.

To continue the previous example, if you had two Resource Plans named DAY_PLAN and NIGHT_PLAN, you could have two scheduler windows automatically switch between them. The DAY_WINDOW could start at 6:00 A.M. and activate the DAY_PLAN, and the NIGHT_WINDOW could start at 10:00 P.M. and activate the NIGHT_PLAN. The DAY_PLAN could restrict members of the REPORTS_CONSUMER_GROUP to no more than 25 percent of available CPU, giving preference to interactive users; the NIGHT_PLAN could reverse those percentages so that report jobs would receive 75 percent.

For more information on the features of DBMS_SCHEDULER, consult Chapter 27 in the Oracle Administrator's Guide, "Using the Scheduler."

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Print/View all Posts Comments on this article

Learn more of DBMS_SCHDULERvirag.sharma@...  | 04/11/07