Oracle Window Group Scheduling Oracle Tips by Burleson Consulting

Window Groups

A window group is a collection of related windows, which can be assigned to the schedule_name parameter of a job instead of a schedule object.  It can be created with zero, one or many windows as group members using the create_window_group procedure.

PROCEDURE create_window_group(
  group_name             IN VARCHAR2,
  window_list            IN VARCHAR2 DEFAULT NULL,
  comments               IN VARCHAR2 DEFAULT NULL)

The parameters associated with this procedure and their usage are as follows:

* group_name - A name that uniquely identifies the window group.

* window_list - A comma separated list of windows associated with the window group.

* comments - Free text allowing the user to record additional information.

The following code creates a window group and assigns the two test windows defined in the previous section.

BEGIN
  DBMS_SCHEDULER.create_window_group (
    group_name  => 'test_window_group',
    window_list => 'test_window_1, test_window_2',
    comments    => 'A test window group');
END;
/

Figure 2.15 shows the Create Window Group screen in the OEM 10g DB Control.

Figure 2.15 ? OEM 10g DB Control: Create Window Group

Information about window groups can be displayed using the dba_scheduler_window_groups and dba_scheduler_wingroup_members views.  The following script uses both views to display a summary of window group information.

*
window_groups.sql

prompt
prompt WINDOW GROUPS
prompt --------------

select
   window_group_name,
   enabled,
   number_of_windowS
from
   dba_scheduler_window_groups
;

prompt
prompt WINDOW GROUP MEMBERS
prompt ---------------------

select
   window_group_name,
   window_name
from
   dba_scheduler_wingroup_members
;

The output from the window_groups.sql script shows that the window group was created successfully.

SQL> @window_groups

WINDOW GROUPS
-------------

WINDOW_GROUP_NAME              ENABL NUMBER_OF_WINDOWS
------------------------------ ----- -----------------
MAINTENANCE_WINDOW_GROUP       TRUE                  2
TEST_WINDOW_GROUP              TRUE                  2

2 rows selected.

WINDOW GROUP MEMBERS
--------------------

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
TEST_WINDOW_GROUP              TEST_WINDOW_1
TEST_WINDOW_GROUP              TEST_WINDOW_2

4 rows selected.

Figure 2.16 shows the Scheduler Window Groups screen in the OEM 10g DB Control.

Figure 2.16 ? OEM 10g DB Control: Scheduler Window Groups

Windows can be added and removed from a group using the add_window_group_member and remove_window_group_member procedures, respectively.

PROCEDURE add_window_group_member (
  group_name             IN VARCHAR2,
  window_list            IN VARCHAR2)

PROCEDURE remove_window_group_member (
  group_name             IN VARCHAR2,
  window_list            IN VARCHAR2)

The parameters associated with these procedures and their usage are as follows:

* group_name - A name that uniquely identifies the window group.

* window_list - A comma separated list of windows to be added or removed from the window group.

The following example creates a new window, adds it to a group and then removes it from the group.

Figure 2.17 shows the Edit Window Group screen in the OEM 10g DB Control.  Windows can be added and removed from a window group using this screen.

Figure 2.17 ? OEM 10g DB Control: Edit Window Group

Windows groups are removed using the drop_window_group procedure.

PROCEDURE drop_window_group (
  group_name             IN VARCHAR2,
  force                  IN BOOLEAN DEFAULT FALSE)

The parameters associated with this procedure and their usage are as follows.

* group_name - A name that uniquely identifies the window group.

* force - When set to FALSE, an error is produced if any jobs reference the specified window group.  When set to TRUE, any dependant jobs are disabled.

The following example shows how to drop a window group:

BEGIN
  DBMS_SCHEDULER.drop_window_group (
    group_name => 'test_window_group',
    force      => TRUE);
END;
/

The output from the window_groups.sql script shows that the window group has been removed.

SQL> @window_groups

WINDOW GROUPS
-------------

WINDOW_GROUP_NAME              ENABL NUMBER_OF_WINDOWS
------------------------------ ----- -----------------
MAINTENANCE_WINDOW_GROUP       TRUE                  2

1 row selected.

WINDOW GROUP MEMBERS
--------------------

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW

2 rows selected.

The following section will explain common procedures and functions for managing the scheduler objects that have been created.

Enabling, Disabling and Setting Attributes of Scheduler Objects

All applicable scheduler objects can be enabled and disabled using the enable and disable procedures respectively.

PROCEDURE disable(
  name                   IN VARCHAR2,
  force                  IN BOOLEAN DEFAULT FALSE)

PROCEDURE enable(
  name                   IN VARCHAR2)

The parameters associated with these procedures and their usage are as follows:

* name - A name that uniquely identifies the scheduler object: program; job; window; or window group.

* force - When set to FALSE, an error is produced if the object has any dependants.  When set to TRUE, the object is disabled, but any dependents remain unaltered.

The following example shows their usage:

BEGIN
  -- Enable programs and jobs.
  DBMS_SCHEDULER.enable (
    name  => 'test_stored_procedure_prog');
 

  DBMS_SCHEDULER.enable (
    name  => 'test_full_job_definition');

  -- Disable programs and jobs.
  DBMS_SCHEDULER.disable (
    name  => 'test_stored_procedure_prog',
    force => TRUE);

  DBMS_SCHEDULER.disable (
    name  => 'test_full_job_definition',
    force => TRUE);
END;
/

The values for individual attributes of all scheduler objects can be altered using one of the set_attribute overloads.

BEGIN
  DBMS_SCHEDULER.set_attribute (
    name      => 'test_hourly_schedule',
    attribute => 'repeat_interval',
    value     => 'freq=hourly; byminute=30');
END;
/

Attribute values are set to NULL using the set_attribute_null procedures.

BEGIN
  DBMS_SCHEDULER.set_attribute_null (
    name      => 'test_hourly_schedule',
    attribute => 'repeat_interval');
END;
/

Since the creation and maintenance of the scheduler objects has been explained in the previous section, discovering how to identify which dictionary views are available to view information about them will be presented in the following section.

Data Dictionary Views Related to dbms_scheduler

The main views relating to the dbms_scheduler package and their comments can be displayed using the table_comments.sql script as follows:

SQL> @table_comments sys dba_scheduler

TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------------
DBA_SCHEDULER_GLOBAL_ATTRIBUTE All scheduler global attributes
DBA_SCHEDULER_JOBS             All scheduler jobs in the database
DBA_SCHEDULER_JOB_ARGS         All arguments with set values of all                                                scheduler jobs in the database

DBA_SCHEDULER_JOB_CLASSES      All scheduler classes in the database
DBA_SCHEDULER_JOB_LOG          Logged information for all scheduler jobs

DBA_SCHEDULER_JOB_RUN_DETAILS  The details of a job run
DBA_SCHEDULER_PROGRAMS         All scheduler programs in the database
DBA_SCHEDULER_PROGRAM_ARGS     All arguments of all scheduler programs in the database

DBA_SCHEDULER_RUNNING_JOBS
DBA_SCHEDULER_SCHEDULES        All schedules in the database
 

DBA_SCHEDULER_WINDOWS          All scheduler windows in the database
DBA_SCHEDULER_WINDOW_DETAILS   The details of a window
DBA_SCHEDULER_WINDOW_GROUPS    All scheduler window groups in the database

DBA_SCHEDULER_WINDOW_LOG       Logged information for all scheduler windows

DBA_SCHEDULER_WINGROUP_MEMBERS Members of all scheduler window groups in the database

15 rows selected.

The columns and associated comments for each view can be displayed using the column_comments.sql script.  Below is an example of how it is used.

SQL> @column_comments sys dba_scheduler_window_groups

COLUMN_NAME          COMMENTS
-------------------- -----------------------------------------------
COMMENTS             An optional comment about this window group
ENABLED              Whether the window group is enabled
NUMBER_OF_WINDOWS    Number of members in this window group
WINDOW_GROUP_NAME    Name of the window group

4 rows selected.

This script can be used against the other scheduler views to give some indication of their usage.

For further examples of using the scheduler views, refer to Chapter 5.

Windows define the times when resource plans are active.  Since job classes point to resource consumer groups, and therefore resource plans, this mechanism allows control over the resources allocated to job classes and their associated jobs during specific time periods.  A window can be assigned to the schedule_name parameter of a job instead of a schedule object.

Only one window can be active at any time, with one resource plan assigned to the window.  The effects of resource plan switches are instantly visible to running jobs that are assigned to job classes.

A window can be created using the create_window procedure with a predefined or an inline schedule.

PROCEDURE create_window (
  window_name             IN VARCHAR2,
  resource_plan           IN VARCHAR2,
  schedule_name           IN VARCHAR2,
  duration                IN INTERVAL DAY TO SECOND,
  window_priority         IN VARCHAR2                 DEFAULT 'LOW',
  comments                IN VARCHAR2                 DEFAULT NULL)

PROCEDURE create_window (
  window_name             IN VARCHAR2,
  resource_plan           IN VARCHAR2,
  start_date              IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  repeat_interval         IN VARCHAR2,
  end_date                IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  duration                IN INTERVAL DAY TO SECOND,
  window_priority         IN VARCHAR2                 DEFAULT 'LOW',
  comments                IN VARCHAR2                 DEFAULT NULL)

The parameters associated with these procedures and their usage are as follows:

* window_name - A name that uniquely identifies the window.

* resource_plan - The resource plan associated with the window.  When the window opens, the system switches to use the associated resource plan.  When the window closes, the system switches back to the previous resource plan.

* schedule_name - The name of the schedule associated with the window.  If this is specified, the start_date, repeat_interval and end_date must be NULL.

* start_date - The date when this window will take effect.  This may be in the future if the window is to be setup in advance.

* repeat_interval - The definition of how often the window should open.  A value of NULL indicates that the window should only open once.

* end_date - The date when this window will stop.  This combined with the start_date parameter enables a window to be scheduled for a finite period of time.

* duration - The length of time in minutes the window should remain open.

* window_priority - The priority (LOW or HIGH) of the window.  In the event of multiple windows opening at the same time, windows with a high priority take precedence over windows with a low priority, which is the default.

* comments - Free text allowing the user to record additional information.

The following code shows how the create_window procedures can be used:

BEGIN
  -- Window with a predefined schedule.
  DBMS_SCHEDULER.create_window (
    window_name     => 'test_window_1',
    resource_plan   => NULL,
    schedule_name   => 'TEST_HOURLY_SCHEDULE',
    duration        => INTERVAL '30' MINUTE,
    window_priority => 'LOW',
    comments        => 'Window with a predefined schedule.');
END;
/

BEGIN
  -- Window with an inline schedule.
  DBMS_SCHEDULER.create_window (
    window_name     => 'test_window_2',
    resource_plan   => NULL,
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    duration        => INTERVAL '30' MINUTE,
    window_priority => 'LOW',
    comments        => 'Window with an inline schedule.');
END;
/

The SYS user is the owner of all windows, so any schedules referenced by them must also be owned by SYS.

Figure 2.13 shows the Create Window screen in the OEM 10g DB Control.

Figure 2.13 ? OEM 10g DB Control: Create Window

Information about windows can be displayed using the dba_scheduler_windows view. The following script uses this view:

* windows.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

select
   window_name,
   resource_plan,
   enabled,
   active
from
   dba_scheduler_windows
;

The output from the windows.sql script is displayed below.

job_user@db10g> @windows

WINDOW_NAME                    RESOURCE_PLAN           ENABL ACTIV
------------------------------ ----------------------- ----- -----
TEST_WINDOW_1                                          TRUE  FALSE
TEST_WINDOW_2                                          TRUE  FALSE
WEEKEND_WINDOW                                         TRUE  TRUE
WEEKNIGHT_WINDOW                                       TRUE  FALSE

4 rows selected.

Figure 2.14 shows the Scheduler Windows screen in the OEM 10g DB Control.

Figure 2.14 ? OEM 10g DB Control: Scheduler Windows

The server normally controls the opening and closing of windows, but they also can be opened and closed manually using the open_window and close_window procedures.

PROCEDURE open_window (
  window_name             IN VARCHAR2,
  duration                IN INTERVAL DAY TO SECOND,
  force                   IN BOOLEAN DEFAULT FALSE)

PROCEDURE close_window (
  window_name             IN VARCHAR2)

The parameters associated with these procedures and their usage are as follows:

* window_name - A name that uniquely identifies the window.

* duration - The length of time, in minutes, the window should remain open.

* force - When set to FALSE, attempting to open a window when one is already open will result in an error unless the currently open window is the one that is attempting to open.  In this case, the close time is set to the current system time plus the specified duration.

Closing a window causes all jobs associated with that window to be stopped.

The following example opens then closes test_window_2.  Notice how the active window switches back to weekend_window when test_window_2 is closed.

BEGIN
  -- Open window.
  DBMS_SCHEDULER.open_window (
   window_name => 'test_window_2',
   duration    => INTERVAL '1' MINUTE,
   force       => TRUE);
END;
/

SQL> @windows

WINDOW_NAME                    RESOURCE_PLAN        ENABL ACTIV
------------------------------ -------------------- ----- -----
TEST_WINDOW_1                                       TRUE  FALSE
TEST_WINDOW_2                                       TRUE  TRUE
WEEKEND_WINDOW                                      TRUE  FALSE
WEEKNIGHT_WINDOW                                    TRUE  FALSE

4 rows selected.

BEGIN
  -- Close window.
  DBMS_SCHEDULER.close_window (
   window_name => 'test_window_2');
END;
/

SQL> @windows

WINDOW_NAME                    RESOURCE_PLAN        ENABL ACTIV
------------------------------ -------------------- ----- -----
TEST_WINDOW_1                                       TRUE  FALSE
TEST_WINDOW_2                                       TRUE  FALSE
WEEKEND_WINDOW                                      TRUE  TRUE
WEEKNIGHT_WINDOW                                    TRUE  FALSE

4 rows selected.

Windows can be removed using the drop_window procedure.

PROCEDURE drop_window (
  window_name             IN VARCHAR2,
  force                   IN BOOLEAN DEFAULT FALSE)

The parameters associated with this procedure and their usage are as follows:

* window_name - A single or comma separated list of window names.  If a window group name is specified, all windows within that group will be dropped.  In addition, all jobs that use the specified window or window group as a schedule will be disabled, although running jobs will complete normally.

* force - When set to FALSE, attempting to drop an open window will result in an error.  When set to TRUE the open window is closed before it is dropped.

The following example drops the two test windows that were created earlier:

BEGIN
  DBMS_SCHEDULER.drop_window (
    window_name => 'test_window_1,test_window_2',
    force       => TRUE);
END;
/

The output from the windows.sql script confirms that the windows have been removed successfully.

SQL> @windows

WINDOW_NAME              RESOURCE_PLAN     ENABL ACTIV
------------------------ ----------------- ----- -----
WEEKEND_WINDOW                             TRUE  TRUE
WEEKNIGHT_WINDOW                           TRUE  FALSE

2 rows selected.

The following section will show how to group related windows together using window groups.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.