8.5. Managing PrioritiesOracle allows you to assign priorities to jobs, and that is critical functionality for any robust scheduling mechanism. Suppose that you have created several jobs in the database with a variety of schedules. There is always a possibility that some jobs will overlap with others. After all, there are only 24 hours in a day. When two jobs run at the same time, they might consume resources such as CPU and memory at a pace not usually seen during the normal operation of the database. The resource consumptionmore accurately, the resource contentionmay cause both jobs to slow down (as well as other applications running on the system). In such a case, what should be done? You may want to give Job A all the resources it needs, and let Job B suffer a little until Job A completes. Or you may want to give Job B more resources. 8.5.1. Using the Resource ManagerOracle job scheduling priorities are managed through a database feature called the Resource Manager, which in turn is made available through the built-in package, DBMS_RESOURCE_MANAGER. It's beyond the scope of this book to discuss the Resource Manager, so I will assume you are aware of and familiar with it. Let's look at an example. First, I define a resource manager group known as OLTP_GROUP to handle the database load during regular OLTP activities. BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area ( ); DBMS_RESOURCE_MANAGER.create_pending_area ( ); DBMS_RESOURCE_MANAGER.create_consumer_group (consumer_group => 'oltp_group', COMMENT => 'OLTP Activity Group' ); DBMS_RESOURCE_MANAGER.submit_pending_area ( ); END; Next, I need to define plan directives that show how resources are allocated among various groups in the plan. Oracle comes with a predefined group known as OTHER_GROUP. In this example, I define a second group known as OLTP_GROUP, which serves as the group for all OLTP activities. I am going to assign 80% of the CPU to OLTP activities and the rest to the OTHER_GROUP. BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area ( ); DBMS_RESOURCE_MANAGER.create_pending_area ( ); DBMS_RESOURCE_MANAGER.create_plan ('OLTP_PLAN', 'OLTP Database Activity Plan' ); DBMS_RESOURCE_MANAGER.create_plan_directive (PLAN => 'OLTP_PLAN', group_or_subplan => 'OLTP_GROUP', COMMENT => 'This is the OLTP Plan', cpu_p1 => 80, parallel_degree_limit_p1 => 4, switch_group => 'OTHER_GROUPS', switch_time => 10, switch_estimate => TRUE, max_est_exec_time => 10, undo_pool => 500 ); DBMS_RESOURCE_MANAGER.create_plan_directive (PLAN => 'OLTP_PLAN', group_or_subplan => 'OTHER_GROUPS', COMMENT => NULL, cpu_p1 => 20, parallel_degree_limit_p1 => 0, active_sess_pool_p1 => 0, queueing_p1 => 0, switch_estimate => FALSE, max_est_exec_time => 0, undo_pool => 10 ); DBMS_RESOURCE_MANAGER.submit_pending_area ( ); END; After the resource plans have been established, I can use them when scheduling jobs. There are two different ways to use resource plans: in a job class and in a window. The next section describes the use of a job class. The later section "Managing Windows" describes how to use windows with DBMS_SCHEDULER, including their use with resource plans. 8.5.2. Job ClassA job class is a collection of properties. I can create a job class with DBMS_SCHEDULER's CREATE_JOB_CLASS procedure . When I create a job, I can specify the name of an already created job class using the job_class parameter in CREATE_JOB. Here is how to create a scheduler job class that follows the resource allocation group OLTP_GROUP. 1 BEGIN 2 DBMS_SCHEDULER.create_job_class 3 (job_class_name => 'OLTP_JOBS', 4 logging_level => dbms_scheduler.logging_full, 5 log_history => 45, 6 resource_consumer_group => 'OLTP_GROUP', 7 comments => 'OLTP Related Jobs' 8 ); 9 END; Let's examine the lines in detail in the following table.
After the job class is created, I can check its attributes or those of any previously defined job classes in the DBA_SCHEDULER_JOB_CLASSES data dictionary view. Here is a record from the view, shown in vertical format for easy readability. JOB_CLASS_NAME : OLTP_JOBS RESOURCE_CONSUMER_GROUP : OLTP_GROUP SERVICE : LOGGING_LEVEL : FULL LOG_HISTORY : 45 COMMENTS : OLTP Related Jobs When you define a job class, you can also define a service name under which the jobs in the job class will run. The service name must have been defined in the database. For example, if I use a service name called INTCALC_SRVC, I must have enabled it as follows: ALTER SYSTEM SET SERVICE_NAME = 'INTCALC_SRVC'; After that, I can define the job class under that service name by specifying the service parameter in the CREATE_JOB_CLASS procedure (see the highlighted line below). BEGIN DBMS_SCHEDULER.create_job_class (job_class_name => 'INT_JOBS', logging_level => dbms_scheduler.logging_full, log_history => 45, service => 'INTCALC_SRVC', comments => 'Interest Calculation Related Jobs' ); END; Service names are useful when you want to control a session connected to a service, instead of to an instance. An Oracle instance can have many services defined in it, and when sessions connect, they can specify a SERVICE_NAME parameter in the TNSNAMES.ORA file instead of using the SID in the connect string. When a session connects that way, the SERVICE_NAME column in V$SESSION shows it for that session. When the service is not enabled in the instance, the sessions can't connect, even if the instance is up. Services are helpful in other ways as well. For example, if you are running a multi-instance RAC database, you might want to define a service in only one instance of that database, thus restricting the job to only that one instance. When the instance fails, the sessions will be transferred over to the other surviving instances. Defining a service allows you to specify to which node the sessions get transferred when the instance fails. A service may also be used to control resource manager plans allocated to the sessions assigned the service.
As a last step, create a job that has the definition of that job class. In this case, I have created a job to collect optimizer statistics. BEGIN DBMS_SCHEDULER.create_job (job_name => 'COLLECT_STATS', job_type => 'STORED_PROCEDURE', job_action => 'collect_opt_stats', job_class => 'OLTP_JOBS', repeat_interval => 'FREQ=WEEKLY; INTERVAL=1', enabled => TRUE, comments => 'Collect Optimizer Stats' ); END; Note the highlighted linethat is the only difference from the other examples we've seen. The job_class parameter here is set to OLTP_JOBS, which is the job class I created. The job COLLECT_STATS inherits all the properties of the job class OLTP_JOBS. Because the job class has been assigned the resource consumer group OLTP_GROUP, all of the resource control parameters defined for the resource consumer group will be applicable to the job COLLECT_STATS. |