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 Resource Manager Examples

Oracle Tips by Burleson Consulting

Oracle Resource Manager examples

Job classes, windows and window groups provide a link between the scheduler and the resource manager. The syntax for creating these scheduler objects was presented in Chapter 2, so this section will illustrate how they should be used. Since a complete investigation of the resource manager is beyond the scope of this book, this section will focus on the basic elements needed to start integrating resource management into job schedules.

See these new notes on 12c Resource Manager (DBRM) enhancements for container databases.

The dbms_resource_manager package is an API which provides a means of controlling the allocation of system resources between Oracle sessions. Information about resource allocation can be displayed using the dba_rsrc_% views, which can be listed using the table_comments.sql script from Chapter 2, as shown below.

SQL> @table_comments.sql sys dba_rsrc

TABLE_NAME                     COMMENTS
------------------------------ -------------------------------------
DBA_RSRC_CONSUMER_GROUPS       all the resource consumer groups
DBA_RSRC_CONSUMER_GROUP_PRIVS Switch privileges for consumer groups
DBA_RSRC_GROUP_MAPPINGS        all the consumer group mappings
DBA_RSRC_MANAGER_SYSTEM_PRIVS system privileges for the resource
                               manager
DBA_RSRC_MAPPING_PRIORITY      the consumer group mapping attribute
                               priorities
DBA_RSRC_PLANS                 All the resource plans
DBA_RSRC_PLAN_DIRECTIVES       all the resource plan directives

7 rows selected.

Modifications to resource management must be complete and valid before they are applied to the system. For this reason, most operations using the dbms_resource_manager package are performed in a pending area where they are validated before being applied. The following code shows the procedure calls which must enclose any modifications:

BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;

-- Do something

DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

To illustrate the use of the resource manager, assume there is a system in which OLTP operations must take priority over batch operations during the day. At night, the situation is reversed such that batch operations take priority over OLTP operations.

To model this scenario, create two new consumer groups for the OLTP and batch tasks using the create_consumer_group procedure.

PROCEDURE create_consumer_group(
consumer_group IN VARCHAR2,
comment IN VARCHAR2,
cpu_mth IN VARCHAR2 DEFAULT 'ROUND-ROBIN')

The create_consumer_groups.sql script uses this procedure to create the OLTP and batch consumer groups.

* create_consumer_groups.sql

CONN sys/password AS SYSDBA
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;

-- Create the consumer groups
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'oltp_consumer_group',
comment => 'OLTP process consumer group.');

DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'batch_consumer_group',
comment => 'Batch process consumer group.');

DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

The consumer_groups.sql script listed below uses the dba_rsrc_consumer_groups view to display information about the consumer groups that have been created.

* consumer_groups.sql

column comments format a60

select
   consumer_group,
   comments
from
   dba_rsrc_consumer_groups
order by
   consumer_group
;

The output from this script is displayed below.

SQL> @consumer_groups.sql

CONSUMER_GROUP                 COMMENTS
------------------------------ ------------------------------------------------
AUTO_TASK_CONSUMER_GROUP       System maintenance task consumer group
BATCH_CONSUMER_GROUP           Batch process consumer group.
DEFAULT_CONSUMER_GROUP         consumer group for users not assigned to any
                               group
LOW_GROUP                      Group of low priority sessions
OLTP_CONSUMER_GROUP            OLTP process consumer group.
OTHER_GROUPS                   consumer group for users not included in any
                               group in the active top-plan
SYS_GROUP                      Group of system sessions

The delete_consumer_groups.sql script uses the delete_consumer_group procedure to clean up the consumer groups created for the example. The consumer groups can only be removed if they have no dependant plan directives.

* delete_consumer_groups.sql

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

-- Delete consumer groups.
DBMS_RESOURCE_MANAGER.delete_consumer_group (
consumer_group => 'oltp_consumer_group');

DBMS_RESOURCE_MANAGER.delete_consumer_group (
consumer_group => 'batch_consumer_group');

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

With the consumer groups present, a resource plan can be created using the create_plan procedure, and it can be associated to the consumer groups using the create_plan_directive procedure.

PROCEDURE create_plan (
plan                      IN VARCHAR2,
comment                   IN VARCHAR2,
cpu_mth                   IN VARCHAR2 DEFAULT 'EMPHASIS',
active_sess_pool_mth      IN VARCHAR2 DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',
parallel_degree_limit_mth IN VARCHAR2 DEFAULT 'PARALLEL_DEGREE_LIMIT_ABSOLUTE',
queueing_mth              IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT')

PROCEDURE create_plan_directive (
plan                      IN VARCHAR2,
   group_or_subplan          IN VARCHAR2,
comment                   IN VARCHAR2,
cpu_p1                    IN NUMBER DEFAULT NULL,
cpu_p2                    IN NUMBER DEFAULT NULL,
cpu_p3                    IN NUMBER DEFAULT NULL,
cpu_p4                    IN NUMBER DEFAULT NULL,
cpu_p5                    IN NUMBER DEFAULT NULL,
cpu_p6                    IN NUMBER DEFAULT NULL,
cpu_p7                    IN NUMBER DEFAULT NULL,
cpu_p8                    IN NUMBER DEFAULT NULL,
active_sess_pool_p1       IN NUMBER DEFAULT NULL,
queueing_p1               IN NUMBER DEFAULT NULL,
parallel_degree_limit_p1 IN NUMBER DEFAULT NULL,
   switch_group              IN VARCHAR2 DEFAULT NULL,
switch_time               IN NUMBER DEFAULT NULL,
switch_estimate           IN BOOLEAN DEFAULT FALSE,
max_est_exec_time         IN NUMBER DEFAULT NULL,
undo_pool                 IN NUMBER DEFAULT NULL,
max_idle_time            IN NUMBER DEFAULT NULL,
max_idle_blocker_time     IN NUMBER DEFAULT NULL,
switch_time_in_call       IN NUMBER DEFAULT NULL)

The day_plan.sql script uses these procedures to create a resource plan suitable for daytime processing. The OLTP operations are associated 80% of the CPU on level one; while batch operations receive 100% of the remaining CPU at level two. The switch_group and switch_time parameters are used in the OLTP plan directive to specify that OLTP processes lasting more than 60 seconds should be switched to the batch consumer group. The other_groups consumer group must be included in any valid plan as it provides resource allocation information for any processes that are not explicitly associated with the consumer groups.

* day_plan.sql

BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;

-- Create a new plan
DBMS_RESOURCE_MANAGER.create_plan(
plan => 'day_plan',
comment => 'Plan suitable for daytime processing.');

-- Assign consumer groups to plan and define priorities
DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'day_plan',
    group_or_subplan => 'oltp_consumer_group',
    comment          => 'Give OLTP processes higher priority - level 1',
    cpu_p1           => 80,
    switch_group     => 'batch_consumer_group',
    switch_time      => 60);

DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'day_plan',
    group_or_subplan => 'batch_consumer_group',
    comment          => 'Give batch processes lower priority - level 2',
    cpu_p2           => 100);

DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'day_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'all other users - level 3',
    cpu_p3           => 100);

DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

The night_plan.sql script creates a resource plan suitable for nighttime processing in which the resource allocation is the reverse of the daytime processing, such that batch processes receive 80% of the CPU at level one, and OLTP operations receive 100% of the remaining CPU at level two. Once again, the other_groups consumer group is specified as a catch-all.

* night_plan.sql

BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;

-- Create a new plan
DBMS_RESOURCE_MANAGER.create_plan(
plan => 'night_plan',
comment => 'Plan suitable for daytime processing.');

-- Assign consumer groups to plan and define priorities
DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'night_plan',
    group_or_subplan => 'batch_consumer_group',
    comment          => 'Give batch processes lower priority - level 2',
    cpu_p1           => 80);

DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'night_plan',
    group_or_subplan => 'oltp_consumer_group',
    comment          => 'Give OLTP processes higher priority - level 1',
    cpu_p2           => 100);

DBMS_RESOURCE_MANAGER.create_plan_directive(
   plan             => 'night_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'all other users - level 3',
    cpu_p3           => 100);

DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

The resource_plan_directives.sql script uses the dba_rsrc_plan_directives view to display information about the resource plans currently defined on the system.

* resource_plan_directives.sql

select
   plan,
   group_or_subplan,
   status
from
   dba_rsrc_plan_directives
order by
   plan,
   group_or_subplan
;

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

SQL> @resource_plan_directives.sql

PLAN                           GROUP_OR_SUBPLAN               STATUS
------------------------------ ------------------------------ ------
DAY_PLAN                       BATCH_CONSUMER_GROUP           ACTIVE
DAY_PLAN                       OLTP_CONSUMER_GROUP            ACTIVE
DAY_PLAN                       OTHER_GROUPS                   ACTIVE
INTERNAL_PLAN                  OTHER_GROUPS                   ACTIVE
INTERNAL_QUIESCE               OTHER_GROUPS                   ACTIVE
INTERNAL_QUIESCE               SYS_GROUP                      ACTIVE
NIGHT_PLAN                     BATCH_CONSUMER_GROUP           ACTIVE
NIGHT_PLAN                     OLTP_CONSUMER_GROUP            ACTIVE
NIGHT_PLAN                     OTHER_GROUPS                   ACTIVE
SYSTEM_PLAN                    LOW_GROUP                      ACTIVE
SYSTEM_PLAN                    OTHER_GROUPS                   ACTIVE
SYSTEM_PLAN                    SYS_GROUP                      ACTIVE

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.

��