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 Concepts - Administering a New Resource Plan

Oracle Tips by Burleson Consulting

DBMS_RESOURCE_MANAGER Package

The DBMS_RESOURCE_MANAGER package is used to administer the new resource plan and consumer group options in Oracle8i. The package contains several procedures that are used to create, modify, drop and grant access to resource plans, groups, directives and pending areas. The invoker must have the ADMINISTER_RESOURCE_MANAGER system privilege to execute these procedures. The procedures to grant and revoke this privilege are in the package DBMS_RESOURCE_MANAGER_PRIVS. The procedures in DBMS_RESOURCE_MANAGER are listed in table 1.

Table 1 DBMS_RESOURCE_MANAGER_PACKAGES

Procedure	Purpose
CREATE_PLAN	Creates entries which define resource plans.
UPDATE_PLAN	Updates entries which define resource plans.
DELETE_PLAN	Deletes the specified plan as well as all the plan directives it refers to.
DELETE_PLAN_CASCADE	Deletes the specified plan as well as all its descendants (plan directives, subplans, consumer groups).
CREATE_CONSUMER_GROUP	Creates entries which define resource consumer groups.
UPDATE_CONSUMER_GROUP	Updates entries which define resource consumer groups.
DELETE_CONSUMER_GROUP	Deletes entries which define resource consumer groups.
CREATE_PLAN_DIRECTIVE	Creates resource plan directives.
UPDATE_PLAN_DIRECTIVE	Updates resource plan directives.
DELETE_PLAN_DIRECTIVE	Deletes resource plan directives.
CREATE_PENDING_AREA	Creates a work area for changes to resource manager objects.
VALIDATE_PENDING_AREA	Validates pending changes for the resource manager.
CLEAR_PENDING_AREA	Clears the work area for the resource manager.
SUBMIT_PENDING_AREA	Submits pending changes for the resource manager.
SET_INITIAL_CONSUMER_GROUP	Assigns the initial resource consumer group for a user.
SWITCH_CONSUMER_GROUP_FOR_SESS	Changes the resource consumer group of a specific session.
SWITCH_CONSUMER_GROUP_FOR_USER	Changes the resource consumer group for all sessions with a given user name.

DBMS_RESOURCE_MANGER Procedure Syntax

The calling syntax for all of the DBMS_RESOURCE_MANAGER packages follow.

Syntax for the CREATE_PLAN Procedure:

DBMS_RESOURCE_MANAGER.CREATE_PLAN (
   plan                       IN VARCHAR2,
   comment                    IN VARCHAR2,
   cpu_mth                    IN VARCHAR2 DEFAULT 'EMPHASIS',
   max_active_sess_target_mth IN VARCHAR2 DEFAULT
                                  'MAX_ACTIVE_SESS_ABSOLUTE',
   parallel_degree_limit_mth IN VARCHAR2 DEFAULT
                            'PARALLEL_DEGREE_LIMIT_ABSOLUTE');

Where:

Plan - the plan name

Comment - any text comment you want associated with the plan name

Cpu_mth - one of EMPHASIS or ROUND-ROBIN
max_active_sess_target_mth - allocation method for max. active sessions
parallel_degree_limit_mth - allocation method for degree of parallelism

Syntax for the UPDATE_PLAN Procedure:

DBMS_RESOURCE_MANAGER.UPDATE_PLAN (
   plan                           IN VARCHAR2,
   new_comment                    IN VARCHAR2 DEFAULT NULL,
   new_cpu_mth                    IN VARCHAR2 DEFAULT NULL,
   new_max_active_sess_target_mth IN VARCHAR2 DEFAULT NULL,
   new_parallel_degree_limit_mth IN VARCHAR2 DEFAULT NULL);

Where:

plan - name of resource plan

new_comment - new user's comment
new_cpu_mth - name of new allocation method for CPU resources
new_max_active_sess_target_mth - name of new method for max. active sessions
new_parallel_degree_limit_mth - name of new method for degree of parallelism

Syntax for the DELETE_PLAN Procedure:

DBMS_RESOURCE_MANAGER.DELETE_PLAN (
plan IN VARCHAR2);

Where:

Plan - Name of resource plan to delete.

Syntax for the DELETE_PLAN Procedure:

DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE (
plan IN VARCHAR2);

Where:

Plan - Name of plan.

Syntax for the CREATE_RESOURCE_GROUP Procedure:

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
   consumer_group IN VARCHAR2,
   comment        IN VARCHAR2,
   cpu_mth        IN VARCHAR2 DEFAULT 'ROUND-ROBIN');

Where:

consumer_group - Name of consumer group.
Comment - User's comment.
cpu_mth - Name of CPU resource allocation method.

Syntax for the UPDATE_RESOURCE_GROUP Procedure:

DBMS_RESOURCE_MANAGER.UPDATE_CONSUMER_GROUP (
   consumer_group IN VARCHAR2,
   new_comment    IN VARCHAR2 DEFAULT NULL,
   new_cpu_mth    IN VARCHAR2 DEFAULT NULL);

Where:
plan - name of resource plan
new_comment - new user's comment
new_cpu_mth - name of new allocation method for CPU resources
new_max_active_sess_target_mth - name of new method for max. active sessions
new_parallel_degree_limit_mth - name of new method for degree of parallelism

Syntax for the DELTE_RESOURCE_GROUP Procedure:

DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (
consumer_group IN VARCHAR2);

Where:
plan - name of resource plan.

Syntax for the CREATE_PLAN_DIRECTIVE Procedure:

DBMS_RESOURCE_MANAGER.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,
   max_active_sess_target_p1 IN NUMBER   DEFAULT NULL,
parallel_degree_limit_p1 IN NUMBER   DEFAULT NULL);

Where:
plan - name of resource plan
group_or_subplan - name of consumer group or subplan

comment - comment for the plan directive
cpu_p1 - first parameter for the CPU resource allocation method
cpu_p2 - second parameter for the CPU resource allocation method
cpu_p3 - third parameter for the CPU resource allocation method
cpu_p4 - fourth parameter for the CPU resource allocation method
cpu_p5 - fifth parameter for the CPU resource allocation method
cpu_p6 - sixth parameter for the CPU resource allocation method
cpu_p7 - seventh parameter for the CPU resource allocation method
cpu_p8 - eighth parameter for the CPU resource allocation method
max_active_sess_target_p1 - first parameter for the max. active sessions allocation
method
(RESERVED FOR FUTURE USE)
parallel_degree_limit_p1 - first parameter for the degree of parallelism allocation method

Syntax for the UPDATE_PLAN_DIRECTIVE Procedure:

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (
   plan                          IN VARCHAR2,
   group_or_subplan              IN VARCHAR2,
   new_comment                   IN VARCHAR2 DEFAULT NULL,
   new_cpu_p1                    IN NUMBER   DEFAULT NULL,
   new_cpu_p2                    IN NUMBER   DEFAULT NULL,
   new_cpu_p3                    IN NUMBER   DEFAULT NULL,
   new_cpu_p4                    IN NUMBER   DEFAULT NULL,
   new_cpu_p5                    IN NUMBER   DEFAULT NULL,
   new_cpu_p6                    IN NUMBER   DEFAULT NULL,
   new_cpu_p7                   IN NUMBER   DEFAULT NULL,
   new_cpu_p8                    IN NUMBER   DEFAULT NULL,
   new_max_active_sess_target_p1 IN NUMBER   DEFAULT NULL,
   new_parallel_degree_limit_p1 IN NUMBER   DEFAULT NULL);

Where:

plan - name of resource plan
group_or_subplan - name of group or subplan

new_comment - comment for the plan directive
new_cpu_p1 - first parameter for the CPU allocation method
new_cpu_p2 - parameter for the CPU allocation method
new_cpu_p3- parameter for the CPU allocation method
new_cpu_p4 - parameter for the CPU allocation method
new_cpu_p5 - parameter for the CPU allocation method
new_cpu_p6 - parameter for the CPU allocation method
new_cpu_p7 - parameter for the CPU allocation method
new_cpu_p8 - parameter for the CPU allocation method
new_max_active_sess_target_p1 - first parameter for the max. active sessions allocation method
(RESERVED FOR FUTURE USE)
new_parallel_degree_limit_p1 - first parameter for the degree of parallelism allocation method

Syntax for the DELETE_PLAN_DIRECTIVE Procedure:

DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (

plan IN VARCHAR2,
group_or_subplan IN VARCHAR2);

Where:

plan - name of resource plan
group_or_subplan - name of group or subplan.

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam. It?s only $19.95 when you buy it directly from the publisher here.

��