Resource Manager and Scheduler

---

Sometimes, availability is defined on a more limited scope than system-wide availability-perhaps the database is so available for one user that another user cannot get access to resources. Sure, you can always try to throw more resources at the problem, but let's face it, sometimes you have to make do with what you have. And that means that you have to mitigate the demands between different kinds of database users that compete for limited resources. For instance, if a long-running report is kicked off at night for end-of-month review, it could still be running come morning time when the OLTP users come in to begin transaction processing for the day. The users may find their updates running too slowly, or completely hung. To use modern buzzy words, you can think of the process of automatically controlling resource allocation as provisioning, albeit on a database scale only.

Oracle implemented the Resource Manager to assist with managing and mediating the pool of resources for the database between different classes of users and jobs. Then, to help expand the power of the Resource Manager, Oracle introduces in Oracle Database 10g the Scheduler.

Managing Limited Resources

The Resource Manager can be used to control database resource utilization to a high degree of granularity. It can be used to

• Allocate CPU percentages to user groups to better distribute processor time

• Guarantee a certain amount of processing power to specific users

• Based on optimizer estimates, prevent any transactions that would run longer than a specified time

• Limit session idle time, and allow for session-killing of blocking, idle sessions

• Limit the degree of parallelism allowed for user groups

As a DBA, you must determine which users or groups of users need which types of resource limitations or guarantees, and then you can use the DBMS_RESOURCE_MANAGER package. This package allows you to (most importantly) set up resource consumer groups. Consumer groups allow you to group users based on function or activity type, so that you can better control the group as a single entity.

To get started with the Resource Manager, you can set up a simple resource plan that does not require any complex associations, other than the creation of consumer groups and their CPU allotment.

BEGIN DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'woodscrew_plan',    CONSUMER_GROUP1 => 'order_placement', GROUP1_CPU => 70,    CONSUMER_GROUP2 => 'order_review', GROUP2_CPU => 30); END; /

There is clearly a lot more to the Resource Manager than simple CPU mapping, but we wanted to at least introduce you to the concept before we move on. From an availability perspective, a good handle on resource provisioning can help you keep available resources for those that need them most, when they need them most, and put a stop to those users who may be overutilizing resources best allocated elsewhere.

Heir to the Job Throne: The Scheduler

Oracle has introduced in Database 10g a new job scheduling utility. In previous versions, database jobs were administrated using the DBMS_JOB package, and the jobs themselves were run by job queue processes that would check the job queue at regular intervals to see if jobs needed to be executed. In Oracle Database 10g, DBMS_JOB has been deprecated in favor of DBMS_SCHEDULER, a more robust and complex job scheduling and administrating utility.

The Scheduler is rooted in the power of object-oriented programming for its modularization of tasks and attributes. Whereas the DBMS_JOB package was a simplistic list of what to do, and when to do it, the Scheduler allows for more fine-tuned control of the entire job, including job grouping, scheduling, and resource allocation maintenance. In essence, the Scheduler allows for better programmatical structuring of job creation, execution, and maintenance so that jobs better reflect the needs of your applications and business.

The first thing worth noting is that the Scheduler now breaks up a job into its different components, so that you now create a schedule, a program, and a job.

Schedules

Schedules reflect the when of a job, as well as its interval. By freeing schedules from each individual job, and making them independent database objects, you can now use the same schedule for multiple jobs.

A schedule is comprised of its name, a start date, an end date, and the repeating interval for any jobs that are assigned this schedule. You build a schedule with DBMS_SCHEDULER:

begin dbms_scheduler.create_schedule ( schedule_name     => 'nightly_review_schedule', start_date        => '24-DEC-2003 01:00:00AM', end_date          => '01-JAN-2005 01:00:00AM', repeat_interval   => 'FREQ=DAILY; INTERVAL=1', comments          => 'Schedule for Nightly Reviews'); end; / 

Note 

The repeat_interval uses a new calendaring syntax. You first need to specify the type of interval unit-DAILY, WEEKLY, MONTHLY, YEARLY, even MINUTELY (no, we're not kidding); then you specify how many intervals of those units should pass before rerunning any jobs with this schedule.

Programs

Programs define the how of a job. More specifically, think of a program as the metadata about a job that is run by the Scheduler: the program name, the program action, the program type, and the number of input arguments for the program. The program action will refer to what the program will do-run an executable file, or name a PL/SQL procedure. The program type defines what type of program this is: an external executable, a PL/SQL block, a stored procedure, a Java stored procedure, and so forth.

For instance, you can create a program that runs an external shell script for monitoring the alert log for errors:

BEGIN dbms_scheduler.create_program ( program_name     => 'alert_review_script', program_action   => '/oracle/ora10/admin/PROD/scripts/alert_rev.sh', program_type     => 'EXECUTABLE', comments         => 'executes an alert review'); end; /

Jobs

A job describes what it is you want to do. As in the past, you can schedule a free-standing job that has internalized its task and its schedule. However, if you have created a schedule and a program that you can use, the job is nothing more than a combination of the schedule and the program:

BEGIN dbms_scheduler.create_job ( job_name          => 'daily_alert_review', program_name      => 'alert_review_script', schedule_name     => 'nightly_review_schedule'); end; / 

Thus, based on the start time and interval of the schedule, and the metadata from the program, our job will kick off an external shell script called alert_rev.sh every night at 1:00 A.M.

Job Classes

In situations where there are high numbers of jobs that need to be maintained, Oracle introduces the job class, which allows you to group jobs by purpose similarity, run times, or any other classification system you want to use. Job classes allow you to prioritize work within jobs of the same class, as well as set attributes for all jobs in a class. Classes provide a maintenance opportunity as well as the ability to manage resources.

Windows

Windows are essentially resource allocation definitions. You create a window of time, be it a day or a week or a quarter, and then you assign a resource plan to the window. Thus, you can change resource allocation among users and groups based on the time of day or the month of the year.

Windows are created using DBMS_SCHEDULER, but they have little to do with jobs, necessarily. Windows are more a way of scheduling resource plan changes to coincide with different resource needs at different times. Windows are defined by when they open, when they close, the window priority, and the resource plan associated with the window. The window priority is included so that Oracle will know what to do if two or more windows overlap.

Begin dbms_scheduler.create_window ( window_name       => 'Year_end_reporting_window', resource_plan     => 'woodscrew_plan', start_date        => '01-JAN-2004 01:00:00AM', repeat_interval   => 'FREQ=YEARLY', end_date          => '31-JAN-2006 01:00:00AM', duration          => interval '30' day, window_priority   => 'HIGH', comments          => 'end of year sales reporting for CEO'); end; /

---