|
 |
|
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.
|
