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'
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.
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.
Conclusion
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
» 
Comment and Contribute
