BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

DBMS_RESOURCE MANAGER Package

Oracle Tips by Burleson Consulting

The DBMS_RESOURCE_MANAGER package is used to administer the new resource plan and consumer group options since 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 9.5.

Table 9.5 DBMS_RESOURCE_MANAGER_PACKAGES

Procedure

Purpose

CREATE_PLAN

Creates entries that define resource plans. 

CREATE_SIMPLE_PLAN

Creates a single-level resource plan containing up to eight consumer groups in one step (9i).

UPDATE_PLAN

Updates entries that define resource plans. 

DELETE_PLAN

Deletes the specified plan as well as all the plan directives to which it refers. 

DELETE_PLAN_CASCADE

Deletes the specified plan as well as all its descendants (plan directives, subplans, consumer groups). 

CREATE_CONSUMER_GROUP

Creates entries that define resource consumer groups. 

UPDATE_CONSUMER_GROUP

Updates entries that define resource consumer groups. 

DELETE_CONSUMER_GROUP

Deletes entries that 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_MANAGER Procedure Syntax 

The calling syntaxes for all of the DBMS_RESOURCE_MANAGER packages are presented in the following subsections.

CREATE_PLAN Syntax

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',
   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');

where:

plan. The plan name.

comment. Any text comment you want associated with the plan name.

cpu_mth. Either set to EMPHASIS or ROUND-ROBIN. 

max_active_sess_target_mth. Allocation method for maximum active sessions.

active_sess_pool_mth. Type of allocation method used for maximum active sessions.

parallel_degree_limit_mth. Allocation method for degree of parallelism.

queueing_mth. Specifies type of queuing policy to use with active session pool feature. 

Syntax for the CREATE_SIMPLE_PLAN Procedure

This procedure creates a simplified group in which a top-level group, SYS_GROUP, with 100 percent allocation is created; all named groups are at level 2;and the OTHER_GROUPS is at 100 percent at level 3.

DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (
   Simple_plan                       IN VARCHAR2,
   Consumer_group1                   IN VARCHAR2,
   group1_cpu                        IN NUMBER,
   Consumer_group2                   IN VARCHAR2,
   group2_cpu                        IN NUMBER,
   Consumer_group3                   IN VARCHAR2,
   group3_cpu                        IN NUMBER,
   Consumer_group4                   IN VARCHAR2,
   group4_cpu                        IN NUMBER,
   Consumer_group5                   IN VARCHAR2,
   group5_cpu                        IN NUMBER,
   Consumer_group6                   IN VARCHAR2,
   group6_cpu                        IN NUMBER,
   Consumer_group7                   IN VARCHAR2,
   group7_cpu                        IN NUMBER,
   Consumer_group8                   IN VARCHAR2,
   group8_cpu                        IN NUMBER);

where:

Simple_plan. The plan name.

Consumer_group1-8. The name of the specified consumer group.

Group1-8_cpu. The percentage of CPU to give to the specified group.

UPDATE_PLAN Syntax

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
                                  'MAX_ACTIVE_SESS_ABSOLUTE',
   new_active_sess_pool_mth       IN VARCHAR2 DEFAULT
                              'ACTIVE_SESS_POOL_ABSOLUTE',
   new_parallel_degree_limit_mth  IN VARCHAR2 DEFAULT
                              'PARALLEL_DEGREE_LIMIT_ABSOLUTE',
   new_queueing_mth               IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT');

where:

plan. The plan name.

new_comment. Any new text comment you want associated with the plan name.

new_cpu_mth. Set to either EMPHASIS or ROUND-ROBIN.

new_max_active_sess_target_mth. New allocation method for maximum active sessions.

new_active_sess_pool_mth. New type of allocation method used for maximum active sessions.

new_parallel_degree_limit_mth. New allocation method for degree of parallelism.

new_queueing_mth. Specifies new type of queuing policy to use with active session pool feature. 

DELETE_PLAN Syntax

DBMS_RESOURCE_MANAGER.DELETE_PLAN (
   plan IN VARCHAR2);

where:

plan. Name of resource plan to delete.

DELETE_PLAN_CASCADE Syntax
DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE (
   plan IN VARCHAR2);

where:

plan. Name of plan.

CREATE_RESOURCE_GROUP Syntax

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.

UPDATE_RESOURCE_GROUP Syntax

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 maximum active sessions.

new_parallel_degree_limit_mth. Name of new method for degree of parallelism.

DELETE_RESOURCE_GROUP Syntax
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (
   consumer_group IN VARCHAR2);

where:

plan. Name of resource plan.

CREATE_PLAN_DIRECTIVE Syntax

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,
   active_sess_pool_p1       IN NUMBER   DEFAULT 1000000,
   queueing_p1               IN NUMBER   DEFAULT 1000000,
   parallel_degree_limit_p1  IN NUMBER   DEFAULT 1000000,
   switch_group              IN VARCHAR2 DEFAULT NULL,
   switch_time               IN NUMBER   DEFAULT 1000000,
   switch_estimate           IN BOOLEAN  DEFAULT FALSE,
   max_est_exec_time         IN NUMBER   DEFAULT 1000000,
   undo_pool                 IN NUMBER   DEFAULT 1000000);

where:

plan. Name of resource plan.

group_or_subplan.  Name of consumer group or subplan.

comment. Comment for the plan directive.

cpu_p1. First-level parameter for the CPU resource allocation method.

cpu_p2. Second-level parameter for the CPU resource allocation method.

cpu_p3. Third-level parameter for the CPU resource allocation method.

cpu_p4. Fourth-level parameter for the CPU resource allocation method.

cpu_p5. Fifth-level parameter for the CPU resource allocation method.

cpu_p6. Sixth-level parameter for the CPU resource allocation method.

cpu_p7. Seventh-level parameter for the CPU resource allocation method.

cpu_p8. Eighth-level parameter for the CPU resource allocation method.

active_sess_pool_p1.  First parameter for the maximum active sessions allocation method.          

queueing_p1. Queue timeout in seconds.

parallel_degree_limit_p1. First parameter for the degree of parallelism  allocation method.

switch_group. Group to switch to once switch time is reached.

switch_time. Maximum execution time within a group.

switch_estimate. Execution time estimate to assign a group.

Max_est_exec_time. Maximum estimated execution time in seconds.

Undo_pool. Maximum cumulative undo allocated for consumer groups.

UPDATE_PLAN_DIRECTIVE Syntax

DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (
   plan                          IN VARCHAR2,
   group_or_subplan              IN VARCHAR2,
   new_comment                   IN VARCHAR2,
   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_active_sess_pool_p1       IN NUMBER   DEFAULT NULL,
   new_queueing_p1               IN NUMBER   DEFAULT NULL,
   new_parallel_degree_limit_p1  IN NUMBER   DEFAULT NULL,
   new_switch_group              IN VARCHAR2 DEFAULT NULL,
   new_switch_time               IN NUMBER   DEFAULT NULL,
   new_switch_estimate           IN BOOLEAN  DEFAULT FALSE,
   new_max_est_exec_time         IN NUMBER   DEFAULT NULL,
   new_undo_pool                 IN NUMBER   DEFAULT NULL);

where:

plan. Name of resource plan.

group_or_subplan. Name of consumer group or subplan.

new_comment.  Comment for the plan directive.

new_cpu_p1. First-level parameter for the CPU resource allocation method.

new_cpu_p2. Second-level parameter for the CPU resource allocation method.

new_cpu_p3. Third-level parameter for the CPU resource allocation method.

new_cpu_p4. Fourth-level parameter for the CPU resource allocation method.

new_cpu_p5. Fifth-level parameter for the CPU resource allocation method.

new_cpu_p6. Sixth-level parameter for the CPU resource allocation method.

new_cpu_p7. Seventh-level parameter for the CPU resource allocation method.

new_cpu_p8. Eighth-level parameter for the CPU resource allocation method.

new_active_sess_pool_p1.  First parameter for the maximum active sessions allocation method.          

new_queueing_p1. Queue timeout in seconds.

new_parallel_degree_limit_p1. First parameter for the degree of parallelism  allocation method.

new_switch_group. Group to switch to once switch time is reached.

new_switch_time. Maximum execution time within a group.

new_switch_estimate.  Use execution time estimate to assign a group?

new_max_est_exec_time. Maximum estimated execution time in seconds.

new_undo_pool. Maximum cumulative undo allocated for consumer groups.

DELETE_PLAN_DIRECTIVE Syntax

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.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

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

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter