The Oracle 10g Scheduler, Part 3: Advanced Features

October 28, 2004

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.