|< Day Day Up >||
Sometimes, availability is defined on a more limited scope than system-wide availability-perhaps the database is so available for one user that another
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 10 g the Scheduler.
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
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.
Oracle has introduced in Database 10
a new job scheduling utility. In previous versions, database jobs were administrated using the DBMS_JOB package, and the jobs
The Scheduler is rooted in the power of object-oriented programming for its
The first thing worth noting is that the Scheduler now breaks up a job into its different
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
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; /
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 define the
of a job. More
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; /
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 .
In situations where there are high
Windows are created using DBMS_SCHEDULER, but they have little to do with jobs,
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; /
|< Day Day Up >||