You must have seen the basic concepts of Oracle Database scheduler. The basic concepts includes programs, Jobs and schedule. Oracle Database 10g scheduler – Basic post includes these concepts and shows how to create programs, jobs and schedule the same.

This post is regarding the advanced concepts of Oracle 10g scheduler. This includes Job Classes, Window, Window group and profiles. Lets see each of these concepts one by one.

Job Classes

Job class allows DBA to categorize the jobs according to similar resource requirements. For each jobs  to be executed, database needs resource to be allocated to the job. We can define resource group and limit the resource for each resource group. You can check the existing resource group using the below query.

SQL> SELECT consumer_group FROM dba_rsrc_consumer_groups;

CONSUMER_GROUP
——————————
SYS_GROUP
LOW_GROUP
OTHER_GROUPS
AUTO_TASK_CONSUMER_GROUP
DEFAULT_CONSUMER_GROUP
ORA$AUTOTASK_URGENT_GROUP
BATCH_GROUP
ORA$DIAGNOSTICS
ORA$AUTOTASK_HEALTH_GROUP
ORA$AUTOTASK_SQL_GROUP
ORA$AUTOTASK_SPACE_GROUP

CONSUMER_GROUP
——————————
ORA$AUTOTASK_STATS_GROUP
ORA$AUTOTASK_MEDIUM_GROUP
INTERACTIVE_GROUP

14 rows selected.

Each of these resource group is having some defined level of resource usage. We can create a job class and allocate a resource group to that job class using RESOURCE_CONSUMER_GROUP parameter as given below.

— Create a job class.
BEGIN
DBMS_SCHEDULER.create_job_class (
job_class_name          =>  ‘test_job_class’,
resource_consumer_group =>  ‘low_group’);
END;

If we don’t provide any value for RESOURCE_CONSUMER_GROUP parameter then a default value of DEFAULT_CONSUMER_GROUP will be allocated.

You can see different job classes and there association to consumer group using the following SQL.

SQL> SELECT job_class_name, resource_consumer_group FROM dba_scheduler_job_classes;

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP
—————————— ——————————
DEFAULT_JOB_CLASS
AUTO_TASKS_JOB_CLASS           AUTO_TASK_CONSUMER_GROUP
DBMS_JOB$
ORA$AT_JCURG_OS                ORA$AUTOTASK_URGENT_GROUP
ORA$AT_JCNRM_OS                ORA$AUTOTASK_STATS_GROUP
ORA$AT_JCMED_OS                ORA$AUTOTASK_MEDIUM_GROUP
ORA$AT_JCURG_SA                ORA$AUTOTASK_URGENT_GROUP
ORA$AT_JCNRM_SA                ORA$AUTOTASK_SPACE_GROUP
ORA$AT_JCMED_SA                ORA$AUTOTASK_MEDIUM_GROUP
ORA$AT_JCURG_SQ                ORA$AUTOTASK_URGENT_GROUP
ORA$AT_JCNRM_SQ                ORA$AUTOTASK_SQL_GROUP

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP
—————————— ——————————
ORA$AT_JCMED_SQ                ORA$AUTOTASK_MEDIUM_GROUP
AQ$_PROPAGATION_JOB_CLASS
XMLDB_NFS_JOBCLASS

14 rows selected.

Once Job class has been created you can create jobs and assign the jobs to one of the Job class. You can even assign the existing jobs to the job class.

BEGIN
— Job defined by an existing program and schedule and assigned to a job class.
DBMS_SCHEDULER.create_job (
job_name      => ‘new_job’,
program_name  => ‘test_plsql_block_prog’,
schedule_name => ‘test_hourly_schedule’,
job_class     => ‘test_job_class’,
enabled       => TRUE,
comments      => ‘Job defined by an existing program and schedule and assigned toa job class.’);

DBMS_SCHEDULER.set_attribute (
name      => ‘existing_job’,
attribute => ‘job_class’,
value     => ‘test_job_class’);
END;

You can check the scheduled jobs and the job class to which they belong using DBA_SCHEDULER_JOBS

SQL> SELECT owner, job_name, job_class, enabled FROM dba_scheduler_jobs;

OWNER      JOB_NAME                       JOB_CLASS            ENABL
———- —————————— ——————– —–
SYS        PURGE_LOG                      DEFAULT_JOB_CLASS    TRUE
SYS        GATHER_STATS_JOB               AUTO_TASKS_JOB_CLASS FALSE
SYS        XMLDB_NFS_CLEANUP_JOB          XMLDB_NFS_JOBCLASS   FALSE
SYS        AUTO_SPACE_ADVISOR_JOB         AUTO_TASKS_JOB_CLASS FALSE
SYS        FGR$AUTOPURGE_JOB              DEFAULT_JOB_CLASS    FALSE
SYS        ORA$AUTOTASK_CLEAN             DEFAULT_JOB_CLASS    TRUE
SYS        HM_CREATE_OFFLINE_DICTIONARY   DEFAULT_JOB_CLASS    TRUE
SYS        DRA_REEVALUATE_OPEN_FAILURES   DEFAULT_JOB_CLASS    TRUE
ORACLE_OCM MGMT_CONFIG_JOB                DEFAULT_JOB_CLASS    TRUE
ORACLE_OCM MGMT_STATS_CONFIG_JOB          DEFAULT_JOB_CLASS    TRUE
SYS        BSLN_MAINTAIN_STATS_JOB        DEFAULT_JOB_CLASS    TRUE

Window

A window is a time slot created within the database. A window is associated with a resource plan. A resource plan defines the resource allocation among the resource consumer group. Do not get confused with resource consumer group and resource plan. A resource consumer group is associated with Job Classes which defines how much resources are required by that job for execution. Where as resource plan is the blue print for resource allocation among resource consumer groups.

So when a resource plan is associated with a window, during that period of time when the window is active that particular resource plan will be active. This plan will decide the resource allocation to different consumer groups.

So if a certain amount of CPU is allocated to a resource plan which is currently assigned to the active window, then depending on the currently running job classes each will be assigned required resources from the available resources in the plan. Example of CPU allocated to resource plan is 50% of total CPU and 2 resource consumer group allocated to 2 job classes is running, one job class requires 20% CPU and other needs 80% CPU, then 50% CPU of resource plan will be divided into 80:20 ratio and will be provided to each of job classes. Job class will run the jobs accordingly.

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 ’60’ MINUTE,
window_priority => ‘LOW’,
comments        => ‘Window with a predefined schedule.’);

— 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 ’60’ MINUTE,
window_priority => ‘LOW’,
comments        => ‘Window with an inline schedule.’);
END;

You can display window names and resource plans using following query.

SQL> SELECT window_name, resource_plan, enabled, active FROM   dba_scheduler_windows;

WINDOW_NAME                    RESOURCE_PLAN                  ENABL ACTIV
—————————— —————————— —– —–
WEEKNIGHT_WINDOW               DEFAULT_MAINTENANCE_PLAN       FALSE FALSE
WEEKEND_WINDOW                 DEFAULT_MAINTENANCE_PLAN       FALSE FALSE
MONDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN       FALSE FALSE
ADS_GATHER_STATS_WINDOW1       SYSTEM_PLAN                    TRUE  FALSE
TUESDAY_WINDOW                 DEFAULT_MAINTENANCE_PLAN       FALSE FALSE
WEDNESDAY_WINDOW               DEFAULT_MAINTENANCE_PLAN       FALSE FALSE
THURSDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN       FALSE FALSE
FRIDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN       FALSE FALSE
SATURDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN       FALSE FALSE
SUNDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN       FALSE FALSE

Windows can be opened and closed manually using the OPEN_WINDOW and CLOSE_WINDOW procedures:

BEGIN
— Open window.
DBMS_SCHEDULER.open_window (
window_name => ‘test_window_2’,
duration    => INTERVAL ‘1’ MINUTE,
force       => TRUE);
END;

Windows can be dropped using the DROP_WINDOW procedure:

BEGIN
DBMS_SCHEDULER.drop_window (
window_name => ‘test_window_1’,
force       => TRUE);

DBMS_SCHEDULER.drop_window (
window_name => ‘test_window_2’,
force       => TRUE);
END;

Window Group

Window group is just a collection of similar windows. It can be created with 0, 1 or many windows as group members using the CREATE_WINDOW_GROUP procedure.

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;
/

You can display the window group details using following query.

SQL> SELECT window_group_name, enabled, number_of_windowS FROM   dba_scheduler_window_groups;

WINDOW_GROUP_NAME              ENABL NUMBER_OF_WINDOWS
—————————— —– —————–
MAINTENANCE_WINDOW_GROUP       TRUE                  9
ORA$AT_WGRP_OS                 TRUE                  9
ORA$AT_WGRP_SA                 TRUE                  9
ORA$AT_WGRP_SQ                 TRUE                  9

Windows can be added and removed from a group using the ADD_WINDOW_GROUP_MEMBER and REMOVE_WINDOW_GROUP_MEMBER procedures.

BEGIN
— Create a new window.
DBMS_SCHEDULER.create_window (
window_name     => ‘test_window_3’,
resource_plan   => NULL,
schedule_name   => ‘test_hourly_schedule’,
duration        => INTERVAL ’60’ MINUTE,
window_priority => ‘LOW’,
comments        => ‘Window with a predefined schedule.’);

DBMS_SCHEDULER.add_window_group_member (
group_name  => ‘test_window_group’,
window_list => ‘test_window_3’);
END;
/

BEGIN
DBMS_SCHEDULER.remove_window_group_member (
group_name  => ‘test_window_group’,
window_list => ‘test_window_3’);
END;
/

Window groups can be dropped using the drop_window_group procedure.

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

Hope this helps !!

References:

http://www.oracle-base.com/articles/10g/Scheduler10g.php