The Oracle 10g Scheduler, Part 3: Advanced Features

Thursday Oct 28th 2004 by Jim Czuprynski

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.

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.

Key Windows Concepts

Since Scheduler windows are a powerful and elegant toolset, any discussion about them can grow complex, so I have attempted to distill them down to a few basic concepts:

Defined Schedules and Priorities. First and foremost, it is important to remember that windows open and close based on their defined schedules when each window was created. In addition, each window has a defined priority (either HIGH or LOW) that the Scheduler uses to resolve conflicts between windows when their schedules overlap. Note that a window can be modified without affecting a window that is currently open; the change will just take effect the next time a window is opened.

Opening A Window Implies a Resource Plan Switch. Whenever a Scheduler window opens, its corresponding resource plan is applied to all jobs that are scheduled to run, (or indeed are running,) within the window's time frame. This ability to switch resource availability is obviously at the heart of windows scheduling, as it allows the database to make selected resources available for some types of scheduled tasks while denying them to others. Likewise, when a window closes, the resource plan that is put into effect is determined by the next window that is opened.

Handling Window Overlaps. The Scheduler will only allow one window to be open at any one time. If schedules for two windows accidentally overlap, or if a window is forced open, then the Scheduler will attempt to make an intelligent decision about which window should actually be opened (and therefore what resource plan should be in effect) based on the following rules:

  • If two overlapping windows have the same priority, the active window will stay open; otherwise, the window with the lowest priority is closed.
  • If the end of a window's duration is reached, but multiple windows are defined for that time period, the window that has the highest percentage of remaining time will be opened.
  • If a window is open, but it is dropped, it is automatically closed, and the immediately previous rule applies if multiple windows still overlap.

Forcing Windows To Open or Close. Finally, as I alluded to previously, it is indeed possible to force open a window that is currently closed. Likewise, it is possible to force a window that is currently open to close. However, when either action occurs, the Scheduler will use the rules described above to determine which window's resource plan should now be applied. Listing 3.5 shows how to force a currently open window to close and how to force a currently closed window to open prematurely.

Listing 3.6 shows a simple report that displays an abbreviated listing from the appropriate Scheduler view, DBA_SCHEDULER_WINDOW_LOG, that shows the most recent window openings and closings. Here is a sample of the output for the windows I have just defined:

SQL> TTITLE 'Recent Window Status:'
SQL> COL oper_date           FORMAT A20  HEADING 'Log Date'
SQL> COL window_name         FORMAT A32  HEADING 'Window Name'
SQL> COL operation           FORMAT A12  HEADING 'Open/|Closed'
  2       TO_CHAR(log_date, 'mm-dd-yyyy hh24:mi:ss') oper_date
  3      ,window_name
  4      ,operation
  5    FROM dba_scheduler_window_log
  6   WHERE operation IN ('OPEN', 'CLOSE')
  7   ORDER BY log_date DESC
  8  ;

                             Recent Window Status:

Log Date             Window Name          Closed
-------------------- -------------------- ------
10-26-2004 12:00:03  OFF_DAYS             OPEN
10-26-2004 18:00:02  PEAK_PM              CLOSE
10-26-2004 12:00:02  PEAK_PM              OPEN
10-26-2004 12:00:01  PEAK_AM              CLOSE
10-26-2004 06:00:03  PEAK_AM              OPEN
10-26-2004 06:00:02  OFF_DAYS             CLOSE

6 rows selected.

Window Groups

The Scheduler also provides the window group object for organizing similar windows. Window groups are analogous in functionality to the job class object for jobs, and are implemented in a similar fashion.

Listing 3.7 demonstrates how to create window groups. In this example, I have created two groups, PEAKTIME and OFF-PEAK, that I have used to group the four windows I created previously.

Scheduling Calendar

Prior to Oracle 10g, I knew I could always tell when the next (iteration +1) occurrence of a particular DBMS_JOB task was going to run based on the NEXT_DATE and NEXT_SEC columns of the DBA_JOBS view. However, I usually found myself muttering under my breath and consulting a desk calendar to determine when any other future scheduled iterations (+2, +3, and so forth) would occur, especially when the DBMS_JOB entry had been set up with a relatively complex value for INTERVAL.

The new Scheduler has addressed this with one really neat feature that I just have to chortle about: the Scheduling Calendar. The calendar can be easily interrogated for any number of future occurrences of a scheduled task via the DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING procedure. Listing 3.8 shows a simple stored procedure that accepts any calendar string value and returns as many desired iterations of the schedule, and Listing 3.9 shows an example of how to use this procedure to validate the next 20 scheduled occurrences based on the supplied calendar string.

Viewing Advanced Scheduler Features Metadata

As in my previous article, I have assembled some queries that I have found useful in returning metadata about the Scheduler objects that support these advanced features.

Job Class Objects. view DBA_SCHEDULER_JOB_CLASSES provides information about all job class objects in the database, as shown by the query in Listing 4.1.

Window Objects. View DBA_SCHEDULER_WINDOWS shows all window objects and their attributes, and view DBA_SCHEDULER_WINDOW_DETAILS shows logged detail about window objects' last executions. Listing 4.2 and Listing 4.3 demonstrate sample queries against these two views, respectively.

Window Group Objects. The DBA_SCHEDULER_WINDOW_GROUPS view gives details on all existing window groups as shown in Listing 4.4. View DBA_SCHEDULER_WINGROUP_MEMBERS shows which windows have been attached to which window groups, as demonstrated in Listing 4.5.


The Oracle 10g Scheduler's advanced features offer extensive control and effective management of database resources for scheduled tasks, especially for resolving conflicts between tasks that require access to similar resources simultaneously.

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:

B10802-01 Oracle 10g PL/SQL Packages and Types Reference

B10739-01 Oracle 10g Concepts, Chapters 25-28

» See All Articles by Columnist Jim Czuprynski

Mobile Site | Full Site