Synopsis. Oracle 10g’s new DBMS_SCHEDULER package
offers significant advanced features over its predecessor, DBMS_JOB, that any reasonably
skilled Oracle DBA can use to insure that sufficient resources will always be
available for processing complex business requirements. This article – the
final in a three-part series – provides some practical examples of how the new
Scheduler can help a DBA to manage and overcome these challenges.
The previous
article in this series provided working examples of how the new Oracle
Scheduler can replace tasks originally scheduled with DBMS_JOB with
corresponding DBMS_SCHEDULER features. This final article will concentrate on
what I like to think of as the advanced features of the Oracle
Scheduler: the capability to manage a complex queue of scheduled tasks
within specific timeframes and resource utilization requirements.
Advanced Scheduling Features for Business Realities
Most business organizations usually have at least some
business processes that typically take a large amount of time, or require
comparatively large amounts of server resources, to complete. Some examples I
have encountered include:
-
Resource-intensive reporting (e.g., generation of
customers’ billing statements) -
Long-running “batch” processing (e.g., consolidation and
posting of billing detail to general ledger accounts) -
Consolidation of the current period’s data (e.g., daily or
weekly data warehouse updates) -
Data maintenance (e.g. data “pruning” to remove obsolete
information)
Typically these processes need to be invoked during off-peak
periods when sufficient resources are available, and – more importantly – when
there will be little or no contention with online transaction processing (OLTP)
activities like order entry, customer service inquiries, and order fulfillment.
To provide a sufficient example of the power of these
advanced scheduling capabilities, I will take the liberty of expanding the
requirements described in previous articles to include some new business
realities. I am basing these requirements on recent experience with my own user
community, by the way:
-
The Accounting department has just
submitted a list of brand-new reports that have to be run on a daily
basis. -
Many of these new reports are
relatively small resource consumers, but some of these reports produce large
amounts of output, and they consume a lot of system resources (i.e.
heavy sorting and grouping as well as intensive calculations). -
My database is running at peak
capacity (i.e. maximum users performing OLTP activities) in the early
morning through late afternoon, and I need to reserve sufficient system
resources for these activities. (In fact, the number of OLTP users is
increasing steadily, and, no, I cannot persuade upper management that we need a
new server to handle the ever-increasing load – yet.) -
My users understand that I must concentrate
on allocating system resources for OLTP activities, and are willing to
accept batch processing of the really large reports during off-peak
periods, but they would still like to run the smaller reports when there are
sufficient resources (if any) during peak periods. -
My development team is still
working on creating a set of packaged procedures and functions that will
eventually generate the reporting output. As DBA, I just need to insure the
reports are executed within the restrictions listed previously.
The Oracle 10g Scheduler’s advanced features
significantly simplify scheduling the tasks to support these requirements. Let’s
start with a way to group similar jobs together: job classes.
Using Job Classes to Group Scheduled Tasks
Job classes provide the capability to group similar
scheduled tasks together based on whatever criteria I decide. For example, I
might gather jobs together based on common functionality, or I might group them
based on similar resource consumption expectations.
Listing
3.1 shows how to create job classes. In this scenario, I have created
just two for now, AccountingRpts for the
new Accounting reports requirements, and DBManagement for database management tasks. I can also
specify the amount and level of Scheduler logging to be preserved for any job
in this class. Also note that I can assign a resource consumer group to
link the class to a specific Database Resource Management (DRM) resource
consumer group if I so desire. (In the interest of a simpler example, I have
chosen to leave this feature off for now.)
Assigning jobs to job classes is also simple. Assuming that
the job class already exists, it can be assigned when the job is created as one
of the arguments for the job’s definition; otherwise, the job class can be
changed by executing the DBMS_SCHEDULER.SET_ATTRIBUTE
procedure for the desired job. Note that if no job class is assigned when the
job is first created, the Scheduler’s default job class (DEFAULT_JOB_CLASS) will
be automatically assigned.
Listing
3.2 demonstrates how to assign existing jobs to the new job classes
just created.
Using Windows to Control Resource Consumption
While job classes are useful for grouping similar jobs
together, the Scheduler’s window object provides an even more powerful
set of tools: the ability to determine which database resources should
be used to process which scheduled tasks between specific time ranges
and for a specific length of time. This is precisely what I need to
satisfy my users’ new reporting requirements.
As I noted in the first
article in this series, this advanced feature is tightly coupled with
the existing Database Resource Manager (DRM) functionality that enables resource
plans, resource groups, and resource consumer groups. A full
discussion of these features is unfortunately beyond the scope of this article;
however, please see my prior articles on DRM for detailed
information as well as practical examples of resource plans.
For these new reporting requirements, I will create four
windows for the following time periods using the DBMS_SCHEDULER.CREATE_WINDOW
procedure. Listing 3.3
provides an example of creating these four windows for specific time frames
and durations:
- PEAK_AM: 06:00 through 12:00 Central Time (CT), Mondays thru Fridays
- PEAK_PM: 12:00 through 18:00 CT, Mondays thru Fridays
-
OFF_DAYS: Mondays
starting at 18:00 CT through Saturdays at 06:00 CT -
WKENDS: Saturdays
starting at 06:00 CT through Mondays at 06:00 CT
Windows can also be created based on the parameters stored
in an existing schedule object. See Listing 3.4 for an example
of this method.
Even though my development team is still working on the
packaged procedures that will create the required new reports for my users,
these new window objects firmly define how I will need to eventually schedule
the corresponding jobs that will fire to create the reporting output:
-
Jobs that execute long-running reports should definitely be
placed into the OFF_DAYS
window so that the reports will run within the strictures of the OFF-PEAK
resource plan. -
Jobs that run the smaller reports can be placed into
either the PEAK_AM or PEAK_PM
windows to allow them to run within the strictures of the PEAKTIME
resource plan. -
Any other scheduled tasks that need to execute during peak times
will be placed into the appropriate PEAK_AM or PEAK_PM windows. -
Any maintenance tasks can be placed into the OFF_DAYS or WKENDS
windows.
