Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle Scheduling Resource Manager Plan

Oracle Tips by Burleson Consulting

The resource manager is only activated when a default resource plan is assigned. Only one resource plan can be active at any given time. Resource plan switches can be automated using scheduler windows or performed manually by setting the resource_manager_plan parameter using the alter system command as shown below.

alter system set resource_manager_plan = day_plan;

The currently active resource plan can be identified by querying the v$rsrc_plan view as shown in the active_plan.sql script listed below.

* active_plan.sql

select
*
from
v$rsrc_plan
;

The output from the active_plan.sql script is displayed below.

SQL> @active_plan.sql

NAME IS_TO
-------------------------------- -----
DAY_PLAN TRUE

1 row selected.

The delete_plans.sql script uses the delete_plan procedure to remove the resource plans defined in this example. The resource_manager_plan parameter is unset before the plans are deleted, which deactivates the resource manager.

* delete_plans.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

alter system set resource_manager_plan = '';

BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area();
DBMS_RESOURCE_MANAGER.create_pending_area();

-- Delete plans.
DBMS_RESOURCE_MANAGER.delete_plan (
plan => 'day_plan');

DBMS_RESOURCE_MANAGER.delete_plan (
plan => 'night_plan');

DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

With the plans present, the create_job_classes.sql script can be used to create job classes that are associated with the OLTP and batch consumer groups.

* create_job_classes.sql

BEGIN
   DBMS_SCHEDULER.create_job_class(
      job_class_name          => 'oltp_job_class',
      resource_consumer_group => 'oltp_consumer_group',
      comments                => 'OLTP process job class.');

   DBMS_SCHEDULER.create_job_class(
      job_class_name          => 'batch_job_class',
      resource_consumer_group => 'batch_consumer_group',
      comments                => 'Batch process job class.');
END;
/

Using the job_classes.sql script from Chapter 2, it can be noted that the job classes were created correctly.

SQL> job_classes.sql

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP
------------------------------ ------------------------------
DEFAULT_JOB_CLASS
AUTO_TASKS_JOB_CLASS           AUTO_TASK_CONSUMER_GROUP
BATCH_JOB_CLASS                BATCH_CONSUMER_GROUP
OLTP_JOB_CLASS                 OLTP_CONSUMER_GROUP

4 rows selected.

The drop_job_classes.sql script uses the drop_job_class procedure to remove the job classes used in this example.

* drop_job_classes.sql

BEGIN
DBMS_SCHEDULER.drop_job_class (
job_class_name => 'oltp_job_class');

DBMS_SCHEDULER.drop_job_class (
job_class_name => 'batch_job_class');
END;
/

The consumer groups and job classes that have been created will work properly for jobs scheduled by the SYS user, but extra privileges must be granted before they can be used by other users. First, grant the EXECUTE privilege on both job classes, and then make sure the user can switch consumer groups properly by calling the grant_switch_consumer_group procedure from the dbms_resource_manager_privs package. The job_class_resource_privileges.sql script performs both tasks, granting the necessary privileges to a user called job_user.

* job_class_resource_privileges.sql

grant execute on oltp_job_class to job_user;
grant execute on batch_job_class to job_user;

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group (
    grantee_name   => 'JOB_USER',
    consumer_group => 'OLTP_CONSUMER_GROUP',
    grant_option   => TRUE);

DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group (
    grantee_name   => 'JOB_USER',
    consumer_group => 'BATCH_CONSUMER_GROUP',
    grant_option   => TRUE);
END;
/

With the job classes and privileges in place, create a job to test the resource manager. The test_resource_manager_job_1.sql script connects to a user called, job_user, and creates a job associated with the oltp_job_class job class.

* test_resource_manager_job_1.sql

conn job_user/job_user
BEGIN
DBMS_SCHEDULER.create_job (
    job_name        => 'test_resource_manager_job_1',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_LOCK.sleep(60); END;',
    job_class       => 'oltp_job_class',
    start_date      => SYSTIMESTAMP,
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job to test a job classes use of the resource manager.');
END;
/

The running_job_consumer_groups.sql script uses the dba_scheduler_running_jobs view to display the consumer groups associated with each running job.

* running_job_consumer_groups.sql

select
   job_name,
   resource_consumer_group
from
   dba_scheduler_running_jobs
order by
   job_name
;

The output from the running_job_consumer_groups.sql script is displayed below.

SQL> @running_job_consumer_groups.sql

JOB_NAME RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
TEST_RESOURCE_MANAGER_JOB_1 OLTP_CONSUMER_GROUP

1 row selected.

The consumer_group_usage.sql script uses the v$rsrc_consumer_group view to monitor the relative usage of each consumer group.

* consumer_group_usage.sql

select
   name,
   consumed_cpu_time
from
   v$rsrc_consumer_group
;

The output from this script is listed below.

SQL> @consumer_group_usage.sql

NAME                             CONSUMED_CPU_TIME
-------------------------------- -----------------
BATCH_CONSUMER_GROUP                             0
OTHER_GROUPS                                  2502
OLTP_CONSUMER_GROUP                             49

3 rows selected.

With the resource allocations and job classes defined, all that is left to do is to define windows to automatically switch between the day and night processing plans. The create_windows.sql script creates a 10-hour window associated with daytime processing and a 14-hour window associated with nighttime processing, with both windows added to a newly created window group.

* create_windows.sql

BEGIN
DBMS_SCHEDULER.create_window (
    window_name     => 'day_window',
    resource_plan   => 'day_plan',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=8; byminute=0; bysecond=0;',
    end_date        => NULL,
    duration        => INTERVAL '10' HOUR,
    window_priority => 'HIGH',
    comments        => 'Day time processing window.');

DBMS_SCHEDULER.create_window (
    window_name     => 'night_window',
    resource_plan   => 'night_plan',
    start_date      => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=18; byminute=0; bysecond=0;',
    end_date        => NULL,
    duration        => INTERVAL '14' HOUR,
    window_priority => 'HIGH',
    comments        => 'Night time processing window.');

DBMS_SCHEDULER.create_window_group (
    group_name => 'processing_window_group',
    window_list => 'day_window, night_window',
    comments    => '24 hour processing window group');
END;
/

Using the windows.sql script defined in Chapter 2, it can be seen that the windows were created successfully.

SQL> @windows.sql

WINDOW_NAME              RESOURCE_PLAN                  ENABL ACTIV
------------------------ ------------------------------ ----- -----DAY_WINDOW               DAY_PLAN                       TRUE FALSE
NIGHT_WINDOW             NIGHT_PLAN                     TRUE FALSE
WEEKEND_WINDOW                                          TRUE TRUE
WEEKNIGHT_WINDOW                                        TRUE FALSE

4 rows selected.

Rather than waiting for the windows to open automatically, they can be forced to open, and the effects on the active resource plan can be monitored. To do this, open the nighttime window using the open_window procedure, and then use the active_plan.sql script to display the resource plan currently active on the system.

BEGIN
DBMS_SCHEDULER.open_window (
   window_name => 'night_window',
   duration    => INTERVAL '30' MINUTE,
   force       => TRUE);
END;
/

SQL> @active_plan.sql

NAME IS_TO
-------------------------------- -----
NIGHT_PLAN TRUE

1 row selected.

The output from the active_plan.sql script shows that opening the nighttime window has activated the nighttime resource plan, as expected. Now, open the daytime window.

BEGIN
DBMS_SCHEDULER.open_window (
   window_name => 'day_window',
   duration    => INTERVAL '30' MINUTE,
   force       => TRUE);
END;
/

SQL> @active_plan.sql

NAME IS_TO
-------------------------------- -----
DAY_PLAN TRUE

1 row selected.

As expected, opening the daytime window has activated the daytime resource plan.

Windows can overlap, but it is not recommended since only one window can be open at any given time. When windows overlap, Oracle decides which one should open by using the following rules:

* If overlapping windows have the same priority, the currently open window will remain open.

* If overlapping windows have different priorities, the window with the highest priority will open and the lower priority window will be closed.

* When a window closes, the overlapping window with the highest percentage time remaining will open.

* When an open window is dropped, it is automatically closed.

The drop_windows.sql script uses the drop_window and drop_window_group procedures to remove the windows and window group defined in this example.

* drop_windows.sql

BEGIN
DBMS_SCHEDULER.drop_window (
window_name => 'day_window',
force => TRUE);

DBMS_SCHEDULER.drop_window (
window_name => 'night_window',
force => TRUE);

DBMS_SCHEDULER.drop_window_group (
group_name => 'processing_window_group',
force => TRUE);
END;
/

This section has shown how resource plans are created, linked to job classes and switched by windows. Armed with this information, it should be quite simple to create resource allocation schemes to suit various scheduling needs.

Now that information on how to manage the allocation of resources between jobs has been presented, the next section will detail how scheduler objects can be transferred between databases.

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.

��