Section 8.6. Managing Windows | Oracle PL(s)SQL For DBAs

8.6. Managing Windows

Windows 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:

A schedule, which defines the starting time of the window
A time duration of the window, indicating how long it is open
A resource allocation plan that is applied to a job attached to the window

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 Window

You 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:

It starts each day at 3:00 A.M.
It is 14 hours long.
It uses the resource plan OLTP_PLAN.

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.

Line	Description
2	Name of the window.
3	Name of the resource manager plan, OLTP_PLAN, which was previously created.
4	Name of the schedule, which was previously created. It starts at 3:00 A.M. every day.
58	Duration of this window. The duration parameter is specified in the datatype INTERVAL DAY TO SECOND. I must specify the 14-hour period in the manner shown.
9	Comment for the window.

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.

Windows can be created by any user with the MANAGE SCHEDULER system privilege . However, windows are not owned by that user. Technically, the window objects are owned by SYS.

8.6.2. Prioritizing Windows

Because 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:

Window W1 starts at 7 A.M. and ends at 9 P.M.
Window W2 starts at 7 P.M. and ends at 7 A.M.

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 Windows

DBMS_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 Windows

You can obtain information about windows in the database from the view DBA_SCHEDULER_WINDOWS. Here are the important columns of the view.

Column name	Description
WINDOW_NAME	Name of the window.
RESOURCE_PLAN	Name of the Resource Manager plan associated with the window.
SCHEDULE_OWNER	If the window has a named schedule, this is the name of the owner of the schedule.
SCHEDULE_NAME	Name of the schedule, if any.
START_DATE	Timestamp when the window will open. This is effective only when the window's calendar properties are provided inline, not via a schedule. Shows the date in the TIMESTAMP(6) WITH TIME ZONE datatype.
REPEAT_INTERVAL	If the window has an inline schedule (calendar string, as opposed to a named schedule), this is the calendar string.
END_DATE	When a window has an inline schedule (calendar string, as opposed to a named schedule), this specifies the timestamp when the window will close permanently. Similar to start_date, this column shows the date in the TIMESTAMP(6) WITH TIME ZONE datatype.
DURATION	How long the window is open. Shown in the DURATION datatype .
WINDOW_PRIORITY	When two windows overlap, the one with the higher priority will be opened, and the other one will be closed. The priorities are shown as HIGH and LOW.
NEXT_START_DATE	Next timestamp the window is scheduled to open. Shows the date in the datatype TIMESTAMP(6) WITH TIME ZONE.
LAST_START_DATE	Timestamp of the last time the window was opened. Shows the date in the datatype TIMESTAMP(6) WITH TIME ZONE.
ENABLED	Indicates whether a window is enabled; may be TRUE or FALSE.
ACTIVE	Indicates whether a window is open now; may be TRUE or FALSE.
COMMENTS	Comments about the window.

8.6.5. Dropping Windows

When 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 Windows

Instead 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 Closed

Let'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 Groups

Let's suppose that I have defined three different windows:

MORNING: 1:00 A.M. through 9:00 A.M. Most of the management jobs, such as statistics collection, etc., run during this window.
WORKDAY: 9:00 A.M. through 5:00 P.M. The entire regular database OLTP processing occurs during this window. No management or batch jobs are allowed to run.
EVENING: 5:00 P.M. through 1:00 A.M. Most of the batch jobs and ETL processing occur during this window.

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.