Section 8.5. Managing Priorities


8.5. Managing Priorities

Oracle 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 Manager

Oracle 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 Class

A 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.

Line

Description

3

Name of the job class.

4

When a job executes, it creates a log of its activities in a SYS-owned table named SCHEDULER$_EVENT_LOG. You can expose this table to others via the DBA_SCHEDULER_JOB_LOG view . The log entries can be full or partial. (See the "Managing Logging" section for an explanation.)

5

If the execution of the job is logged, then the log entries must be purged periodically; otherwise, the log will become unmanageably large. The log_history parameter determines how many days of logs must be kept. The default is 30 days. In this example, I increase the number of days to 45.

6

This job class is assigned the resource group OLTP_GROUP, which governs the resource allocation and utilization of the jobs defined in this job class.

7

Comments for this job class.


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.

Because a service also controls resource allocation, you cannot specify both the resource_consumer_plan and the service parameters; you must choose between these two approaches.


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.




Oracle PL(s)SQL For DBAs
Oracle PL(s)SQL For DBAs
ISBN: N/A
EAN: N/A
Year: 2005
Pages: 122

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net