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