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

See Also:

For more information on using the Database Resource Manager, see Oracle Database Administrator's Guide.

This chapter contains the following topics:

Using DBMS_RESOURCE_MANAGER

Security Model

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.

Constants

Table 76-1 Constants - Names and Oracle Enterprise Manager Abbreviations

Constant	Definition
`client_machine`	`CONSTANT VARCHAR2(30) := 'CLIENT_MACHINE';`
`client_os_user`	`CONSTANT VARCHAR2(30) := 'CLIENT_OS_USER';`
`client_program`	`CONSTANT VARCHAR2(30) := 'CLIENT_PROGRAM';`
`module_name`	`CONSTANT VARCHAR2(30) := 'MODULE_NAME';`
`module_name_action`	`CONSTANT VARCHAR2(30) := 'MODULE_NAME_ACTION';`
`oracle_user`	`CONSTANT VARCHAR2(30) := 'ORACLE_USER'`
`service_module`	`CONSTANT VARCHAR2(30) := 'SERVICE_MODULE';`
`service_module_action`	`CONSTANT VARCHAR2(30) := 'SERVICE_MODULE_ACTION';`
`service_name`	`CONSTANT VARCHAR2(30) := 'SERVICE_NAME';`

Examples

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 76-2 MYDB PLAN CPU Plan Directives

Subplan/Group	CPU_Level 1
MAILDB Plan	30%
BUGDB Plan	70%

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. Figure 76-1 diagram depicts this scenario:

Figure 76-1 Resource Manager Scenario

Text description of arpls007.gif follows

Text description of the illustration arpls007.gif

Conceptually the active sessions are underneath the consumer groups. 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 multiplan (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 multiplan definition in the preceding 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.

The following example uses the default plan and consumer group allocation methods:

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'bugdb_plan', 
   COMMENT => 'Resource plan/method for bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'maildb_plan', 
   COMMENT => 'Resource plan/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'mydb_plan', 
   COMMENT => 'Resource plan/method for bug and mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Online_
group', 
   COMMENT => 'Resource consumer group/method for online bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Batch_group', 
COMMENT => 'Resource consumer group/method for bug users sessions who run batch 
jobs');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maintenance_
group',
   COMMENT => 'Resource consumer group/method for users sessions who maintain 
   the bug  db');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_users_
group', 
   COMMENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Postman_
group',
   COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maintenance_
group', 
   COMMENT => 'Resource consumer group/method for users sessions who maintain 
the mail
   db');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_
SUBPLAN => 'Bug_Online_group',
   COMMENT => 'online bug users sessions at level 1', CPU_P1 => 80, CPU_P2=> 0,
   PARALLEL_DEGREE_LIMIT_P1 => 8);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_
SUBPLAN => 'Bug_Batch_group', 
   COMMENT => 'batch bug users sessions at level 1', CPU_P1 => 20, CPU_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_
SUBPLAN => 'Bug_Maintenance_group',
   COMMENT => 'bug maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 
=> 100, PARALLEL_DEGREE_LIMIT_P1 => 3);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan', GROUP_OR_
SUBPLAN => 'OTHER_GROUPS', 
   COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0, 
CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_
SUBPLAN => 'Mail_Postman_group',
   COMMENT => 'mail postman at level 1', CPU_P1 => 40, CPU_P2 => 0,
   PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_
SUBPLAN => 'Mail_users_group',
   COMMENT => 'mail users sessions at level 2', CPU_P1 => 0, CPU_P2 => 80,
   PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_
SUBPLAN => 'Mail_Maintenance_group',
   COMMENT => 'mail maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 
=> 20,
   PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan', GROUP_OR_
SUBPLAN => 'OTHER_GROUPS', 
   COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0, 
CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', GROUP_OR_
SUBPLAN => 'maildb_plan', 
   COMMENT=> 'all mail users sessions at level 1', CPU_P1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan', GROUP_OR_
SUBPLAN => 'bugdb_plan', 
   COMMENT => 'all bug users sessions at level 1', CPU_P1 => 70);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

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

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(
 PLAN => 'bugdb_plan',
 COMMENT => 'Resource plan/method for bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
 PLAN => 'maildb_plan',
 COMMENT => 'Resource plan/method for mail users sessions');

DBMS_RESOURCE_MANAGER.CREATE_PLAN(
 PLAN => 'mydb_plan',
 COMMENT => 'Resource plan/method for bug and mail users sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
 CONSUMER_GROUP => 'Bug_Online_group',
 COMMENT => 'Resource consumer group/method for online bug users sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
 CONSUMER_GROUP => 'Bug_Batch_group',
 COMMENT => 'Resource consumer group/method for bug users sessions who run
batch jobs');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
 CONSUMER_GROUP => 'Bug_Maintenance_group',
 COMMENT => 'Resource consumer group/method for users sessions who maintain
the bug db');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
 CONSUMER_GROUP => 'Mail_users_group',
 COMMENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
 CONSUMER_GROUP => 'Mail_Postman_group',
 COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
 CONSUMER_GROUP => 'Mail_Maintenance_group',
 COMMENT => 'Resource consumer group/method for users sessions who maintain
the mail
db');

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 'Bug_Online_group',
 COMMENT => 'online bug users sessions at level 1',
 CPU_P1 => 80, CPU_P2=> 0,
 PARALLEL_DEGREE_LIMIT_P1 => 8);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 'Bug_Batch_group',
 COMMENT => 'batch bug users sessions at level 1',
 CPU_P1 => 20, CPU_P2 => 0,
 PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 'Bug_Maintenance_group',
 COMMENT => 'bug maintenance users sessions at level 2',
 CPU_P1 => 0, CPU_P2 => 100,
 PARALLEL_DEGREE_LIMIT_P1 => 3);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'bugdb_plan', GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
 COMMENT => 'all other users sessions at level 3',
 CPU_P1 => 0, CPU_P2 => 0, CPU_P3 => 100);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 'Mail_Postman_group',
 COMMENT => 'mail postman at level 1',
 CPU_P1 => 40, CPU_P2 => 0,
 PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 'Mail_users_group',
 COMMENT => 'mail users sessions at level 2',
 CPU_P1 => 0, CPU_P2 => 80,
 PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 'Mail_Maintenance_group',
 COMMENT => 'mail maintenance users sessions at level 2',
 CPU_P1 => 0, CPU_P2 => 20,
 PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'maildb_plan', GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
 COMMENT => 'all other users sessions at level 3',
 CPU_P1 => 0, CPU_P2 => 0, CPU_P3 => 100);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'mydb_plan', GROUP_OR_SUBPLAN => 'maildb_plan',
 COMMENT=> 'all mail users sessions at level 1',
 CPU_P1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
 PLAN => 'mydb_plan', GROUP_OR_SUBPLAN => 'bugdb_plan',
 COMMENT => 'all bug users sessions at level 1',
 CPU_P1 => 70);

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

Summary of DBMS_RESOURCE_MANAGER Subprograms

Table 76-3 DBMS_RESOURCE_MANAGER Package Subprograms

Subprogram	Description
CLEAR_PENDING_AREA Procedure	Clears the work area for the resource manager
CREATE_CONSUMER_GROUP Procedure	Creates entries which define resource consumer groups
CREATE_PENDING_AREA Procedure	Creates a work area for changes to resource manager objects
CREATE_PLAN Procedure	Creates entries which define resource plans
CREATE_PLAN_DIRECTIVE Procedure	Creates resource plan directives
CREATE_SIMPLE_PLAN Procedure	Creates a single-level resource plan containing up to eight consumer groups in one step
DELETE_CONSUMER_GROUP Procedure	Deletes entries which define resource consumer groups
DELETE_PLAN Procedure	Deletes the specified plan as well as all the plan directives it refers to
DELETE_PLAN_CASCADE Procedure	Deletes the specified plan as well as all its descendants (plan directives, subplans, consumer groups)
DELETE_PLAN_DIRECTIVE Procedure	Deletes resource plan directives
SET_CONSUMER_GROUP_MAPPING Procedure	Adds, deletes, or modifes pairs for the login and run-time attribute mappings
SET_CONSUMER_GROUP_MAPPING_PRI Procedure	Creates the session attribute mapping priority list
SET_INITIAL_CONSUMER_GROUP Procedure	Assigns the initial resource consumer group for a user
SUBMIT_PENDING_AREA Procedure	Submits pending changes for the resource manager
SWITCH_CONSUMER_GROUP_FOR_SESS Procedure	Changes the resource consumer group of a specific session
SWITCH_CONSUMER_GROUP_FOR_USER Procedure	Changes the resource consumer group for all sessions with a given user name
SWITCH_PLAN Procedure	Sets the current resource manager plan
UPDATE_CONSUMER_GROUP Procedure	Updates entries which define resource consumer groups
UPDATE_PLAN Procedure	Updates entries which define resource plans
UPDATE_PLAN_DIRECTIVE Procedure	Updates resource plan directives
VALIDATE_PENDING_AREA Procedure	Validates pending changes for the resource manage

CLEAR_PENDING_AREA Procedure

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

Syntax

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

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 76-4 CREATE_CONSUMER_GROUP Procedure Parameters

Parameter	Description
`consumer_group`	The name of the consumer group.
`comment`	The user's comment.
`cpu_mth`	The resource allocation method for distributing CPU among sessions in the consumer group. The default is `ROUND-ROBIN`, which uses a round-robin scheduler to ensure sessions are fairly executed. `RUN-TO-COMPLETION` specifies that sessions with the largest active time are scheduled ahead of other sessions

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.

Syntax

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

Usage Notes

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.

Note:

Oracle allows "orphan" consumer groups (in other words, consumer groups that have no plan directives that refer to them). This is in anticipation that an administrator may want to create a consumer group that is not currently being used, but will be used in the future.

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

No plan schema may contain any loops.
All plans and consumer groups referred to by plan directives must exist.
All plans must have plan directives that refer to either plans or consumer groups.
All percentages in any given level must not add up to greater than 100 for the emphasis resource allocation method.
No plan may be deleted that is currently being used as a top plan by an active instance.
For Oracle8i, the plan directive parameter, parallel_degree_limit_p1, may only appear in plan directives that refer to consumer groups (that is, not at subplans).
There cannot be more than 32 plan directives coming from any given plan (that is, no plan can have more than 32 children).
There cannot be more than 32 consumer groups in any active plan schema.
Plans and consumer groups use the same namespace; therefore, no plan can have the same name as any consumer group.
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 preceding 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.

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

Parameters

Table 76-5 CREATE_PLAN Procedure Parameters

Parameter	Description
`plan`	The name of the resource plan.
`comment`	User's comment.
`cpu_mth`	The resource allocation method for specifying how much CPU each consumer group or sub plan gets. `EMPHASIS`, the default method, is for multilevel plans that use percentages to specify how CPU is distributed among consumer groups. `RATIO` is for single-level plans that use ratios to specify how CPU is distributed.
`active_sess_pool_mth`	The Active session pool resource allocation method. Limits the number of active sessions. All other sessions are inactive and wait in a queue to be activated. `ACTIVE_SESS_POOL_ABSOLUTE` is the default and only method available.
`parallel_degree_limit_mth`	The resource allocation method for specifying a limit on the degree of parallelism of any operation. `PARALLEL_DEGREE_LIMIT_ABSOLUTE` is the default and only method available.
`queueing_mth`	The Queuing resource allocation method. Controls order in which queued inactive sessions will execute. `FIFO_TIMEOUT` is the default and only method available

Usage Notes

If you want to use any default resource allocation method, then you do not need not specify it when creating or updating a plan.

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

Parameters

Table 76-6 CREATE_PLAN_DIRECTIVE Procedure Parameters

Parameter	Description
`plan`	The name of the resource plan.
`group_or_subplan`	The name of the consumer group or subplan.
`comment`	Comment for the plan directive.
`cpu_p1`	For `EMPHASIS`, specifies the CPU percentage at the first level. For `RATIO`, specifies the weight of cpu usage. Default is `NULL` for all CPU parameters.
`cpu_p2`	For `EMPHASIS`, specifies the CPU percentage at the second level. Not applicable for `RATIO`.
`cpu_p3`	For `EMPHASIS`, specifies the CPU percentage at the third level. Not applicable for `RATIO`.
`cpu_p4`	For `EMPHASIS`, specifies the CPU percentage at the fourth level. Not applicable for `RATIO`.
`cpu_p5`	For `EMPHASIS`, specifies the CPU percentage at the fifth level. Not applicable for `RATIO`.
`cpu_p6`	For `EMPHASIS`, specifies the CPU percentage at the sixth level. Not applicable for `RATIO`.
`cpu_p7`	For `EMPHASIS`, specifies the CPU percentage at the seventh level. Not applicable for `RATIO`.
`cpu_p8`	For `EMPHASIS`, specifies the CPU percentage at the eighth level. Not applicable for `RATIO`.
`active_sess_pool_p1`	Specifies maximum number of concurrently active sessions for a consumer group. Default is `NULL`, which means unlimited.
`queueing_p1`	Specified time (in seconds) after which a job in the inactive session queue (waiting for execution) will time out. Default is `NULL`, which means unlimited.
`parallel_degree_limit_p1`	Specifies a limit on the degree of parallelism for any operation. Default is `NULL`, which means unlimited.
`switch_group`	Specifies consumer group to which this session is switched if other switch criteria is met. Default is `NULL`. If the group name is '`CANCEL_SQL`', the current call will be canceled when other switch criteria are met. If the group name is '`KILL_SESSION`', the session will be killed when other switch criteria are met.
`switch_time`	Specifies time (in seconds) that a session can execute before an action is taken. Default is `NULL`, which means unlimited.
`switch_estimate`	If `TRUE`, tells Oracle to use its execution time estimate to automatically switch the consumer group of an operation before beginning its execution. Default is `FALSE`.
`max_est_exec_time`	Specifies the maximum execution time (in seconds) allowed for a session. If the optimizer estimates that an operation will take longer than `MAX_EST_EXEC_TIME`, the operation is not started and ORA-07455 is issued. If the optimizer does not provide an estimate, this directive has no effect. Default is `NULL`, which means unlimited.
`undo_pool`	Sets a maximum in kilobytes (`K`) on the total amount of undo generated by a consumer group. Default is `NULL`, which means unlimited.
`max_idle_time`	Indicates the maximum session idle time. Default is `NULL`, which means unlimited.
`max_idle_blocker_time`	The maximum amount of time in seconds that a session can be idle while blocking another session's acquisition of a resource.
`switch_time_in_call`	Specifies time (in seconds) that a session can execute before an action is taken. At the end of the top call, the consumer group of the session is restored to its original consumer group. Default is `NULL`, which means unlimited. Both `SWITCH_TIME_IN_CALL` and `SWITCH_TIME` cannot be specified.

Usage Notes

All parameters default to NULL. However, for the EMPHASIS CPU resource allocation method, this case would starve all the users.
For max_idle_time and max_idle_blocker_time, PMON will check these limits once a minute. If it finds a session that has exceeded one of the limits, it will forcibly kill the session and clean up all its state.
The parameter switch_time_in_call is mostly useful for three-tier applications where the mid-tier server is implementing session pooling. By using switch_time_in_call, the resource usage of one client will not affect a future client that happens to be executed on the same session.

CREATE_SIMPLE_PLAN Procedure

This procedure creates a single-level resource plan containing up to eight consumer groups in one step. You do not need to create a pending area manually before creating a resource plan, or use the CREATE_CONSUMER_GROUP and CREATE_RESOURCE_PLAN_DIRECTIVES procedures separately.

Syntax

DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (
   SIMPLE_PLAN      IN  VARCHAR2  DEFAULT,
   CONSUMER_GROUP1  IN  VARCHAR2  DEFAULT,
   GROUP1_CPU       IN  NUMBER    DEFAULT,
   CONSUMER_GROUP2  IN  VARCHAR2  DEFAULT,
   GROUP2_CPU       IN  NUMBER    DEFAULT,
   CONSUMER_GROUP3  IN  VARCHAR2  DEFAULT,
   GROUP3_CPU       IN  NUMBER    DEFAULT,
   CONSUMER_GROUP4  IN  VARCHAR2  DEFAULT,
   GROUP4_CPU       IN  NUMBER    DEFAULT,
   CONSUMER_GROUP5  IN  VARCHAR2  DEFAULT,
   GROUP5_CPU       IN  NUMBER    DEFAULT,
   CONSUMER_GROUP6  IN  VARCHAR2  DEFAULT,
   GROUP6_CPU       IN  NUMBER    DEFAULT,
   CONSUMER_GROUP7  IN  VARCHAR2  DEFAULT,
   GROUP7_CPU       IN  NUMBER    DEFAULT,
   CONSUMER_GROUP8  IN  VARCHAR2  DEFAULT,
   GROUP8_CPU       IN  NUMBER    DEFAULT);

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 76-7 DELETE_CONSUMER_GROUP Procedure Parameters

Parameters	Description
`consumer_group`	The name of the consumer group to be deleted.

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 76-8 DELETE_PLAN Procedure Parameters

Parameter	Description
`plan`	The name of the resource plan to delete.

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 76-9 DELETE_PLAN_DIRECTIVE Procedure Parameters

Parameter	Description
`plan`	The name of the resource plan.
`group_or_subplan`	The name of the group or subplan.

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 76-10 DELETE_PLAN_CASCADE Procedure Parameters

Parameters	Description
`plan`	The name of the plan.

Usage Notes

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

SET_CONSUMER_GROUP_MAPPING Procedure

This procedure adds, deletes, or modifies entries that map sessions to consumer groups, based on the session's login and runtime attributes.

Syntax

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
   attribute       IN VARCHAR2, 
   value            IN VARCHAR2, 
   consumer_group  IN VARCHAR2 DEFAULT NULL);

Parameters

Table 76-11 SET_CONSUMER_GROUP_MAPPING Procedure Parameters

Parameters	Description
`attribute`	The mapping attribute to add/modify. It can be one of the Constants listed.
`value`	The attribute value to match.
`consumer_group`	The name of the mapped consumer group, or `NULL` to delete a mapping.

Usage Notes

If no mapping exists for the given attribute and value, a mapping to the given consumer group will be created. If a mapping already exists for the given attribute and value, the mapped consumer group will be updated to the one given. If the consumer_group argument is NULL, then any mapping from the given attribute and value will be deleted.

SET_CONSUMER_GROUP_MAPPING_PRI Procedure

Multiple attributes of a session can be used to map the session to a consumer group. This procedure prioritizes the attribute mappings.

Syntax

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
   explicit               IN NUMBER, 
   oracle_user            IN NUMBER, 
   service_name           IN NUMBER, 
   client_os_user         IN NUMBER, 
   client_program         IN NUMBER, 
   client_machine         IN NUMBER, 
   module_name            IN NUMBER, 
   module_name_action     IN NUMBER,
   service_module         IN NUMBER,
   service_module_action  IN NUMBER);

Parameters

Table 76-12 SET_CONSUMER_GROUP_MAPPING_PRI Procedure Parameters

Parameters	Description
`explicit`	The priority of the explicit mapping.
`oracle_user`	The priority of the Oracle user name mapping.
`service_name`	The priority of the client service name mapping.
`client_os_user`	The priority of the client operating system user name mapping.
`client_program`	The priority of the client program mapping.
`client_machine`	The priority of the client machine mapping.
`module_name`	The priority of the application module name mapping.
`module_name_action`	The priority of the application module name and action mapping.
`service_module`	The priority of the service name and application module name mapping.
`module_name_action`	The priority of the service name, application module name, and application action mapping.

-PRIORITY

Usage Notes

Each priority value must be a unique integer from 1 to 10. Together, they establish an ordering where 1 is the highest priority and 10 is the lowest.

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 76-13 SET_INITIAL_CONSUMER_GROUP Procedure Parameters

Parameters	Description
`user`	The name of the user.
`consumer_group`	The user's initial 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.

Note:

These semantics are similar to those for ALTER USER DEFAULT ROLE.

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.

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

Note:

A call to SUBMIT_PENDING_AREA may fail even if VALIDATE_PENDING_AREA succeeds. This may happen if a plan being deleted is loaded by an instance after a call to VALIDATE_PENDING_AREA, but before a call to SUBMIT_PENDING_AREA.

Syntax

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

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 76-14 SWITCH_CONSUMER_GROUP_FOR_SESS Procedure Parameters

Parameter	Description
`session_id`	SID column from the view `V$SESSION`.
`session_serial`	`SERIAL#` column from view `V$SESSION`.
`consumer_group`	The name of the consumer group to switch to.

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 76-15 SWITCH_CONSUMER_GROUP_FOR_USER Procedure Parameters

Parameter	Description
`user`	The name of the user.
`consumer_group`	The name of the consumer group to switch to.

Usage Notes

The SWITCH_CONSUMER_GROUP_FOR_SESS Procedure 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.

SWITCH_PLAN Procedure

This procedure sets the current resource manager plan.

Syntax

DBMS_RESOURCE_MANAGER.SWITCH_PLAN(
   plan_name     IN     VARCHAR2,
   sid           IN     VARCHAR2 DEFAULT '*');

Parameters

Table 76-16 SWITCH_PLAN_GROUP_FOR_USER Procedure Parameters

Parameter	Description
`plan_name`	The name of the plan to which to switch.
`sid`	The `sid` parameter is relevant only in a Real Application Clusters environment. This parameter lets you to change the plan for a particular instance. Specify the `sid` of the instance where you want to change the plan. Or specify '*' if you want Oracle to change the plan for all instances.

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 76-17 UPDATE_CONSUMER_GROUP Procedure Parameter

Parameter	Description
`consumer_group`	The name of consumer group.
`new_comment`	New user's comment.
`new_cpu_mth`	The name of new method for CPU resource allocation.

Usage Notes

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

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

Parameters

Table 76-18 UPDATE_PLAN Procedure Parameters

Parameter	Description
`plan`	The name of resource plan.
`new_comment`	New user's comment.
`new_cpu_mth`	The name of new allocation method for CPU resources.
`new_active_sess_pool_mth`	The name of new method for maximum active sessions.
`new_parallel_degree_limit_mth`	The name of new method for degree of parallelism.
`new_queueing_mth`	Specifies type of queuing policy to use with active session pool feature.

Usage Notes

If the parameters to UPDATE_PLAN Procedure are not specified, then they remain unchanged in the data dictionary.
If you want to use any default resource allocation method, then you do not need not specify it when creating or updating a plan.

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_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,
               new_max_idle_time             IN NUMBER   DEFAULT NULL,
               new_max_idle_blocker_time     IN NUMBER   DEFAULT NULL,
               new_switch_time_in_call       IN NUMBER   DEFAULT NULL);

Parameters

Table 76-19 UPDATE_PLAN_DIRECTIVE Procedure Parameters

Parameter	Description
`plan`	The name of the resource plan.
`group_or_subplan`	The name of the consumer group or subplan.
`new_comment`	Comment for the plan directive.
`new_cpu_p1`	For `EMPHASIS`, specifies the CPU percentage at the first level. For `RATIO`, specifies the weight of CPU usage. Default is `NULL` for all CPU parameters.
`new_cpu_p2`	For `EMPHASIS`, specifies the CPU percentage at the second level. Not applicable for `RATIO`.
`new_cpu_p3`	For `EMPHASIS`, specifies the CPU percentage at the third level. Not applicable for `RATIO`.
`new_cpu_p4`	For `EMPHASIS`, specifies the CPU percentage at the fourth level. Not applicable for `RATIO`.
`new_cpu_p5`	For `EMPHASIS`, specifies the CPU percentage at the fifth level. Not applicable for `RATIO`.
`new_cpu_p6`	For `EMPHASIS`, specifies the CPU percentage at the sixth level. Not applicable for `RATIO`.
`new_cpu_p7`	For `EMPHASIS`, specifies the CPU percentage at the seventh level. Not applicable for `RATIO`.
`new_cpu_p8`	For `EMPHASIS`, specifies the CPU percentage at the eighth level. Not applicable for `RATIO`.
`new_active_sess_pool_p1`	Specifies maximum number of concurrently active sessions for a consumer group. Default is `NULL`, which means unlimited.
`new_queueing_p1`	Specified time (in seconds) after which a job in the inactive session queue (waiting for execution) will time out. Default is `NULL`, which means unlimited.
`new_switch_group`	Specifies a limit on the degree of parallelism for any operation. Default is `NULL`, which means unlimited.
`new_switch_time`	Specifies consumer group to which this session is switched if other switch criteria is met. Default is `NULL`. If the group name is '`CANCEL_SQL`', the current call will be canceled when other switch criteria are met. If the group name is '`KILL_SESSION`', the session will be killed when other switch criteria are met.
`new_switch_estimate`	Specifies time (in seconds) that a session can execute before an action is taken. Default is `NULL`, which means unlimited.
`new_max_est_exec_time`	If `TRUE`, tells Oracle to use its execution time estimate to automatically switch the consumer group of an operation before beginning its execution. Default is `FALSE`.
`new_undo_pool`	Specifies the maximum execution time (in seconds) allowed for a session. If the optimizer estimates that an operation will take longer than `MAX_EST_EXEC_TIME`, the operation is not started and ORA-07455 is issued. If the optimizer does not provide an estimate, this directive has no effect. Default is `NULL`, which means unlimited.
`new_parallel_degree_limit_p1`	Sets a maximum in kilobytes (`K`) on the total amount of undo generated by a consumer group. Default is `NULL`, which means unlimited.
`new_max_idle_time`	Indicates the maximum session idle time. Default is `NULL`, which means unlimited.
`new_max_idle_blocker_time`	The maximum amount of time in seconds that a session can be idle while blocking another session's acquisition of a resource.
`new_switch_time_in_call`	Specifies time (in seconds) that a session can execute before an action is taken. At the end of the top call, the consumer group of the session is restored to its original consumer group. Default is `NULL`, which means unlimited. Both `SWITCH_TIME_IN_CALL` and `SWITCH_TIME` cannot be specified.

Usage Notes

If the parameters for UPDATE_PLAN_DIRECTIVE are left unspecified, then they remain unchanged in the data dictionary.
For new_max_idle_time and new_max_idle_blocker_time, PMON will check these limits once a minute. If it finds a session that has exceeded one of the limits, it will forcibly kill the session and clean up all its state.
The parameter new_switch_time_in_call is mostly useful for three-tier applications where the mid-tier server is implementing session pooling. By turning on new_switch_time_in_call, the resource usage of one client will not affect the consumer group of a future client that happens to be executed on the same session.

VALIDATE_PENDING_AREA Procedure

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

Syntax

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

76 DBMS_RESOURCE_MANAGER

Using DBMS_RESOURCE_MANAGER

Security Model

Constants

Table 76-1 Constants - Names and Oracle Enterprise Manager Abbreviations

Examples

Table 76-2 MYDB PLAN CPU Plan Directives

Figure 76-1 Resource Manager Scenario

Summary of DBMS_RESOURCE_MANAGER Subprograms

Table 76-3 DBMS_RESOURCE_MANAGER Package Subprograms

CLEAR_PENDING_AREA Procedure

Syntax

CREATE_CONSUMER_GROUP Procedure

Syntax

Parameters

Table 76-4 CREATE_CONSUMER_GROUP Procedure Parameters

CREATE_PENDING_AREA Procedure

Syntax

Usage Notes

CREATE_PLAN Procedure

Syntax

Parameters

Table 76-5 CREATE_PLAN Procedure Parameters

Usage Notes

CREATE_PLAN_DIRECTIVE Procedure

Syntax

Parameters

Table 76-6 CREATE_PLAN_DIRECTIVE Procedure Parameters

Usage Notes

CREATE_SIMPLE_PLAN Procedure

Syntax

DELETE_CONSUMER_GROUP Procedure

Syntax

Parameters

Table 76-7 DELETE_CONSUMER_GROUP Procedure Parameters

DELETE_PLAN Procedure

Syntax

Parameters

Table 76-8 DELETE_PLAN Procedure Parameters

DELETE_PLAN_DIRECTIVE Procedure

Syntax

Parameters

Table 76-9 DELETE_PLAN_DIRECTIVE Procedure Parameters

DELETE_PLAN_CASCADE Procedure

Syntax

Parameters

Table 76-10 DELETE_PLAN_CASCADE Procedure Parameters

Usage Notes

SET_CONSUMER_GROUP_MAPPING Procedure

Syntax

Parameters

Table 76-11 SET_CONSUMER_GROUP_MAPPING Procedure Parameters

Usage Notes

SET_CONSUMER_GROUP_MAPPING_PRI Procedure

Syntax

Parameters

Table 76-12 SET_CONSUMER_GROUP_MAPPING_PRI Procedure Parameters

Usage Notes

SET_INITIAL_CONSUMER_GROUP Procedure

Syntax

Parameters

Table 76-13 SET_INITIAL_CONSUMER_GROUP Procedure Parameters

Usage Notes

SUBMIT_PENDING_AREA Procedure

Syntax

SWITCH_CONSUMER_GROUP_FOR_SESS Procedure

Syntax

Parameters

Table 76-14 SWITCH_CONSUMER_GROUP_FOR_SESS Procedure Parameters

SWITCH_CONSUMER_GROUP_FOR_USER Procedure

Syntax

Parameters

Table 76-15 SWITCH_CONSUMER_GROUP_FOR_USER Procedure Parameters

Usage Notes

SWITCH_PLAN Procedure

Syntax

Parameters

Table 76-16 SWITCH_PLAN_GROUP_FOR_USER Procedure Parameters

UPDATE_CONSUMER_GROUP Procedure

Syntax

76
DBMS_RESOURCE_MANAGER