Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 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 s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 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.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.