8.6. Managing WindowsWindows are defined durations within which a specified job runs. Using windows, you can control how resources (e.g., CPU, parallel query servers, undo pool size) are allocated and governed. When a job runs, the resource allocation method specified by the window active at that time is in effect, and it controls the resource allocation for that job. A window has three distinct components:
The first component may be specified using either a calendar string or a named schedule, as I described earlier for jobs. The third component may be specified via a resource manager plan, as described for programs in the previous section. In the following sections, I'll explain the second component, the time duration of windows. 8.6.1. Creating a WindowYou can create a window using the CREATE_WINDOW procedure in the DBMS_SCHEDULER package. Suppose that I want to create a window called LOW_LOAD, which has the following characteristics:
I can create this window as follows. 1 BEGIN 2 DBMS_SCHEDULER.create_window (window_name => 'low_load', 3 resource_plan => 'OLTP_PLAN', 4 schedule_name => 'SCHED_MANAGER.EVERY_DAY', 5 DURATION => NUMTODSINTERVAL 6 (840, 7 'minute' 8 ), 9 comments => 'This is a low load window' 10 ); 11 END; Let's examine the lines in detail in the following table.
Once this window has been created, I can create a job that uses this window. When I run the CREATE_JOB procedure, I can specify the window name in the schedule_name parameter, as shown below. Earlier, I described how to set this parameter to define a schedule. Here, the same parameter I used to specify a window named LOW_LOAD. BEGIN DBMS_SCHEDULER.create_job (job_name => 'Calculate_Daily_Interest', program_name => 'INTEREST_ADMIN.CALC_INTEREST', schedule_name => 'SYS.LOW_LOAD', comments => 'Daily Interest Calculation', enabled => TRUE ); END; Because windows are always owned by SYS, I qualify the window name with "SYS". Because the job was assigned this window, its resource allocation method is governed by the resource allocation scheme for the window. When a window is supposed to become active, it is said to be opened; subsequently, when it becomes inactive, it is said to be closed. So a window opens when the time comes for it to be opened based on the calendar defined or the schedule associated with the window. When a window closes, what happens to the jobs that are running at that time? There are two possibilities: you could let them run as usual or just stop them. Sometimes it is important to stop a job when the window closes. For example, you may have a job that collects optimizer statistics for tables. This job usually runs during the nightperhaps until 6:00 A.M. Suppose that on one particular day it does not finish at 6:00 A.M. and instead continues on until 8:00 A.M. By that time it might be affecting regular database processing. This is a case where you may want to stop the job before completion when its associated window closes. This property of the window is controlled by the CREATE_JOB parameter stop_on_window_close. If it is set to TRUE, the specified job is stopped when the window closes.
8.6.2. Prioritizing WindowsBecause windows are defined according to a time scale, only one window is active at any point in time. Suppose that you have defined two windows as follows:
These two windows overlap between 7 P.M. and 9 P.M. During that time, which window will be active? The Scheduler resolves this situation according to the priority you have assigned to each window. You specify the priority in the window_priority parameter of the CREATE_WINDOW procedure. The only two possible values are "low" (the default) and "high." For example: window_priority => 'high' When windows overlap, the one with the "high" priority will be activated. If two windows with the same priority overlap, then the window that is going to end sooner is given precedence, and the other window waits. 8.6.3. Specifying an End Date for WindowsDBMS_SCHEDULER allows you to specify a date when a window will end. What does that mean? Suppose that your accounting database receives data from an outside source. You discover that the outside source has been compromised and that the data coming from there is not accurate. You decide to code a stored procedure that will fix the data in the database, and use a schedule to execute this procedure every day. However, the source for this fix is available to you only until August 2nd, and today is July 5th. After August 2nd, you want this job to end. You defined a window in the first place so that this job will not consume all your resources. Because the issue will be resolved by August 2nd, you want the window to have a definite lifespan. To effectively end this window on that day, and thus conserve resources, you could specify an end date when you create the window. You do this with the end_date parameter of the CREATE_WINDOW procedure . 8.6.4. Getting Information About WindowsYou can obtain information about windows in the database from the view DBA_SCHEDULER_WINDOWS. Here are the important columns of the view.
8.6.5. Dropping WindowsWhen you no longer need a window, you can drop it with the DROP_WINDOW procedure . If you attempt to drop a window and any jobs are currently using this window as their schedules, you will not be able to drop the window normally. Instead, you will have to use the FORCE option as follows. BEGIN DBMS_SCHEDULER.drop_window (window_name => 'window1', FORCE => TRUE); END; If FORCE is specified for a window, that window will be dropped regardless of its association with any job. In this example, if there are any jobs using the window named window1 as their schedule, they will be disabled, and the window will be dropped. 8.6.6. Disabling and Enabling WindowsInstead of dropping a window, you may want to disable it so that it will never open automatically. To disable a window, use the procedure DISABLE, as shown here. BEGIN DBMS_SCHEDULER.disable (NAME => 'window1'); END; If the window named window1 is currently open, this operation will fail. To forcibly disable a window, you must use the FORCE parameter as follows: BEGIN DBMS_SCHEDULER.disable (NAME => 'window1', FORCE=>TRUE); END; This will not close the currently open window, but it will force the window not to open in the future. Similarly, to enable a disabled window, use the ENABLE procedure : BEGIN DBMS_SCHEDULER.enable (NAME => 'window1'); END; Note that the ENABLE and DISABLE procedures shown here are the same ENABLE/DISABLE procedures used for other Scheduler objects, such as jobs, schedules, and programs. 8.6.7. Forcing Windows Open and ClosedLet's suppose that you have defined a window for regular OLTP activities named OLTP_WINDOW, starting at 9:00 A.M. Today, uncharacteristically, the processing started earlyperhaps at 7:00 A.M. You have defined the resource manager group associated with the window, and you want this job to be under the resource plan, if possible. But the window is not scheduled to be open until two hours from now. What can you do? In a case like this, you can force a window to open, using the procedure OPEN_WINDOW, as shown here: BEGIN DBMS_SCHEDULER.open_window ( window_name => 'OLTP_WINDOW', DURATION => NULL); END; This immediately opens the specified window, and any jobs using this window can immediately use the resource plan. In this example, the duration parameter is specified as NULL, which means that the normal duration of the window will be followed. Suppose the normal duration, specified when the window was created, is eight hours, ending at 5:00 P.M. Because the window was manually opened at 7:00 A.M., it will automatically close eight hours after that, at 3:00 P.M. That may not be acceptable to you: you may want to keep the window open until 5:00 P.M. anyway. In that case, you can open the window with a specified duration, as shown below; the duration must be in the INTERVAL datatype . BEGIN dbms_scheduler.open_window ( window_name => 'OLTP_WINDOW', DURATION => NUMTODSINTERVAL (600, 'minute') ); END; 8.6.8. Window GroupsLet's suppose that I have defined three different windows:
However, some jobs have to run at all times, regardless of whatever window is active at that timefor example, jobs that monitor performance. I can't define another window because at any point in time, only one window is active. So what can I do? I can define a window group, which is a collection of windows. When I create a job, I can specify this window group as a schedule, and it will inherit the properties of the windows. I create a window group using the CREATE_WINDOW_GROUP procedure . For example, to create a window group named ALL_DAY as a collection of the three windows listed above, I issue the following code segment: BEGIN DBMS_SCHEDULER.create_window_group ( group_name => 'all_day', window_list => 'MORNING, WORKDAY, EVENING', comments => 'All day window group' ); END; I can specify the names of all of the windows in the window group as a comma-separated list in the window_list parameter. I can also create a window group with no windows assigned to it yet. The parameter will be NULL in that case. This is useful when a window group has just been created and you have not decided which windows should be applied. When I create a job, I can specify the name of the window group in the CREATE_JOB schedule_name parameter as shown here: BEGIN DBMS_SCHEDULER.create_job (job_name => 'DB_Monitor', program_name => 'INTEREST_ADMIN.CALC_INTEREST', schedule_name => 'SYS.ALL_DAY', comments => 'DB Monitor', enabled => TRUE ); END; After I define a window group, I can add windows to it and remove windows from it. To remove the window MORNING from the window group ALL_DAY, I'll use the REMOVE_WINDOW_GROUP_MEMBER procedure as follows: BEGIN DBMS_SCHEDULER.remove_window_group_member (group_name => 'all_day', window_list => 'MORNING' ); END; To add it later, I will use the ADD_WINDOW_GROUP_MEMBER procedure : BEGIN DBMS_SCHEDULER.add_window_group_member (group_name => 'all_day', window_list => 'MORNING' ); END; When I want to remove a window group, I can use the DROP_WINDOW_GROUP procedure as shown here: BEGIN DBMS_SCHEDULER.drop_window_group (group_name => 'all_day'); END; If the window group has windows defined under it, then it cannot be dropped normally. In this example, the window group ALL_DAY has three windows: MORNING, WORKDAY, and EVENING. To drop the window group, use the FORCE parameter: BEGIN DBMS_SCHEDULER.drop_window_group (group_name => 'all_day', FORCE => TRUE); END; Note, however, that this merely drops the window group, not the windows defined under it; they remain intact. If there are any jobs running that use this window group, the jobs are disabled. They can, however, be manually restarted. |