The Oracle 10g Scheduler, Part 3: Advanced Features - Page 2
October 28, 2004
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:
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' SQL> SQL> SELECT 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: Open/ 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.
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.
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