The Oracle 10g Scheduler, Part 3: Advanced Features

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
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

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

  • 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
    (i.e. maximum users performing OLTP activities) in the early
    morning through late afternoon,
    and I need to reserve sufficient system
    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.

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.

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
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
Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles