Job Classes and the Database Scheduler

This post is the first part in a series that discusses the use of job classes with the Oracle database scheduler.

1. Job Classes and the Database Scheduler
2. Demonstration of Scheduler Jobs (DBMS_SCHEDULER) with Job Class
3. Demonstration of Parallel Execution tasks (DBMS_PARALLEL_EXECUTE) with Job Class

Introduction

If you are using the database scheduler to run batch jobs, I recommend specifying a job class for each job, even if that requires creating a job class for every job. If you have developed a custom resource manager plan, sessions will usually be mapped to consumer groups by mapping rules on session attributes (database service, database user, client machine, client program, client OS user, module or action). You can also explicitly map a job within a job class to specific consumer groups and database services.  

For example, an application may execute the same code in different places, possibly as a part of online processing and also within scheduled batch processing, and then require different prioritisation for each by mapping to different consumer groups.  Application instrumentation may map online execution to one consumer group, but execution via a scheduler job can be explicitly mapped to a different consumer group via a job class.

Some code change is required to specify a job class when creating scheduler jobs.  Job class attributes can be altered at any time without requiring further code changes.

If you have not yet developed a custom resource manager plan, then at least use the default resource plan.  Performance metrics will indicate when you have run out of CPU and may need a custom resource manager plan to prioritise processing.  

What is a Job Class?

A job class is an Oracle Scheduler object that enables the scheduler administrator to group jobs for logical purposes; to assign the same set of attribute values to member jobs, to set service affinity for member jobs, to set resource allocation for member jobs, or to group jobs for prioritisation.

See also

• Scheduler Objects
• Scheduler Concepts: Job Classes

Various attributes can be set on a job class:

• Comment
• Log History Retention: Number of days for which job logging is retained.
• Logging Level: Off, Failed Runs, Runs, Full.
• Resource Consumer Group: This explicitly sets the consumer group for jobs in the job class.  Explicitly setting the consumer group always overrides any other mapping.
• Database service: The database service to which the jobs in the job class have affinity.   Some services may be available only on certain RAC instances; any job in this class will only run on those instances.
• If both the resource_consumer_group and service attributes are set for a job class, and if the service is mapped to a resource consumer group, the resource_consumer_group attribute takes precedence.

• NB: Resource Manager is an Enterprise Edition feature.  Job classes also control job logging and RAC affinity.  Job classes are available on Standard Edition, but their consumer group setting has no effect. 

Creating/Altering a Job Class

Job Classes can be dropped and created thus:

exec dbms_scheduler.drop_job_class(job_class_name => 'MY_JOB_CLASS', force=>TRUE);

begin
  dbms_scheduler.create_job_class
  (job_class_name => 'MY_JOB_CLASS'
  ,resource_consumer_group => 'HIGH_BATCH_GROUP'
  ,comments => 'dmk: A sample job class for demonstration purposes'
  );
END;
/

Attributes on existing job classes can be set and reset thus:

BEGIN
  dbms_scheduler.set_attribute
  (name=>'MY_JOB_CLASS'
  ,attribute=>'resource_consumer_group'
  ,value=>'HIGH_BATCH_GROUP');

  dbms_scheduler.set_attribute
  (name=>'MY_JOB_CLASS'
  ,attribute=>'log_history'
  ,value=>1 
  );
END;
/

• See also Job Class Attributes (DBMS_SCHEDULER: Job Class Attribute Values)

NB: For this demonstration, I have set the log retention to 1 day.  It is not a general recommendation.

Verify that the job class is created correctly by querying ALL_SCHEDULER_JOB_CLASSES.  Note that job classes can only be owned by SYS, never by other users.

SELECT *
FROM   all_scheduler_job_classes
WHERE  job_class_name like 'MY%CLASS'
ORDER BY 1,2
/
                                                                                    Logging        Log                                                                                 
OWNER            JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP  SERVICE    Level      History COMMENTS                                                                        
---------------- ------------------------------ ------------------------ ---------- ---------- ------- -----------------------------------------------------------------
SYS              MY_JOB_CLASS                   HIGH_BATCH_GROUP                    RUNS             1 dmk: A sample job class for demonstration purposes

Privileges

Job Class Execute Privileges

The user who submits the job request that specifies a job class (not the job's owner) must be granted execute permission on the job class.  In my tests, the job is owned by GFC_BATCH_USER, but it is executed by both GFC_BATCH_USER and GFC_GENERIC_READONLY;

GRANT EXECUTE ON sys.my_job_class TO GFC_BATCH_USER  ;
GRANT EXECUTE ON sys.my_job_class TO GFC_GENERIC_READONLY;

• Note that "Job classes defined with DBMS_SCHEDULER.CREATE_JOB_CLASS are not owned by the schema user but by user SYS (as inferred by looking at the error message itself)." - See Oracle Support Note 2087507.1: ORA-27476 After Creating Job Class, using SET_ATTRIBUTE to assign New Job Class To Existing Job

Verify execute privilege thus:

SELECT * FROM dba_tab_privs where table_name = 'MY_JOB_CLASS';

GRANTEE              OWNER            TABLE_NAME           GRANTOR              PRIVILEGE  GRA HIE COM TYPE                     INH
-------------------- ---------------- -------------------- -------------------- ---------- --- --- --- ------------------------ ---
GFC_GENERIC_READONLY SYS              MY_JOB_CLASS         SYS                  EXECUTE    NO  NO  NO  JOB CLASS                NO 
GFC_BATCH_USER       SYS              MY_JOB_CLASS         SYS                  EXECUTE    NO  NO  NO  JOB CLASS                NO

Switch Consumer Group Privilege

The job class explicitly switches the consumer group.  The user who submits the job request (not the user who owns the job) needs the privilege to switch consumer groups.

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
  (grantee_name   => 'GFC_GENERIC_READONLY'
  ,consumer_group => 'HIGH_BATCH_GROUP'
  ,grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
  (grantee_name   => 'GFC_BATCH_USER'
  ,consumer_group => 'HIGH_BATCH_GROUP'
  ,grant_option   => FALSE);
END;
/

In the following posts, I demonstrate how to set job classes on scheduler jobs and parallel tasks.