Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Oct 28, 2004

The Oracle 10g Scheduler, Part 3: Advanced Features - Page 2

By Jim Czuprynski

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM