how to apply resource manager to jobs

How To Incorporate Pre-Defined jobs into your Resource Manager Plan [ID 971991.1]

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 25-Jul-2012***

Goal

A standard Oracle Database is delivered with several pre-defined jobs that are scheduled to run on a specific schedule. The names and actions may be different from release to release but the concept is basically the same. Some example of these jobs are AUTO_GATHER_JOB, AUTO_SPACE_ADVISOR_JOB, etc

Oracle 10g, does not have pre-defined settings regarding resource manager and therefor the jobs will be run under the consumer group credentials of the user SYS or even worse in the OTHER_GROUPS consumer group.

As of Oracle 11g, the default maintenance window linked to the jobs will automatically switch the database into the DEFAULT_MAINTENANCE_PLAN plan and switych back to the original plan once the window has been closed.

While the credentials specified in the DEFAULT_MAINTENANCE_PLAN plan is suited for most database, some high demand environment such as 24*7 activity have the need to plan maintenance resources in a more intelligent manor. This article will illustrate how this can be achieved

Fix

There are basically 2 options to achieve this, both make use of the job class. Choose the one that best suites you.

First identify the job class and its consumer group linked to a specific job:

select j.job_name, j.schedule_name, jc.job_class_name, jc.resource_consumer_group
from dba_scheduler_jobs j, dba_scheduler_job_classes jc
where j.job_class = jc.job_class_name
and j.job_name='AUTO_SPACE_ADVISOR_JOB';

JOB_NAME SCHEDULE_NAME JOB_CLASS_NAME RESOURCE_CONSUMER_GROUP
------------------------ -------------------------- --------------------- -------------------------
AUTO_SPACE_ADVISOR_JOB MAINTENANCE_WINDOW_GROUP AUTO_TASKS_JOB_CLASS AUTO_TASK_CONSUMER_GROUP

Option 1: Use Existing Job Class Consumer Group

1. Assign the required resource to the AUTO_TASK_CONSUMER_GROUP in your existing resource manager plan

2. Grant switch privileges to the user SYS for this consumer group:

exec dbms_resource_manager_privs.grant_switch_consumer_group (grantee_name => 'SYS', consumer_group => 'AUTO_TASK_CONSUMER_GROUP', grant_option => FALSE);

--OR--

Option 2: Create your own job class

1. Create your own job class and assign the desired consumer group. In this example I use RM_BATCH_GROUP which is assigned 50% of the CPU at level 2

exec dbms_scheduler.create_job_class(
  job_class_name => 'MY_JOB_CLASS');
  resource_consumer_group => 'RM_BATCH_GROUP',
  comments => 'Job Class to serve automated jobs');

2. Modify the job to reflect the new job class

BEGIN
dbms_scheduler.disable ('AUTO_SPACE_ADVISOR_JOB');

dbms_scheduler.set_attribute (name=>'AUTO_SPACE_ADVISOR_JOB',attribute=>'job_class',value=>'MY_JOB_CLASS');

dbms_scheduler.enable ('AUTO_SPACE_ADVISOR_JOB');
END;
/