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