Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 31, 2004

The Oracle 10g Scheduler, Part 1: Overview

By Jim Czuprynski

Synopsis. Oracle 10g's new DBMS_SCHEDULER package offers significant improvements over DBMS_JOB for scheduling jobs and tasks. This article - the first in a series - provides an overview of the new functionalities that the Oracle Scheduler provides for DBAs.

One of my first tasks I was assigned as a newly-minted DBA was to help one of our senior developers to schedule an Oracle job via DBMS_JOB. I proudly showed my colleague how to schedule his stored procedure so that it would run every Tuesday, Wednesday and Thursday, how to determine when the job was going to run, and how easy it was to "break" the job temporarily.

When I finished, to my surprise my colleague told me that it was just about the most complex implementation of scheduling he'd ever seen, and then showed me how much simpler it was to implement a scheduled task in a Microsoft SQL Server database. "See, I can name my job any way I want, and look how easy it is to specify when I want to run the job, or halt it from running!" he said. "And look - here's a log of when the job was last run. Simple!"

Unfortunately, my colleague was right. As time passed, I found that compared to other Oracle features like Database Resource Management, the rather arcane Oracle scheduler mechanisms provided by DBMS_JOB needed a serious overhaul. Fortunately, Oracle 10g's new Scheduler overcomes many of the shortcomings of DBMS_JOB, and provides a robust, flexible architecture to handle the most mundane scheduling task.

Scheduler Basic Features

Basic Task Scheduling. First off, fear not! The Scheduler does keep the basic functionality of DBMS_JOB intact:

  • A task can be scheduled to run at a particular date and time.
  • A task can be scheduled to run only once, or multiple times.
  • A task can be turned off temporarily or removed completely from the schedule.
  • Complex scheduling is still available, but now much simpler. (For example, DBMS_JOB could be manipulated into running a task every Tuesday, Thursday and Saturday at 08:00, but it did take some experimentation with NEXT_DATE and the INTERVAL parameter of DBMS_JOB before I got it right.)

Scheduler Components

The Scheduler uses three basic components to handle the execution of scheduled tasks. An instance of each component is stored as a separate object in the database when it is created:

Programs. A program defines what the Scheduler will execute. A program's attributes include its name, its type (e.g. a PL/SQL procedure or anonymous block), and the action it is expected to perform. A program can also accept zero to many arguments, which makes it a flexible building block for constructing schemes of tasks to be scheduled.

Schedules. A schedule defines when and at what frequency the Scheduler will execute a particular set of tasks. A schedule's attributes include the date on which a set of tasks should begin, how often the tasks should be repeated and when the set of tasks should no longer be executed, either as of a specified date and time, or after a specified number of repetitions.

Jobs. A job assigns a specific task to a specific schedule. A job therefore tells the schedule which tasks - either one-time tasks created "on the fly," or predefined programs - are to be run. A specific program can be assigned to one, multiple, or no schedule(s); likewise, a schedule may be connected to one, multiple, or no program(s).

The beauty of the redesigned Scheduler' is that it relies upon the reuse of these three basic objects. This corrects one of the more serious shortcomings of DBMS_JOB: For each scheduled task, a separate job had to be created, even if the task being performed was essentially identical.

A perfect example of this shortcoming is refreshing table and index statistics. Since a database's objects are typically not spread evenly across multiple schemas, I normally scheduled statistics refresh for different schemas at different frequencies, which meant I needed to create separate DBMS_JOBs for each invocation of DBMS_STATS.GATHER_SCHEMA_STATS. With the Scheduler, though, I can now create a program that accepts the schema owner as an argument, create an appropriate schedule for each schema, and then schedule separate jobs to run at the appropriate time for each schema.

Scheduler Advanced Features

The new Scheduler also offers some advanced features that DBMS_JOB never offered. Here is a brief sampling that I will flesh out in the next series of articles:

Job Classes. I am probably one of the few ex-mainframers who will admit that he enjoyed working with Job Control Language (JCL). I loved its restartability and especially the level of control it gave me to accomplish a complex set of tasks in background mode. Moreover, I especially savored the concept of a job class, a set of resource thresholds that helped insure jobs that needed fewer resources (e.g., less CPU or shorter run time) would get precedence over jobs that were expected to run longer or consume more resources.

In the same way, the Scheduler provides the capability to group together jobs that have similar resource demands into job classes. A job class can be used to insure all jobs within it utilize the same job class attributes, execute at a higher or lower priority than other jobs in other job classes and only allow jobs in the job class to start if there are sufficient resources available. For example, job class InstantInvoice might encompass tasks that call packages and procedures that produce invoices instantly after a customer has been serviced completely, while job class DBManagemt might encompass tasks that are related to database backups, exports and statistics calculation.

Windows. Most database shops I have worked in tend to have periods of peak and off-peak use. For example, many U.S. companies typically perform the majority of their on-line transaction processing tasks such as order fulfillment, customer service, and production (manufacturing or supply of services) during the morning, afternoon, and early evening, with demand tapering off during the evening and early morning.

The Scheduler acknowledges this business reality, and provides the concept of windows to assign resources to job classes. For example, window PeakTime might be established for scheduled tasks that give 75% priority to the aforementioned InstantInvoice job class, but only a 25% priority to all other job classes, during peak activity periods. Likewise, an OffPeak window could be established for scheduled database maintenance that would give jobs in the DBManagemt job class 90% priority over all other job classes during periods of off-peak usage.

Window Groups. The Scheduler also allows windows with similar scheduling properties - for example, normal business weekday off-peak time, weekends and holidays - to be collected within window groups for easier management of jobs and scheduled tasks.

Window Overlaps. The Scheduler also acknowledges that it is possible to have windows overlap each other, and it does provide a simple conflict-resolution method to insure that the appropriate jobs do get the appropriate resources. (I promise to elucidate on this in greater detail in my next articles.)

As you might guess, much of the functionality in these advanced features is coupled with the existing Database Resource Manager (DRM) functionality that enables and enforces resource groups. See my prior articles on Oracle DRM for more information.

Monitoring and Managing the Scheduler

Prior to Oracle 10g, my ability as a DBA to manage the job queue was limited to two data dictionary views, DBA_JOBS and DBA_JOBS_RUNNING, that respectively allowed me to determine when a job was going to run next and whether a job was running right now. Moreover, if a job was already running that needed to be cancelled, I had to follow special procedures to insure it was cancelled properly, especially when working within the confines of Windows NT.

The new Scheduler offers myriad database views for monitoring the status of all its components, including jobs, programs, program arguments, schedules, job classes, and windows. The Scheduler also creates a log entry for each instance of each job or task that the Scheduler has run, and records its current status. I will cover this in much greater detail in the next articles in this series.

The Cornerstone: DBMS_SCHEDULER

The Scheduler uses the supplied PL/SQL package DBMS_SCHEDULER to handle almost all scheduling functionality. I will delve into its features in much greater detail in my next article, but here's a brief sample to pique your interest.

To carry forth one of my previous business scenarios, I need to schedule a task that will recalculate statistics on all tables and indexes in just the HR schema of my database. In addition, I need that task to run every Monday, Tuesday, Wednesday, Thursday, and Friday evening at 9 PM. Here is an example of scheduling that task with DBMS_JOB:

		 job => :jobno
		,next_date => '09/01/2004 21:00:00'
		,interval => 'TRUNC(SYSDATE) + 1 + 21/24'

Here is an example of scheduling that same task with DBMS_SCHEDULER. Note that the syntax is now much clearer, and the parameters even now make sense when compared to DBMS_JOB. And gone at last is that wacky INTERVAL parameter!

		 job_name => 'HR_STATS_REFRESH'
		,job_type => 'PLSQL_BLOCK'
		,start_date => '09/01/2004 09:00 PM'
		,repeat_interval => 'FREQ=DAILY'
		,enabled => TRUE
		,comments => 'Refreshes the HR Schema every night at 9 PM'


Oracle 10g's new Scheduler offers some long-awaited features for flexible and effective scheduling of repetitive tasks within an Oracle database without the encumbrances of the outmoded DBMS_JOB PL/SQL supplied package. In my next article, I will discuss more practical examples of how to implement the Scheduler's features, including how to transition effectively from DBMS_JOB to DBMS_SCHEDULER.

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article:

B10739-01 Oracle 10g Concepts, Chapters 25-28

» See All Articles by Columnist Jim Czuprynski

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM