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:
VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT (
job => :jobno
,what => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”HR”);END;’
,next_date => ’09/01/2004 21:00:00′
,interval => ‘TRUNC(SYSDATE) + 1 + 21/24’
);
COMMIT;
END;
/
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!
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘HR_STATS_REFRESH’
,job_type => ‘PLSQL_BLOCK’
,job_action => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”HR”);END;’
,start_date => ’09/01/2004 09:00 PM’
,repeat_interval => ‘FREQ=DAILY’
,enabled => TRUE
,comments => ‘Refreshes the HR Schema every night at 9 PM’
);
END;
/
Conclusion
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
