| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
Windows provide a way to automatically activate different resource plans at different times. Running jobs can then see a change in the resources that are allocated to them when there is a change in resource plan.
The key attributes of a window are its:
Schedule
This controls when the window is in effect.
Duration
This controls how long the window is open.
Resource plan
This names the resource plan that activates when the window opens.
Only one window can be in effect at any given time. Windows belong to the SYS schema.
All window activity is logged in the *_SCHEDULER_WINDOW_LOG views, otherwise known as the window logs. See "Window Logs" for examples of window logging.
This section introduces you to basic window tasks, and discusses the following topics:
See Also:
"Windows" for an overview of windows.Table 27-5 illustrates common window tasks and the procedures you use to handle them.
Table 27-5 Window Tasks and Their Procedures
| Task | Procedure | Privilege Needed |
|---|---|---|
|
Create a window |
|
|
|
Open a window |
|
|
|
Close a window |
|
|
|
Alter a window |
|
|
|
Drop a window |
|
|
|
Disable a window |
|
|
|
Enable a window |
|
|
See "Scheduler Privileges" for further information regarding privileges.
You can use Enterprise Manager or the DBMS_SCHEDULER.CREATE_WINDOW package procedure to create windows. There is one difference between these methods, other than the fact that one uses PL/SQL, and the other a graphical user interface. When using the package procedure, you can leave the resource_plan parameter NULL. In this case, when the window opens, the current plan remains in effect. See Oracle Database PL/SQL Packages and Types Reference and "Configuring the Scheduler" for more information.
The following are instructions for creating a window with Enterprise Manager:
Access the Database Home page.
At the top of the page, click Server to display the Server page.
In the Oracle Scheduler section, click Windows.
The Scheduler Windows page appears. It displays existing windows.
Click Create.
The Create Window page appears.
Enter a name for the window.
Choose a resource plan from the Resource Plan drop-down list, or create a resource plan.
You can use the default, INTERNAL_PLAN. To view the contents of an existing resource plan, click View Resource Plan. If you choose to create a new resource plan, click Create Resource Plan and follow those steps.
Select a priority, Low or High.
Enter optional comments in the Description field.
Under the Schedule heading, do one of the following:
To create a schedule, select Use a calendar.
To use an existing schedule, select Use an existing schedule. A schedule with its information is displayed. If you want a different schedule, click Select Schedule, in which case the Select Schedule page is displayed. Select one of the schedules listed under the Results heading, or enter the schema and a text string for the schedule name, and then click Go. The schedule with the search string in its name is returned. Select the desired schedule and click Select.
Note:
The Scheduler does not check if there is already a window defined for that schedule. Therefore, this may result in windows that overlap. Also, using a named schedule that has a PL/SQL expression as its repeat interval is not supported for windows.Click OK, and skip the remaining steps in this procedure.
If you want to change the time zone, click the flashlight icon next to the Time Zone field and follow the steps.
Under the Repeating drop-down list, choose how often you want the window to repeat. If you choose a value other than Do Not Repeat, the page changes so that you can enter the interval and enter a starting time.
Under the Start heading, select whether you want the schedule to start Immediately or Later. If you choose Later, enter a date.
Under the Duration heading, enter how long the window is to remain open.
Click OK to save the window.
You alter a window using the SET_ATTRIBUTE procedure or Enterprise Manager. With the exception of WINDOW_NAME, all the attributes of a window can be changed when it is altered. The attributes of a window are available in the *_SCHEDULER_WINDOWS views.
When a window is altered, it does not affect an active window. The changes only take effect the next time the window opens.
All windows can be altered. If you alter a window that is disabled, it will remain disabled after it is altered. An enabled window will be automatically disabled, altered, and then reenabled, if the validity checks performed during the enable process are successful.
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE procedure and "Configuring the Scheduler".
When a window opens, the Scheduler switches to the resource plan that has been associated with it during its creation. If there are jobs running when the window opens, the resources allocated to them might change due to the switch in resource plan.
There are two ways a window can open:
According to the window's schedule
Manually, using the OPEN_WINDOW procedure
This procedure opens the window independent of its schedule. This window will open and the resource plan associated with it will take effect immediately. Only an enabled window can be manually opened.
In the OPEN_WINDOW procedure, you can specify the time interval that the window should be open for, using the duration attribute. The duration is of type interval day to second. If the duration is not specified, then the window will be opened for the regular duration as stored with the window.
Opening a window manually has no impact on regular scheduled runs of the window.
When a window that was manually opened closes, the rules about overlapping windows are applied to determine which other window should be opened at that time if any at all.
You can force a window to open even if there is one already open by setting the force option to TRUE in the OPEN_WINDOW call or Enterprise Manager.
When the force option is set to TRUE, the Scheduler automatically closes any window that is open at that time, even if it has a higher priority. For the duration of this manually opened window, the Scheduler does not open any other scheduled windows even if they have a higher priority. You can open a window that is already open. In this case, the window stays open for the duration specified in the call, from the time the OPEN_WINDOW command was issued.
Consider an example to illustrate this. window1 was created with a duration of four hours. It has how been open for two hours. If at this point you reopen window1 using the OPEN_WINDOW call and do not specify a duration, then window1 will be open for another four hours because it was created with that duration. If you specified a duration of 30 minutes, the window will close in 30 minutes.
When a window opens, an entry is made in the window log.
A window can fail to switch resource plans if the current resource plan has been manually switched using the ALTER SYSTEM statement with the FORCE option, or using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure with the allow_scheduler_plan_switches argument set to FALSE. In this case, the failure to switch resource plans is written to the window log.
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the OPEN_WINDOW procedure and the DBMS_RESOURCE_MANAGER.SWITCH_PLAN procedure.
There are two ways a window can close:
Based on a schedule
A window will close based on the schedule defined at creation time.
Manually, using the CLOSE_WINDOW procedure
The CLOSE_WINDOW procedure will close an open window prematurely.
A closed window means that it is no longer in effect. When a window is closed, the Scheduler will switch the resource plan to the one that was in effect outside the window or in the case of overlapping windows to another window. If you try to close a window that does not exist or is not open, an error is generated.
A job that is running will not close when the window it is running in closes unless the attribute stop_on_window_close was set to TRUE when the job was created. However, the resources allocated to the job may change because the resource plan may change.
When a running job has a window group as its schedule, the job will not be stopped when its window is closed if another window that is also a member of the same window group then becomes active. This is the case even if the job was created with the attribute stop_on_window_close set to TRUE.
When a window is closed, an entry will be added to the window log DBA_SCHEDULER_WINDOW_LOG.
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the CLOSE_WINDOW procedure.
You drop one or more windows using the DROP_WINDOW procedure or Enterprise Manager. When a window is dropped, all metadata about the window is removed from the *_SCHEDULER_WINDOWS views. All references to the window are removed from window groups.
You can drop several windows in one call by providing a comma-delimited list of window names or window group names to the DROP_WINDOW procedure. For example, the following statement drops both windows and window groups:
BEGIN
DBMS_SCHEDULER.DROP_WINDOW ('window1, window2,
window3, windowgroup1, windowgroup2');
END;
/
Note that if a window group name is provided, then the windows in the window group are dropped, but the window group is not dropped. To drop the window group, you must use the DROP_WINDOW_GROUP procedure.
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_WINDOW procedure.
You disable one or more windows using the DISABLE procedure or with Enterprise Manager. This means that the window will not open, however, the metadata of the window is still there, so it can be reenabled. Because the DISABLE procedure is used for several Scheduler objects, when disabling windows, they must be preceded by SYS.
A window can also become disabled for other reasons. For example, a window will become disabled when it is at the end of its schedule. Also, if a window points to a schedule that no longer exists, it becomes disabled.
If there are jobs that have the window as their schedule, you will not be able to disable the window unless you set force to TRUE in the procedure call. By default, force is set to FALSE. When the window is disabled, those jobs that have the window as their schedule will not be disabled.
You can disable several windows in one call by providing a comma-delimited list of window names or window group names to the DISABLE procedure call. For example, the following statement disables both windows and window groups:
BEGIN
DBMS_SCHEDULER.DISABLE ('sys.window1, sys.window2,
sys.window3, sys.windowgroup1, sys.windowgroup2');
END;
/
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DISABLE procedure and Admin for a list of why windows may be disabled.
You enable one or more windows using the ENABLE procedure or Enterprise Manager. An enabled window is one that can be opened. Windows are, by default, created enabled. When a window is enabled using the ENABLE procedure, a validity check is performed and only if this is successful will the window be enabled. When a window is enabled, it is logged in the window log table. Because the ENABLE procedure is used for several Scheduler objects, when enabling windows, they must be preceded by SYS.
You can enable several windows in one call by providing a comma-delimited list of window names. For example, the following statement enables three windows:
BEGIN
DBMS_SCHEDULER.ENABLE ('sys.window1, sys.window2, sys.window3');
END;
/
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the ENABLE procedure.
Although Oracle does not recommend it, windows can overlap. Because only one window can be active at one time, the following rules are used to determine which window will be active when windows overlap:
If windows of the same priority overlap, the window that is active will stay open. However, if the overlap is with a window of higher priority, the lower priority window will close and the window with the higher priority will open. Jobs currently running that had a schedule naming the low priority window may be stopped depending on the behavior you assigned when you created the job.
If at the end of a window there are multiple windows defined, the window with the highest priority will open. If all windows have the same priority, the window that has the highest percentage of time remaining will open.
An open window that is dropped will be automatically closed. At that point, the previous rule applies.
Whenever two windows overlap, an entry is written in the Scheduler log.
Figure 27-1 illustrates a typical example of how windows, resource plans, and priorities might be determined for a 24 hour schedule. In the following two examples, assume that Window1 has been associated with Resource Plan1, Window2 with Resource Plan2, and so on.
Figure 27-1 Windows and Resource Plans (Example 1)
In Figure 27-1, the following occurs:
From 12AM to 4AM
No windows are open, so a default resource plan is in effect.
From 4AM to 6AM
Window1 has been assigned a low priority, but it opens because there are no high priority windows. Therefore, Resource Plan 1 is in effect.
From 6AM to 9AM
Window3 will open because it has a higher priority than Window1, so Resource Plan 3 is in effect.
From 9AM to 11AM
Even though Window1 was closed at 6AM because of a higher priority window opening, at 9AM, this higher priority window is closed and Window1 still has two hours remaining on its original schedule. It will be reopened for these remaining two hours and resource plan will be in effect.
From 11AM to 2PM
A default resource plan is in effect because no windows are open.
From 2PM to 3PM
Window2 will open so Resource Plan 2 is in effect.
From 3PM to 8PM
Window4 is of the same priority as Window2, so it will not interrupt Window2. Therefore, Resource Plan 2 is in effect.
From 8PM to 10PM
Window4 will open so Resource Plan 4 is in effect.
From 10PM to 12AM
A default resource plan is in effect because no windows are open.
Figure 27-2 illustrates another example of how windows, resource plans, and priorities might be determined for a 24 hour schedule.
Figure 27-2 Windows and Resource Plans (Example 2)
In Figure 27-2, the following occurs:
From 12AM to 4AM
A default resource plan is in effect.
From 4AM to 6AM
Window1 has been assigned a low priority, but it opens because there are no high priority windows, so Resource Plan 1 is in effect.
From 6AM to 9AM
Window3 will open because it has a higher priority than Window1. Note that Window6 does not open because another high priority window is already in effect.
From 9AM to 11AM
At 9AM, Window5 or Window1 are the two possibilities. They both have low priorities, so the choice is made based on which has a greater percentage of its duration remaining. Window5 has a larger percentage of time remaining compared to the total duration than Window1. Even if Window1 were to extend to, say, 11:30AM, Window5 would have 2/3 * 100% of its duration remaining, while Window1 would have only 2.5/7 * 100%, which is smaller. Thus, Resource Plan 5 will be in effect.
