Synopsis. Oracle 10g Release 1 (10gR1) added a new and powerful scheduling tool - aptly named the Scheduler - that significantly augments an Oracle DBA's abilities to schedule and control just about any type of frequently repeating task from within an Oracle 10g database. This article - the first in this series -- illustrates how to utilize several enhancements added to the Scheduler in Oracle 10g Release 2 (10gR2) to create even more complex schedules with the new schedule combination features, and how to trigger a job based on an event in an event queue.

As my responsibilities as an Oracle DBA have increased over the years, I have noticed a commensurate increase in ever-more complex requirements for scheduling tasks to occur at frequent - and sometimes less-than frequent! - intervals. Here are some of the typical scheduling requirements I have faced:

• Special Handling for Holidays. Many business processes need to run at a particular time on a daily basis. However, when a holiday interrupts the normal business schedule, it may make sense to suspend those processes until the next “regular” business day. Scheduling around the November and December holidays for U.S. businesses can get complex, especially in years like 2005 when Christmas and New Year's Day both fell on a weekend.

• Complex Scheduling For Period-Ending Processes. One of my clients needed to schedule their three-day month-end processing to commence no later than the second business day of the month … that is, unless a national holiday preceded that day, in which case the processing schedule shifted ahead by one day … unless, of course, the shifting of the schedule would cause the jobs to start on a Friday evening, because no users would be available over the weekend to verify issues that might arise after processing had completed. (I am only slightly exaggerating this scenario.)

• External File Batch Processing. A vendor was supposed to supply an external file every day that contained paid invoices, rejected credit card transactions, and other accounting information. Unfortunately, this file tended to arrive on an infrequent basis, so I needed to first verify if the file had been transferred in the last 24 hours; if no file was present, I needed to inform the accounting support team that our vendor has once again forgotten to send the file in a timely fashion. However, if a new file was indeed present, I needed to validate the contents of the new file for any errors; if no errors are found, the data was to be loaded into several production database tables. Once the file had been successfully processed, I needed to notify an end user that the data is ready for review.

Handling these types of scheduling requests usually meant assembling a carefully constructed set of DBMS_JOB tasks, then insuring that the on-call DBA fired off the appropriate tasks in the appropriate order. In the case of complex scheduling changes because of holidays, this usually meant a careful disassemble / reassemble of the previous DBMS_JOB schedule. Fortunately, Oracle 10gR2 has made these types of scheduling nightmares a thing of the past with some nifty new enhancements to the Oracle 10g Scheduler. (For a complete picture of the types of scheduling tasks the Oracle Scheduler can handle as part of Oracle 10g Release 1, please see my previous article series that discusses those features.)

Upgrades to Scheduler Calendaring Expressions

In Oracle 10gR1, the Oracle Scheduler already boasted a robust set of calendaring expression features that could handle most complex scheduling tasks. For example, the Scheduler made it relatively simple to create a schedule that runs every six hours starting at 06:00 on March 1, 2006, and that terminates on March 31, 2006:

BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE (
         schedule_name => 'SpecialSchedule_032006'
        ,start_date => '03/01/2006 06:00'
        ,end_date => '03/31/2006 18:00'
	  ,repeat_interval => 'FREQ=HOURLY; INTERVAL=6'
	  ,comments => 'Special March 2006 4x daily schedule'
    );
END;
/

This schedule can then be invoked by multiple Scheduler jobs so that they all run on the same schedule; each job can call two different Scheduler programs to accomplish their goals simultaneously:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
         job_name => 'SpecialEndofMth'
        ,job_type => 'STORED_PROCEDURE'
        ,program_name => 'sp_EndOfMonth'
        ,schedule_name => 'SpecialSchedule_032006'
	  ,comments => 'Special March 2006 End-of-Month processing'
    );
    DBMS_SCHEDULER.CREATE_JOB (
         job_name => 'SpecialEndofQtr'
        ,job_type => 'STORED_PROCEDURE'
        ,program_name => 'sp_EndOfQuarter'
        ,schedule_name => 'SpecialSchedule_032006'
	  ,comments => 'Special March 2006 End-of-Quarter processing'
    );
END;
/

Building Highly-Selective Schedules: BYDATE, OFFSET and SPAN

While this type of scheduling is usually more than sufficient for most processing requirements, I now have the capability to create extremely selective schedules with the new BYDATE parameter. BYDATE allows me to include into a schedule a series of dates based on a date mask, or even specific calendar dates. For example, to build a schedule that is triggered on the tenth day of January, April, July, and October for all current and future years, I could specify the following calendaring expression for a schedule's repeat_interval parameter:

BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE (
         schedule_name => 'SampleAnnualSchedule'
  ,repeat_interval => FREQ=YEARLY; BYDATE='0110,0410,0710,1015'
	  ,comments => 'Example of an annual calendar using date masks'
    );
END;
/

The BYDATE parameter also offers some interesting possibilities with its two optional qualifiers, SPAN and OFFSET. SPAN allows me to choose a range of dates that begins on the specific date or date mask and extends into the future for the number of days specified. On the other hand, OFFSET, SPAN's counterpart, lets me choose a range of dates that begins the specified number of days prior to the specified date and then ends on the specified date itself.

Listing 1.1 shows yet another interesting possibility that the BYDATE parameter offers: the creation of a standard annual holiday schedule for specific dates. In this example, I have created a Scheduler object named StandardHolidays that describes the five-year holiday schedule spanning 2006 through 2010. I've included a rather typical example of U.S. holidays (New Year's Day, Martin Luther King's Birthday, Memorial Day, Independence Day, Labor Day, Thanksgiving and the following Friday, Christmas Eve, Christmas Day, and New Year's Day).

Listing 1.2 illustrates how to use the SPAN and OFFSET directives to create two new schedules, FifthDayCycle and March35thBilling, that will start and end based on specific dates or date masks.

Combining Schedules: INCLUDE, EXCLUDE, and INTERSECT

Oracle 10gR2 offers three new Scheduler directives specific to Schedule objects that allow two schedules to be combined into one:

• The INCLUDE directive tells the Scheduler to combine all occurrences found in one schedule with all occurrences found in a second schedule (essentially the same as the SQL UNION operation).
• The EXCLUDE directive, on the other hand, tells the Scheduler to subtract all occurrences in the second schedule from the occurrences in the first schedule (like a SQL MINUS operation).
• Finally, the INTERSECT directive tells the Scheduler to gather only the occurrences that two schedules have in common.

Since I have already established a standard holiday calendar, I can use it to my advantage to accomplish my first requirement: running a job only on standard business days, but excluding holidays. Listing 1.3 shows two variations of this concept with the creation of the ThursdaysOnlyBatchProcessing and SpecialEndOfMonth schedule objects, both of which use the EXCLUDE directive to remove any occurrence of a standard holiday from each schedule.

BYSETPOS, PERIODS, and BYPERIOD

Three other intriguing Scheduler parameters give me extreme flexibility in choosing one or more specific values from a list of occurrences based on the specified relative position in the resulting set of occurrences:

• The BYSETPOS directive lets me pick the nth occurrence from either end of the result set. For example, if a positive value like +3 is specified for BYSETPOS, then the Scheduler picks the 3^rd occurrence starting from the beginning of the result set. However, if a negative value is specified for BYSETPOS, then the Scheduler starts at the end of the result set and works its way backwards until it finds the nth to the last occurrence.
• PERIODS and BYPERIOD work in concert to classify and then select from a resulting set of occurrences. The PERIODS directive is used to define a specific number of elements in a one-dimensional array - for example, twelve occurrences that represent the month-ending dates for each month in a year - and then the BYPERIOD directive can be applied against that array to choose only certain elements.

Listing 1.4 shows three examples of how to utilize these directives in concert with the other new Oracle 10gR2 Scheduler features:

• The first example creates a new Schedule object named SpecialClosingPeriod that will choose the second-to-last date of the month, providing that the occurrence is either a Tuesday, Thursday, or Saturday.
• The second example takes advantage of the PERIODS directive to create a new Schedule object, StandardEndOfMonth, that will include just the last day of each month (excluding standard holidays, of course).
• The last example creates a new Schedule object, PeriodicalEndOfMonth, that uses the BYPERIOD directive to select just the third, sixth, ninth, and twelfth occurrences of the periods defined in the StandardEndOfMonth Schedule object.

Confirming A Schedule Using EVALUATE_CALENDAR_STRING

Oracle 10g provides an excellent tool for validating the expected dates and times at which I expect my Schedule object to start: the EVALUATE_CALENDAR_STRING function in the DBMS_SCHEDULER package. I have provided a sample implementation of this function in Listing 1.5, as well as a unit-testing script that displays a sufficient number of iterations of each Schedule to prove that it is firing at the expected dates and times. I have also provided a sample of the output from this unit testing script.

Scheduling Event-Based Jobs

As interesting as these new scheduling features and methods are, Oracle 10gR2 has added an even more powerful feature to control when a job will start: event-based scheduling. I can now trigger a job to start whenever an event is raised inside a public or private event queue.

The concept of event queues has been around for some time in Oracle, but many Oracle DBAs have not worked with them extensively, so a brief introduction is in order. The Oracle database server utilizes a public event queue, SYS.ALERT_QUE, to publish information about various database events so that special agents called subscribers can be notified of the events. For example, whenever either a warning or critical threshold that was previously set up via the DBMS_SERVER_ALERT package is violated, Oracle publishes information about that violation event to SYS.ALERT_QUE. Through the public agent named SYS.AQ$AGENT, a subscriber can read information from that queue and may either leave the information intact in the queue for other subscribers to view, or remove the information from the queue once it has been read (a process known as dequeuing that information from the queue).

Scheduling a Simple Event-Triggered Job

To illustrate how easy it is to set up an event-triggered Scheduler job, I will first register two Scheduler objects - a Scheduler job object named AUX_EVENT_MONITOR_JOB and a Scheduler schedule object named AUX_EVENT_MONITOR_SCH -- so that they can subscribe to the SYS.ALERT_QUE queue via the SYS.AQ$AGENT agent. I will also set up a new table, SYS.AUX_EVENT_LOGGING, and a new stored procedure, SYS.AUX_EVENT_MONITOR, that will be used to dequeue the event message and then store it in the new table. See Listing 1.6 for the code that sets up the subscriptions to the queue and these new objects.

Next, I will create three new Scheduler objects: schedule AUX_EVENT_MONITOR_SCH, program AUX_EVENT_MONITOR_PGM, and job AUX_EVENT_MONITOR_JOB. The new Scheduler schedule object uses the new 10gR2 CREATE_EVENT_SCHEDULE procedure of DBMS_SCHEDULER so that the schedule will be triggered whenever any new message is queued in the SYS.ALERT_QUE event queue. Note that I can also set up a specific event condition with this procedure so that only specific events trigger the schedule. (More about this in the next article in this series.) The new program object simply calls the SYS.AUX_EVENT_MONITOR stored procedure, and the new job object ties the program and schedule objects together so that whenever a new event is queued, that stored procedure will execute. Listing 1.7 shows the code to create these three new Scheduler objects.

To demonstrate how a newly-queued event triggers this job, I will use Oracle 10g's built-in capabilities to capture threshold violations for two different situations: excessive user commits per second, and excessive blocking / blocked user sessions. In Listing 1.8 I have set rather low warning and critical thresholds for user commits (2 and 4 commits per second respectively) and blocked/blocking users (1 and 2 blocked users, respectively).

To prove out this example, I have built a small sample table as the target of an anonymous PL/SQL block that performs numerous UPDATE operations over a brief time span. I will also open two other user sessions, update one row in the HR.EMPLOYEES table without committing the change in the first session, and then attempt to update the same row in the second session. Between these two sets of DML operations, Oracle will eventually detect the violation and fire the event-driven schedule, causing information to be dequeued from SYS.ALERT_QUE and written into SYS.AUX_EVENT_LOGGING. Listing 1.9 shows these unit test-code snippets and the resulting rows stored in that table.

Next Steps

In the final article in this series, I will explore how to utilize an even more powerful new Oracle 10gR2 Scheduler object - the job chain - and demonstrate how event-triggered jobs can be used to fire complex sets of rules for performing complex tasks via the Scheduler.

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 10gR2 documentation for the deeper technical details of this article:

B14214-01 Oracle Database New Features Guide

B14229-01 Oracle Streams Concepts and Administration

B14231-01 Oracle Database Administrator's Guide

B14257-01 Oracle Streams Advanced Queuing User's Guide and Reference

B14258-01 PL/SQL Packages and Types Reference

Next

Back to DBAsupport.com