12.8. Using Scheduler Windows
In Chapter 11, "Managing Resources," you learned to create and manage resource plans to allocate system resources. Scheduler windows allow you to change the active resource plan based on defined schedules. In general, resource plans tend to be created with specific time windows in mind. For instance, assume that your system performs heavy transaction processing between the hours of 8:00 A.M. and 5:00 P.M. but runs mostly batch processing and reports after hours. It would make sense to create a separate resource plan to govern resource allocation for each time period. Scheduler windows can then be used to switch automatically between the two.
Unlike most of the other Scheduler objects that you've seen so far, windows are created in the SYS schema. They are stored as database objects and therefore must have a valid name that is unique within the SYS schema.
In the following sections, you will learn to create, open, and close scheduler windows. You'll also learn about scheduler window logging and how to manage window logs. Lastly, you'll learn about purging scheduler logs.
12.8.1. Creating Windows
Windows can be created by using the DBMS_SCHEDULER.CREATE_WINDOW procedure. When creating a window, you have the choice of either using an existing schedule or defining an inline schedule. However, an existing schedule may not be used if the schedule has a repeat interval based on a PL/SQL expression.
The parameters for the CREATE_WINDOW procedure are described here:
WINDOW_NAME The WINDOW_NAME parameter uniquely identifies the window in the SYS schema. The name has to be unique in the SYS schema.
RESOURCE_PLAN The RESOURCE_PLAN parameter specifies the name of the resource plan that will govern the timeframe of the window. When the window opens, the system switches to the specified resource plan. When the window closes, the system either switches back to the prior resource plan or, if another window is opening, to the resource plan of the new window. If the current resource plan has been set through the use of the ALTER SYSTEM SET RESOURCE_MANAGER_PLAN FORCE statement, the Scheduler will not be allowed to change the resource plan. If no resource plan is defined for the window, the current resource plan will remain in effect when the window opens and will stay in effect for the duration of the window.
START_DATE The START_DATE parameter specifies the first date that the window is scheduled to open. If START_DATE is NULL or references a date in the past, the window will open as soon as it is created. The START_DATE parameter is used as a reference date when the REPEAT_INTERVAL parameter uses a calendaring expression. In this situation, the window will open on the first date that matches the calendaring expression and is on or after the date specified in the START_DATE parameter.
DURATION The DURATION attribute specifies how long the window will remain open. There is no default value, so a value must be provided. The value should be specified as an INTERVAL DAY TO SECOND datatype (for example, interval '10' hour or interval '20' minute).
SCHEDULE_NAME The SCHEDULE_NAME parameter specifies the name of the schedule associated with the window.
REPEAT_INTERVAL The REPEAT_INTERVAL parameter specifies how often the window should repeat. It is defined using the calendaring syntax only; PL/SQL expressions cannot be used in conjunction with a window. If the REPEAT_INTERVAL parameter is NULL, the window will open only once at the specified start date.
END_DATE The END_DATE parameter specifies the date when the window will be disabled. If the END_DATE parameter is NULL, a repeating window will repeat forever.
WINDOW_PRIORITY The WINDOW_PRIORITY parameter is relevant only when two windows overlap each other. Because only one window can be in effect at a time, the window priority determines which window will be opened. The valid values are LOW (the default) and HIGH. A high priority window has precedence.
COMMENTS The COMMENTS parameter specifies an optional comment about the window.
To create a window that activates the DAY_PLAN resource plan and uses a schedule named WORK_HOURS_SCHEDULE, see the following example:
SQL> begin 2 dbms_scheduler.create_window ( 3 window_name => 'WORK_HOURS_WINDOW', 4 resource_plan => 'DAY_PLAN', 5 schedule_name => 'WORK_HOURS_SCHEDULE', 6 duration => INTERVAL '10' HOUR, 7 window_priority => 'HIGH'), 8 end; SQL> / PL/SQL procedure successfully completed.
This newly created window will be started based on a schedule named WORK_HOURS_SCHEDULE and will remain in effect for 10 hours. During those 10 hours, the DAY_PLAN resource plan will be in effect. Also, because the priority for this window is set to HIGH, it will take precedence over any overlapping window that has a priority setting of LOW.
12.8.2. Opening and Closing Windows
There are two distinct ways that a window can be opened. The first is based on the window's schedule. The second is they can be opened manually by using the DBMS_SCHEDULER.OPEN_WINDOW procedure.
The OPEN_WINDOW procedure opens a window independent of its schedule. The associated resource plan is enabled immediately, and currently executing jobs are subjected to the change in resource plan, just as if the window had opened based on its schedule.
When opening a window manually, you can specify a new duration for the window to remain open; otherwise it will remain open for the duration defined when the window was created.
If the FORCE parameter is set to TRUE in the OPEN_WINDOW procedure, the Scheduler will automatically close any currently open window, even if it has a higher priority. Also, it will not allow any other windows to be opened for the duration of the manually opened window.
The OPEN_WINDOW procedure accepts only three parameters: WINDOW_NAME, DURATION, and FORCE. An example of its usage is as follows:
SQL> begin 2 dbms_scheduler.open_window ( 3 window_name => 'WORK_HOURS_WINDOW', 4 duration => INTERVAL '20' MINUTE, 5 force => TRUE); 6 end; SQL> / PL/SQL procedure successfully completed.
This example forces the WORK_HOURS_WINDOW to be opened and any current window to close. The new window will remain open for a duration of 20 minutes.
In a similar manner, windows can be manually closed by using the DBMS_SCHEDULER.CLOSE_WINDOW procedure. This procedure accepts the window name as a parameter, as shown here:
SQL> begin 2 dbms_scheduler.close_window ( 3 window_name => 'WORK_HOURS_WINDOW'), 4 end; SQL> / PL/SQL procedure successfully completed.
12.8.3. Window Logging
The Oracle Scheduler maintains window logs of all window activities. The DBA_SCHEDULER_WINDOW_LOG view can be used to view log entries for all of the following window activities:
Creating a new window
Dropping a window
Opening a window
Closing a window
Overlapping windows
Disabling a window
Enabling a window
For example, use the following query to view window log entries:
SQL> select log_id, trunc(log_date) log_date,
window_name, operation
from dba_scheduler_window_log;
LOG_ID LOG_DATE WINDOW_NAME OPERATION
------ --------- -------------------- ---------
527 25-SEP-04 WEEKEND_WINDOW OPEN
544 28-SEP-04 WEEKNIGHT_WINDOW OPEN
547 28-SEP-04 WEEKNIGHT_WINDOW CLOSE
548 29-SEP-04 WEEKNIGHT_WINDOW OPEN
551 29-SEP-04 WEEKNIGHT_WINDOW CLOSE
552 30-SEP-04 WEEKNIGHT_WINDOW OPEN
559 01-OCT-04 WEEKNIGHT_WINDOW CLOSE
560 02-OCT-04 WEEKNIGHT_WINDOW OPEN
563 02-OCT-04 WEEKNIGHT_WINDOW CLOSE
555 30-SEP-04 WEEKNIGHT_WINDOW CLOSE
564 02-OCT-04 WEEKEND_WINDOW OPENFor each CLOSE operation logged in the DBA_SCHEDULER_WINDOW_LOG view, there will be an associated record in the DBA_SCHEDULER_WINDOW_DETAILS view, as shown here:
SQL> select log_id, trunc(log_date) log_date,
window_name, actual_duration
from dba_scheduler_window_details;
LOG_ID LOG_DATE WINDOW_NAME ACTUAL_DURATION
------- ---------- -------------------- ---------------
547 28-SEP-04 WEEKNIGHT_WINDOW +000 08:00:00
551 29-SEP-04 WEEKNIGHT_WINDOW +000 08:00:00
559 01-OCT-04 WEEKNIGHT_WINDOW +000 08:00:00
563 02-OCT-04 WEEKNIGHT_WINDOW +000 08:00:00
555 30-SEP-04 WEEKNIGHT_WINDOW +000 07:59:5812.8.4. Purging Logs
As with any automatic logging system, window logs must be purged on a regular basis to prevent excessive table growth. Oracle provides an automatic method to purge the log files after a specified number of days.
Scheduler job and window logs will be automatically purged based on the setting of the LOG_HISTORY attribute of the Scheduler itself. The value of this parameter determines the number of days that log data should be retained, after which it will be purged. To set this value, use the SET_SCHEDULER_ATTRIBUTE procedure, as in the following example:
SQL> begin 2 DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE( 3 'LOG_HISTORY','60'), 4 end; SQL> / PL/SQL procedure successfully completed.
This example instructs Oracle to automatically purge all records that are over 60 days old.
