39
DBMS_RESOURCE_MANAGER

The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives. It also provides semantics so that you may group together changes to the plan schema.

Requirements

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.

Summary of Subprograms

Table 39-1 DBMS_RESOURCE_MANAGER Package Subprograms

CREATE_PLAN procedure

UPDATE_PLAN procedure

DELETE_PLAN procedure

DELETE_PLAN_CASCADE 
procedure

CREATE_CONSUMER_GROUP 
procedure

UPDATE_CONSUMER_GROUP 
procedure

DELETE_CONSUMER_GROUP 
procedure

CREATE_PLAN_DIRECTIVE 
procedure

UPDATE_PLAN_DIRECTIVE 
procedure

DELETE_PLAN_DIRECTIVE 
procedure

CREATE_PENDING_AREA 
procedure

VALIDATE_PENDING_AREA 
procedure

CLEAR_PENDING_AREA 
procedure

SUBMIT_PENDING_AREA 
procedure

SET_INITIAL_CONSUMER_GROUP 
procedure

SWITCH_CONSUMER_GROUP_FOR_
SESS procedure

SWITCH_CONSUMER_GROUP_FOR_
USER procedure

CREATE_PLAN procedure

This procedure creates entries which define resource plans.

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', 
   parallel_degree_limit_mth  IN VARCHAR2 DEFAULT 'PARALLEL_DEGREE_LIMIT_  
ABSOLUTE'); 

Parameters

Table 39-2 CREATE_PLAN Procedure Parameters

plan

cpu_mth

max_active_sess_target_
mth

parallel_degree_limit_
mth

comment

UPDATE_PLAN procedure

This procedure updates entries which define resource plans.

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 NULL,
   new_parallel_degree_limit_mth  IN VARCHAR2 DEFAULT NULL); 

Parameters

Table 39-3 UPDATE_PLAN Procedure Parameters

plan

new_comment

new_cpu_mth

new_max_active_sess_
target_mth

new_parallel_degree_
limit_mth

Usage Notes

If the parameters to UPDATE_PLAN are not specified, then they remain unchanged in the data dictionary.

DELETE_PLAN procedure

This procedure deletes the specified plan as well as all the plan directives to which it refers.

Syntax

DBMS_RESOURCE_MANAGER.DELETE_PLAN (
   plan IN VARCHAR2); 

Parameters

Table 39-4 DELETE_PLAN Procedure Parameters

plan

DELETE_PLAN_CASCADE procedure

This procedure deletes the specified plan and all of its descendants (plan directives, subplans, consumer groups). Mandatory objects and directives are not deleted.

Syntax

DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE (
   plan IN VARCHAR2); 

Parameters

Table 39-5 DELETE_PLAN_CASCADE Procedure Parameters

plan

Errors

If DELETE_PLAN_CASCADE encounters any error, then it rolls back, and nothing is deleted.

Usage Notes

Defaults are:

• cpu_method = EMPHASIS

• parallel_degree_limit_mth = PARALLEL_DEGREE_LIMIT_ABSOLUTE

• max_active_sess_target_mth = MAX_ACTIVE_SESS_ABSOLUTE

  Note: The parameter max_active_sess_target_mth is undocumented in this release: It is reserved for future use.

CREATE_CONSUMER_GROUP procedure

This procedure lets you create entries which define resource consumer groups.

Syntax

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

Parameters

Table 39-6 CREATE_CONSUMER_GROUP Procedure Parameters

consumer_group

comment

cpu_mth

UPDATE_CONSUMER_GROUP procedure

This procedure lets you update entries which define resource consumer groups.

Syntax

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

Parameters

Table 39-7 UPDATE_CONSUMER_GROUP Procedure Parameter

consumer_group

new_comment

new_cpu_mth

If the parameters to the UPDATE_CONSUMER_GROUP procedure are not specified, then they remain unchanged in the data dictionary.

DELETE_CONSUMER_GROUP procedure

This procedure lets you delete entries which define resource consumer groups.

Syntax

DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (
   consumer_group IN VARCHAR2); 

Parameters

Table 39-8 DELETE_CONSUMER_GROUP Procedure Parameters

consumer_group

CREATE_PLAN_DIRECTIVE procedure

This procedure lets you create resource plan directives.

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

Parameters

Table 39-9 CREATE_PLAN_DIRECTIVE Procedure Parameters

plan

group_or_subplan

comment

cpu_p1

cpu_p2

cpu_p3

cpu_p4

cpu_p5

cpu_p6

cpu_p7

cpu_p8

max_active_sess_target_
p1

parallel_degree_limit_
p1

All parameters default to NULL. However, for the EMPHASIS CPU resource allocation method, this case would starve all the users.

UPDATE_PLAN_DIRECTIVE procedure

This procedure lets you update resource plan directives.

Syntax

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

Parameters

Table 39-10 UPDATE_PLAN_DIRECTIVE Procedure Parameters

plan

group_or_subplan

comment

cpu_p1

cpu_p2

cpu_p3

cpu_p4

cpu_p5

cpu_p6

cpu_p7

cpu_p8

max_active_sess_target_
p1

parallel_degree_limit_
p1

If the parameters for UPDATE_PLAN_DIRECTIVE are left unspecified, then they remain unchanged in the data dictionary.

DELETE_PLAN_DIRECTIVE procedure

This procedure lets you delete resource plan directives.

Syntax

DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (
   plan             IN VARCHAR2, 
   group_or_subplan IN VARCHAR2);

Parameters

Table 39-11 DELETE_PLAN_DIRECTIVE Procedure Parameters

plan

group_or_subplan

CREATE_PENDING_AREA procedure

This procedure lets you make changes to resource manager objects.

All changes to the plan schema must be done within a pending area. The pending area can be thought of as a "scratch" area for plan schema changes. The administrator creates this pending area, makes changes as necessary, possibly validates these changes, and only when the submit is completed do these changes become active.

You may, at any time while the pending area is active, view the current plan schema with your changes by selecting from the appropriate user views.

At any time, you may clear the pending area if you want to stop the current changes. You may also call the VALIDATE procedure to confirm whether the changes you has made are valid. You do not have to do your changes in a given order to maintain a consistent group of entries. These checks are also implicitly done when the pending area is submitted.

Syntax

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

Parameters

None.

Usage Notes

The following rules must be adhered to, and they are checked whenever the validate or submit procedures are executed:

1. No plan schema may contain any loops.

2. All plans and consumer groups referred to by plan directives must exist.

3. All plans must have plan directives that refer to either plans or consumer groups.

4. All percentages in any given level must not add up to greater than 100 for the emphasis resource allocation method.

5. No plan may be deleted that is currently being used as a top plan by an active instance.

6. For Oracle8i, the plan directive parameter, parallel_degree_limit_p1, may only appear in plan directives that refer to consumer groups (i.e., not at subplans).

7. There cannot be more than 32 plan directives coming from any given plan (i.e., no plan can have more than 32 children).

8. There cannot be more than 32 consumer groups in any active plan schema.

9. Plans and consumer groups use the same namespace; therefore, no plan can have the same name as any consumer group.

10. There must be a plan directive for OTHER_GROUPS somewhere in any active plan schema.This ensures that a session not covered by the currently active plan is allocated resources as specified by the OTHER_GROUPS directive.

If any of the above rules are broken when checked by the VALIDATE or SUBMIT procedures, then an informative error message is returned. You may then make changes to fix the problem(s) and reissue the validate or submit procedures.

VALIDATE_PENDING_AREA procedure

This procedure lets you validate pending changes for the resource manager.

Syntax

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

Parameters

None.

CLEAR_PENDING_AREA procedure

This procedure lets you clear pending changes for the resource manager.

Syntax

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

Parameters

None.

SUBMIT_PENDING_AREA procedure

This procedure lets you submit pending changes for the resource manager: It clears the pending area after validating and committing the changes (if valid).

Syntax

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

Parameters

None.

Example

One of the advantages of plans is that they can refer to each other. The entries in a plan can either be consumer groups or subplans. For example, the following is also a set of valid CPU plan directives:

Table 39-12 MYDB PLAN CPU Plan Directives

If these plan directives were in effect and there were an infinite number of runnable sessions in all consumer groups, then the MAILDB plan would be assigned 30% of the available CPU resources, while the BUGDB plan would be assigned 70% of the available CPU resources. Breaking this further down, sessions in the "Postman" consumer group would be run 12% (40% of 30%) of the time, while sessions in the "Online" consumer group would be run 56% (80% of 70%) of the time. The following diagram depicts this scenario:

Conceptually below the consumer groups are the active sessions. In other words, a session belongs to a resource consumer group, and this consumer group is used by a plan to determine allocation of processing resources.

A multi-plan (plan with one or more subplans) definition of CPU plan directives cannot be collapsed into a single plan with one set of plan directives, because each plan is its own entity. The CPU quanta that is allotted to a plan or subplan gets used only within that plan, unless that plan contains no consumer groups with active sessions. Therefore, in this example, if the Bug Maintenance Group did not use any of its quanta, then it would get recycled within that plan, thus going back to level 1 within the BUGDB PLAN. If the multi-plan definition in the above example got collapsed into a single plan with multiple consumer groups, then there would be no way to explicitly recycle the Bug Maintenance Group's unused quanta. It would have to be recycled globally, thus giving the mail sessions an opportunity to use it.

The resources for a database can be partitioned at a high level among multiple applications and then repartitioned within an application. If a given group within an application does not need all the resources it is assigned, then the resource is only repartitioned within the same application.

This example uses the default plan and consumer group allocation methods:

create_pending_area();
create_plan(plan => 'BUGDB_PLAN', comment => 'Resource 
   plan/method for bug users' sessions');
create_plan(plan => 'MAILDB_PLAN', comment => 'Resource 
   plan/method for mail users' sessions');
create_plan(plan => 'MYDB_PLAN', comment => 'Resource 
   plan/method for bug and mail users' sessions');
create_consumer_group(consumer_group => 'Bug_Online_group', 
   comment => 'Resource consumer group/method for online bug users' 
   sessions');
create_consumer_group(consumer_group => 'Bug_Batch_group', 
   comment => 'Resource consumer group/method for bug users' sessions 
   who run batch jobs');
create_consumer_group(consumer_group => 
   'Bug_Maintenance_group', comment => 'Resource consumer 
   group/method for users' sessions who maintain the bug db');
create_consumer_group(consumer_group => 'Mail_users_group', 
   comment => 'Resource consumer group/method for mail users' 
   sessions');
create_consumer_group(consumer_group => 'Mail_Postman_group', 
   comment => 'Resource consumer group/method for mail postman');
create_consumer_group(consumer_group => 
   'Mail_Maintenance_group', comment => 'Resource consumer 
   group/method for users' sessions who maintain the mail db');
create_plan_directive(plan => 'BUGDB_PLAN', group_or_subplan 
   => 'Bug_Online_group', comment => 'online bug users' 
   sessions at level 0', cpu_p1 => 80, cpu_p2=> 0, 
   parallel_degree_limit_p1 => 8);
create_plan_directive(plan => 'BUGDB_PLAN', group_or_subplan 
   => 'Bug_Batch_group', comment => 'batch bug users' 
   sessions at level 0', cpu_p1 => 20, cpu_p2 => 0, 
   parallel_degree_limit_p1 => 2);
create_plan_directive(plan => 'BUGDB_PLAN', group_or_subplan 
   => 'Bug_Maintenance_group', comment => 'bug maintenance users' 
   sessions at level 1', cpu_p1 => 0, cpu_p2 => 100, 
   parallel_degree_limit_p1 => 3);
create_plan_directive(plan => 'MAILDB_PLAN', group_or_subplan 
   => 'Mail_Postman_group', comment => 'mail postman at 
   level 0', cpu_p1 => 40, cpu_p2 => 0, 
   parallel_degree_limit_p1 => 4);
create_plan_directive(plan => 'MAILDB_PLAN', group_or_subplan 
   => 'Mail_users_group', comment => 'mail users' sessions 
   at level 1', cpu_p1 => 0, cpu_p2 => 80, 
   parallel_degree_limit_p1 => 4);
create_plan_directive(plan => 'MAILDB_PLAN', group_or_subplan =>
   'Mail_Maintenance_group', comment => 'mail 
   maintenance users' sessions at level 1', cpu_p1 => 0, 
   cpu_p2 => 20, parallel_degree_limit_p1 => 2);
create_plan_directive(plan => 'MYDB_PLAN', group_or_subplan => 
   'MAILDB_PLAN', comment=> 'all mail users' sessions at 
   level 0', cpu_p1 => 30);
create_plan_directive(plan => 'MYDB_PLAN', group_or_subplan => 
   'BUGDB_PLAN', comment => 'all bug users' sessions at 
   level 0', cpu_p1 = 70);
validate_pending_area();
submit_pending_area();

The above call to VALIDATE_PENDING_AREA is optional, because the validation is implicitly done in SUBMIT_PENDING_AREA.

SET_INITIAL_CONSUMER_GROUP procedure

The initial consumer group of a user is the consumer group to which any session created by that user initially belongs. This procedure sets the initial resource consumer group for a user.

Syntax

DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP (
   user           IN VARCHAR2, 
   consumer_group IN VARCHAR2);

Parameters

Table 39-13 SET_INITIAL_CONSUMER_GROUP Procedure Parameters

user

consumer_group

Usage Notes

The ADMINISTER_RESOURCE_MANAGER or the ALTER USER system privilege are required to be able to execute this procedure. The user, or PUBLIC, must be directly granted switch privilege to a consumer group before it can be set to be the user's initial consumer group. Switch privilege for the initial consumer group cannot come from a role granted to that user.

If the initial consumer group for a user has never been set, then the user's initial consumer group is automatically the consumer group: DEFAULT_CONSUMER_GROUP.

DEFAULT_CONSUMER_GROUP has switch privileges granted to PUBLIC; therefore, all users are automatically granted switch privilege for this consumer group. Upon deletion of a consumer group, all users having the deleted group as their initial consumer group now have DEFAULT_CONSUMER_GROUP as their initial consumer group. All currently active sessions belonging to a deleted consumer group are switched to DEFAULT_CONSUMER_GROUP.

SWITCH_CONSUMER_GROUP_FOR_SESS procedure

This procedure lets you change the resource consumer group of a specific session. It also changes the consumer group of any (PQ) slave sessions that are related to the top user session.

Syntax

DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (
   session_id     IN NUMBER, 
   session_serial IN NUMBER, 
   consumer_group IN VARCHAR2);

Parameters

Table 39-14 SWITCH_CONSUMER_GROUP_FOR_SESS Procedure Parameters

session_id

session_serial

consumer_group

SWITCH_CONSUMER_GROUP_FOR_USER procedure

This procedure lets you change the resource consumer group for all sessions with a given user ID. It also change the consumer group of any (PQ) slave sessions that are related to the top user session.

Syntax

DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER (
   user           IN VARCHAR2, 
   consumer_group IN VARCHAR2);

Parameters

Table 39-15 SWITCH_CONSUMER_GROUP_FOR_USER Procedure Parameters

user

consumer_group

Usage Notes

The SWITCH_CONSUMER_GROUP_FOR_SESS and SWITCH_CONSUMER_GROUP_FOR_USER procedures let you to raise or lower the allocation of CPU resources of certain sessions or users. This provides a functionality similar to the nice command on UNIX.

These procedures cause the session to be moved into the newly specified consumer group immediately.