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

Using DBMS_RESOURCE_MANAGER

Deprecated Subprograms
Security Model
Constants

Deprecated Subprograms

Note:

Oracle recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

The following subprograms are deprecated with Oracle Database 11g:

SET_INITIAL_CONSUMER_GROUP Procedure

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 Chapter 117, "DBMS_RESOURCE_MANAGER_PRIVS".

Constants

Table 116-1 DBMS_RESOURCE_MANAGER Constants

Constant	Type	Value	Description
`CLIENT_MACHINE`	`VARCHAR2(30)`	`CLIENT_MACHINE`	Name of the computer from which the client is making the connection
`CLIENT_OS_USER`	`VARCHAR2(30)`	`CLIENT_OS_USER`	Operating system user name of the client that is logging in
`CLIENT_PROGRAM`	`VARCHAR2(30)`	`CLIENT_PROGRAM`	Name of the client program used to log in to the server
`MODULE_NAME`	`VARCHAR2(30)`	`MODULE_NAME`	Module name in the currently running application as set by the SET_MODULE Procedure in the DBMS_APPLICATION_INFO package, or the equivalent OCI attribute setting
`MODULE_NAME_ACTION`	`VARCHAR2(30)`	`MODULE_NAME_ACTION`	A combination of the current module and the action being performed as set by either of the following procedures in the DBMS_APPLICATION_INFO package, or their equivalent OCI attribute setting: SET_MODULE Procedure SET_ACTION Procedure The attribute is specified as the module name followed by a period (.), followed by the action name (module_name.action_name).
`ORACLE_FUNCTION`	`VARCHAR2(30)`	`ORACLE_FUNCTION`	Function the session is currently executing. Valid functions are the BACKUP, `COPY`, and `DATALOAD`. `BACKUP` is set for sessions that are doing backup operations using `RMAN`. `COPY` is set for sessions that are doing image copies via `RMAN`. `DATALOAD` is set for sessions that are loading data using datapump.
`ORACLE_USER`	`VARCHAR2(30)`	`ORACLE_USER`	Oracle Database user name
`SERVICE_MODULE`	`VARCHAR2(30)`	`SERVICE_MODULE`	Combination of service and module names in this form: service_name.module_name
`SERVICE_MODULE_ACTION`	`VARCHAR2(30)`	`SERVICE_MODULE_ACTION`	Combination of service name, module name, and action name, in this form: service_name.module_name.action_name
`SERVICE_NAME`	`VARCHAR2(30)`	`SERVICE_NAME`	Service name used by the client to establish a connection
`PERFORMANCE_CLASS`	`VARCHAR2(30)`	`PERFORMANCE_CLASS`	Oracle Database user name

Summary of DBMS_RESOURCE_MANAGER Subprograms

Table 116-2 DBMS_RESOURCE_MANAGER Package Subprograms

Subprogram	Description
CALIBRATE_IO Procedure	Calibrates the I/O capabilities of storage
CLEAR_PENDING_AREA Procedure	Clears the work area for the resource manager
CREATE_CATEGORY Procedure	Creates a new resource consumer group category
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_CATEGORY Procedure	Deletes an existing resource consumer group category
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 modifies entries 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 (Caution: Deprecated Subprogram)
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_CATEGORY Procedure	Updates an existing resource consumer group category
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 manager

CALIBRATE_IO Procedure

This procedure calibrates the I/O capabilities of storage. Calibration status is available from the V$IO_CALIBRATION_STATUS view and results for a successful calibration run are located in DBA_RSRC_IO_CALIBRATE table.

Syntax

DBMS_RESOURCE_MANAGER.CALIBRATE_IO (
   num_physical_disks      IN  PLS_INTEGER DEFAULT 1,
   max_latency             IN  PLS_INTEGER DEFAULT 20,
   max_iops                OUT PLS_INTEGER,
   max_mbps                OUT PLS_INTEGER,
   actual_latency          OUT PLS_INTEGER);

Parameters

Table 116-3 CALIBRATE_IO Procedure Parameters

Parameter	Description
`num_physical_disks`	Approximate number of physical disks in the database storage
`max_latency`	Maximum tolerable latency in milliseconds for database-block-sized IO requests
`max_iops`	Maximum number of I/O requests per second that can be sustained. The I/O requests are randomly-distributed, database-block-sized reads.
`max_mbps`	Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O requests are randomly-distributed, 1 megabyte reads.
`actual_latency`	Average latency of database-block-sized I/O requests at `max_iops` rate, expressed in milliseconds

Usage Notes

Only users with the SYSDBA privilege can run this procedure. Qualified users must also turn on timed_statistics, and ensure asynch_io is enabled for datafiles. This can be achieved by setting filesystemio_options to either ASYNCH or SETALL. One can also query the asynch_io status by means of the following SQL statement:
```
col name format a50
SELECT name, asynch_io FROM v$datafile f,v$iostat_file i
  WHERE f.file#        = i.file_no
  AND   filetype_name  = 'Data File'
 /
```
Only one calibration can be run at a time. If another calibration is initiated at the same time, it will fail.
For an Oracle Real Application Clusters (RAC) database, the workload is simultaneously generated from all instances.

CLEAR_PENDING_AREA Procedure

This procedure clears pending changes for the resource manager.

Syntax

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

CREATE_CATEGORY Procedure

This procedure creates a new consumer group category. The primary purpose of this attribute is to support Exadata I/O Resource Manager category plans. The view DBA_RSRC_CATEGORIES defines the currently defined categories. The ADMINISTRATIVE, INTERACTIVE, BATCH, MAINTENANCE, and OTHER categories are available out-of-the-box.

Syntax

DBMS_RESOURCE_MANAGER.CREATE_CATEGORY (
   category    IN    VARCHAR2,
   comment     IN    VARCHAR2);

Parameters

Table 116-4 CREATE_CATEGORY Procedure Parameters

Parameter	Description
`category`	Name of consumer group category
`comment`	User's comment

CREATE_CONSUMER_GROUP Procedure

This procedure creates 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 NULL,
   mgmt_mth        IN VARCHAR2 DEFAULT 'ROUND-ROBIN',
   category        IN VARCHAR2 DEFAULT 'OTHER');

Parameters

Table 116-5 CREATE_CONSUMER_GROUP Procedure Parameters

Parameter	Description
`consumer_group`	Name of the consumer group
`comment`	User's comment
`cpu_mth`	Name of CPU resource allocation method (deprecated)
`mgmt_mth`	Name of CPU resource allocation method
`category`	Describes the category of the consumer group. The primary purpose of this attribute is to support Exadata I/O Resource Manager category plans. The view `DBA_RSRC_CATEGORIES` defines the currently defined categories. Categories can be modified, using the CREATE_CATEGORY Procedure, UPDATE_CATEGORY Procedure, and DELETE_CATEGORY Procedure.

CREATE_PENDING_AREA Procedure

This procedure makes 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 28 plan directives coming from any given plan (that is, no plan can have more than 28 children).
There cannot be more than 28 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 NULL, -- deprecated
   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',
   mgmt_mth                   IN   VARCHAR2 DEFAULT 'EMPHASIS',
   sub_plan                   IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 116-6 CREATE_PLAN Procedure Parameters

Parameter	Description
`plan`	Name of the resource plan
`comment`	User's comment
`cpu_mth`	Allocation method for CPU resources (deprecated)
`active_sess_pool_mth`	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`	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`	Queuing resource allocation method. Controls order in which queued inactive sessions will execute. `FIFO_TIMEOUT` is the default and only method available
`mgmt_mth`	Resource allocation method for specifying how much resources (for example, CPU or I/O) each consumer group or sub-plan gets `EMPHASIS` - for multi-level plans that use percentages to specify how I/O is resources are distributed among consumer groups `RATIO` - for single-level plans that use ratios to specify how I/O is resources are distributed
`sub_plan`	If `TRUE`, indicates that this plan is only intended for use as a sub-plan. Sub-plans are not required to have an `OTHER_GROUPS` directive. Default is `FALSE`.

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 creates 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, -- deprecated
   cpu_p2                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p3                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p4                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p5                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p6                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p7                    IN NUMBER   DEFAULT NULL, -- deprecated
   cpu_p8                    IN NUMBER   DEFAULT NULL, -- deprecated
   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, -- deprecated
   mgmt_p1                   IN NUMBER   DEFAULT NULL,
   mgmt_p2                   IN NUMBER   DEFAULT NULL,
   mgmt_p3                   IN NUMBER   DEFAULT NULL,
   mgmt_p4                   IN NUMBER   DEFAULT NULL,
   mgmt_p5                   IN NUMBER   DEFAULT NULL,
   mgmt_p6                   IN NUMBER   DEFAULT NULL,
   mgmt_p7                   IN NUMBER   DEFAULT NULL,
   mgmt_p8                   IN NUMBER   DEFAULT NULL,
   switch_io_megabytes       IN NUMBER   DEFAULT NULL,
   switch_io_reqs            IN NUMBER   DEFAULT NULL,
   switch_for_call           IN BOOLEAN  DEFAULT NULL,
   max_utilization_limit     IN NUMBER   DEFAULT NULL);

Parameters

Table 116-7 CREATE_PLAN_DIRECTIVE Procedure Parameters

Parameter	Description
`plan`	Name of the resource plan
`group_or_subplan`	Name of the consumer group or subplan
`comment`	Comment for the plan directive
`cpu_p1`	-- deprecated: use `mgmt_p1` instead
`cpu_p2`	-- deprecated: use `mgmt_p2` instead)
`cpu_p3`	-- deprecated: use `mgmt_p3` instead)
`cpu_p4`	-- deprecated: use `mgmt_p4` instead)
`cpu_p5`	-- deprecated: use `mgmt_p5` instead)
`cpu_p6`	-- deprecated: use `mgmt_p6` instead)
`cpu_p7`	-- deprecated: use `mgmt_p7` instead)
`cpu_p8`	-- deprecated: use `mgmt_p8` instead)
`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 switch to, once a switch condition is met. If the group name is '`CANCEL_SQL`', then the current call is canceled when the switch condition is met. If the group name is '`KILL_SESSION`', then the session is killed when the switch condition is met. Default is `NULL`.
`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`	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`	Deprecated. If this parameter is specified, `switch_time` will be effectively set to `switch_time_in_call` and `switch_for_call` will be effectively set to `TRUE`.
`mgmt_p1`	Resource allocation value for level 1 (replaces `cpu_p1`): `EMPHASIS` - specifies the resource percentage at the first level `RATIO` - specifies the weight of resource usage
`mgmt_p2`	Resource allocation value for level 2 (replaces `cpu_p2`) `EMPHASIS` - specifies the resource percentage at the second level `RATIO` - non-applicable
`mgmt_p3`	Resource allocation value for level 3 (replaces `cpu_p3`) `EMPHASIS` - specifies the resource percentage at the third level `RATIO` - non-applicable
`mgmt_p4`	Resource allocation value for level 4 (replaces `cpu_p4`) `EMPHASIS` - specifies the resource percentage at the fourth level `RATIO` - non-applicable
`mgmt_p5`	Resource allocation value for level 5 (replaces `cpu_p5`) `EMPHASIS` - specifies the resource percentage at the fifth level `RATIO` - non-applicable
`mgmt_p6`	Resource allocation value for level 6 (replaces `cpu_p6`) `EMPHASIS` - specifies the resource percentage at the sixth level `RATIO` - non-applicable
`mgmt_p7`	Resource allocation value for level 7 (replaces `cpu_p7`) `EMPHASIS` - specifies the resource percentage at the seventh level `RATIO` - non-applicable
`mgmt_p8`	Resource allocation value for level 8 (replaces `cpu_p8`) `EMPHASIS` - specifies the resource percentage at the eighth level `RATIO` - non-applicable
`switch_io_megabytes`	Specifies the amount of I/O (in MB) that a session can issue before an action is taken. Default is `NULL`, which means unlimited.
`switch_io_reqs`	Specifies the number of I/O requests that a session can issue before an action is taken. Default is `NULL`, which means unlimited.
`switch_for_call`	Specifies that if an action is taken because of the `switch_time`, `switch_io_megabytes`, or `switch_io_reqs` parameters, the consumer group is restored to its original consumer group at the end of the top call. Default is `FALSE`, which means that the original consumer group is not restored at the end of the top call.
`max_utilization_limit`	Specifies absolute maximum CPU utilization percentage permitted for the consumer group. This value overrides any level allocations for CPU (`mgmt_p1` through `mgmt_p8`), and also imposes a limit on total CPU utilization when unused allocations are redistributed. You can specify this attribute and leave `mgmt_p1` through `mgmt_p8` `NULL`. You cannot specify this attribute for a subplan.

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 NULL,
   consumer_group1  IN  VARCHAR2  DEFAULT NULL,
   group1_cpu       IN  NUMBER    DEFAULT NULL,   -- deprecated
   consumer_group2  IN  VARCHAR2  DEFAULT NULL,
   group2_cpu       IN  NUMBER    DEFAULT NULL,   -- deprecated
   consumer_group3  IN  VARCHAR2  DEFAULT NULL,
   group3_cpu       IN  NUMBER    DEFAULT NULL,   -- deprecated
   consumer_group4  IN  VARCHAR2  DEFAULT NULL,
   group4_cpu       IN  NUMBER    DEFAULT NULL,   -- deprecated
   consumer_group5  IN  VARCHAR2  DEFAULT NULL,
   group5_cpu       IN  NUMBER    DEFAULT NULL,   -- deprecated
   consumer_group6  IN  VARCHAR2  DEFAULT NULL,
   group6_cpu       IN  NUMBER    DEFAULT NULL,   -- deprecated
   consumer_group7  IN  VARCHAR2  DEFAULT NULL,
   group7_cpu       IN  NUMBER    DEFAULT NULL,   -- deprecated
   consumer_group8  IN  VARCHAR2  DEFAULT NULL,
   group8_cpu       IN  NUMBER    DEFAULT NULL,   -- deprecated
   group1_percent   IN  NUMBER    DEFAULT NULL,
   group2_percent   IN  NUMBER    DEFAULT NULL,
   group3_percent   IN  NUMBER    DEFAULT NULL,
   group4_percent   IN  NUMBER    DEFAULT NULL,
   group5_percent   IN  NUMBER    DEFAULT NULL,
   group6_percent   IN  NUMBER    DEFAULT NULL,
   group7_percent   IN  NUMBER    DEFAULT NULL,
   group8_percent   IN  NUMBER    DEFAULT NULL);

Parameters

Table 116-8 CREATE_SIMPLE_PLAN Procedure Parameters

Parameter	Description
`simple_plan`	Name of the resource plan
`consumer_group1`	Name of the consumer group
`group1_cpu`	Percentage for group (deprecated)
`consumer_group2`	Name of the consumer group
`group2_cpu`	Percentage for group (deprecated)
`consumer_group3`	Name of the consumer group
`group3_cpu`	Percentage for group (deprecated)
`consumer_group4`	Name of the consumer group
`group4_cpu`	Percentage for group (deprecated)
`consumer_group5`	Name of the consumer group
`group5_cpu`	Percentage for group (deprecated)
`consumer_group6`	Name of the consumer group
`group6_cpu`	Percentage for group (deprecated)
`consumer_group7`	Name of the consumer group
`group7_cpu`	Percentage for group (deprecated)
`consumer_group8`	Name of the consumer group
`group8_cpu`	Percentage for group (deprecated)
`group1_percent`	Percentage of resources allocated for this consumer group
`group2_percent`	Percentage of resources allocated for this consumer group
`group3_percent`	Percentage of resources allocated for this consumer group
`group4_percent`	Percentage of resources allocated for this consumer group
`group5_percent`	Percentage of resources allocated for this consumer group
`group6_percent`	Percentage of resources allocated for this consumer group
`group7_percent`	Percentage of resources allocated for this consumer group
`group8_percent`	Percentage of resources allocated for this consumer group

DELETE_CATEGORY Procedure

This procedure deletes an existing resource consumer group category.

Syntax

DBMS_RESOURCE_MANAGER.DELETE_CATEGORY (
   category        IN    VARCHAR2,
   new_comment     IN    VARCHAR2  DEFAULT NULL);

Parameters

Table 116-9 DELETE_CATEGORY Procedure Parameters

Parameter	Description
`category`	Name of consumer group category

DELETE_CONSUMER_GROUP Procedure

This procedure delete entries which define resource consumer groups.

Syntax

DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (
   consumer_group IN VARCHAR2);

Parameters

Table 116-10 DELETE_CONSUMER_GROUP Procedure Parameters

Parameters	Description
`consumer_group`	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 116-11 DELETE_PLAN Procedure Parameters

Parameter	Description
`plan`	Name of the resource plan to delete

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 116-12 DELETE_PLAN_CASCADE Procedure Parameters

Parameters	Description
`plan`	Name of the plan

Usage Notes

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

DELETE_PLAN_DIRECTIVE Procedure

This procedure deletes resource plan directives.

Syntax

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

Parameters

Table 116-13 DELETE_PLAN_DIRECTIVE Procedure Parameters

Parameter	Description
`plan`	Name of the resource plan
`group_or_subplan`	Name of the group or subplan

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 116-14 SET_CONSUMER_GROUP_MAPPING Procedure Parameters

Parameters	Description
`attribute`	Mapping attribute to add/modify. It can be one of the Constants listed.
`value`	Attribute value to match
`consumer_group`	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 116-15 SET_CONSUMER_GROUP_MAPPING_PRI Procedure Parameters

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

Usage Notes

This procedure requires that you include the pseudo-attribute explicit as an argument. It must be set to 1. It indicates that explicit consumer group switches have the highest priority. You explicitly switch consumer groups with these package procedures:
- DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP
- DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS
- DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER
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

Note:

This procedure is deprecated in Release 11gR1. While the procedure remains available in the package, Initial Consumer Group is set by the session-to-consumer group mapping rules.

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 116-16 SET_INITIAL_CONSUMER_GROUP Procedure Parameters

Parameters	Description
`user`	Name of the user
`consumer_group`	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 submits 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 changes 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 116-17 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`	Name of the consumer group to which to switch

SWITCH_CONSUMER_GROUP_FOR_USER Procedure

This procedure changes 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 116-18 SWITCH_CONSUMER_GROUP_FOR_USER Procedure Parameters

Parameter	Description
`user`	Name of the user
`consumer_group`	Name of the consumer group to which to switch

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 '*',
   allow_scheduler_plan_switches IN   BOOLEAN DEFAULT TRUE);

Parameters

Table 116-19 SWITCH_PLAN Procedure Parameters

Parameter	Description
`plan_name`	Name of the plan to which to switch. Passing in an empty string ('') for the `plan_name`, disables the resource manager
`sid`	The `sid` parameter is relevant only in a Oracle Real Application Clusters environment. This parameter lets you 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.
`allow_scheduler_plan_switches`	`FALSE` - disables automated plan switches by the job scheduler at window boundaries. To re-enable automated plan switches, switch_plan must be called again by the administrator with `allow_scheduler_plan_switches` set to `TRUE`. By default automated plan switches by the job scheduler are enabled.

UPDATE_CATEGORY Procedure

This procedure updates an existing resource consumer group category.

Syntax

DBMS_RESOURCE_MANAGER.UPDATE_CATEGORY (
   category        IN    VARCHAR2,
   new_comment     IN    VARCHAR2  DEFAULT NULL);

Parameters

Table 116-20 UPDATE_CATEGORY Procedure Parameters

Parameter	Description
`category`	Name of consumer group category
`new_comment`	User's comment

UPDATE_CONSUMER_GROUP Procedure

This procedure updates 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,
   new_mgmt_mth    IN VARCHAR2 DEFAULT NULL,
   new_category    IN VARCHAR2 DEFAULT NULL);

Parameters

Table 116-21 UPDATE_CONSUMER_GROUP Procedure Parameter

Parameter	Description
`consumer_group`	Name of consumer group
`new_comment`	New user's comment
`new_cpu_mth`	Name of new method for CPU resource allocation (deprecated)
`new_mgmt_mth`	Name of new method for CPU resource allocation
`new_category`	New consumer group category

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, -- deprecated
   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,
   new_queueing_mth                   IN VARCHAR2 DEFAULT NULL,
   new_mgmt_mth                       IN VARCHAR2 DEFAULT NULL,
   new_sub_plan                       IN BOOLEAN DEFAULT FALSE);

Parameters

Table 116-22 UPDATE_PLAN Procedure Parameters

Parameter	Description
`plan`	Name of resource plan
`new_comment`	New user's comment
`new_cpu_mth`	Name of new allocation method for CPU resources (deprecated)
`new_active_sess_pool_mth`	Name of new method for maximum active sessions
`new_parallel_degree_limit_mth`	Name of new method for degree of parallelism
`new_queueing_mth`	Specifies type of queuing policy to use with active session pool feature
`new_mgmt_mth`	Resource allocation method for specifying how much resources (for example, CPU or I/O) each consumer group or sub-plan gets `EMPHASIS` - for multi-level plans that use percentages to specify how I/O is resources are distributed among consumer groups. `RATIO` - for single-level plans that use ratios to specify how I/O is resources are distributed.
`new_sub_plan`	New setting for whether the plan is only intended for use as a sub-plan

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 updates 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, -- deprecated 
   new_cpu_p2                    IN NUMBER   DEFAULT NULL, -- deprecated 
   new_cpu_p3                    IN NUMBER   DEFAULT NULL, -- deprecated 
   new_cpu_p4                    IN NUMBER   DEFAULT NULL, -- deprecated 
   new_cpu_p5                    IN NUMBER   DEFAULT NULL, -- deprecated 
   new_cpu_p6                    IN NUMBER   DEFAULT NULL, -- deprecated 
   new_cpu_p7                    IN NUMBER   DEFAULT NULL, -- deprecated 
   new_cpu_p8                    IN NUMBER   DEFAULT NULL, -- deprecated 
   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,
   switch_time_in_call           IN NUMBER   DEFAULT NULL, -- deprecated
   new_mgmt_p1                   IN NUMBER   DEFAULT NULL,
   new_mgmt_p2                   IN NUMBER   DEFAULT NULL,
   new_mgmt_p3                   IN NUMBER   DEFAULT NULL,
   new_mgmt_p4                   IN NUMBER   DEFAULT NULL,
   new_mgmt_p5                   IN NUMBER   DEFAULT NULL,
   new_mgmt_p6                   IN NUMBER   DEFAULT NULL,
   new_mgmt_p7                   IN NUMBER   DEFAULT NULL,
   new_mgmt_p8                   IN NUMBER   DEFAULT NULL,
   new_switch_io_megabytes       IN NUMBER   DEFAULT NULL,
   new_switch_io_reqs            IN NUMBER   DEFAULT NULL,
   new_switch_for_call           IN BOOLEAN  DEFAULT NULL,
   new_max_utilization_limit     IN NUMBER DEFAULT NULL);

Parameters

Table 116-23 UPDATE_PLAN_DIRECTIVE Procedure Parameters

Parameter	Description
`plan`	Name of the resource plan
`group_or_subplan`	Name of the consumer group or subplan
`new_comment`	Comment for the plan directive
`new_cpu_p1`	First parameter for the CPU resources allocation method ((deprecated - use `new_mgmt_p1` instead)
`new_cpu_p2`	Parameter for the CPU resources allocation method ((deprecated - use `new_mgmt_p2` instead)
`new_cpu_p3`	Parameter for the CPU resources allocation method (deprecated - use `new_mgmt_p3` instead)
`new_cpu_p4`	Parameter for the CPU resources allocation method (deprecated- use `new_mgmt_p4` instead)
`new_cpu_p5`	Parameter for the CPU resources allocation method (deprecated - use `new_mgmt_p5` instead)
`new_cpu_p6`	Parameter for the CPU resources allocation method (deprecated- use `new_mgmt_p6` instead)
`new_cpu_p7`	Parameter for the CPU resources allocation method (deprecated- use `new_mgmt_p7` instead)
`new_cpu_p8`	Parameter for the CPU resources allocation method (deprecated- use `new_mgmt_p8` instead)
`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_parallel_degree_limit_p1`	Specifies a limit on the degree of parallelism for any operation. Default is `NULL`, which means unlimited.
`new_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.
`new_switch_time`	Specifies time (in seconds) that a session can execute before an action is taken. Default is `NULL`, which means unlimited.
`new_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`.
`new_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.
`new_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.
`new_max_idle_time`	Indicates the maximum session idle time. Default is `NULL`, which means unlimited.
`new_max_idle_blocker_time`	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`	Deprecated. If this parameter is specified, `new_switch_time` will be effectively set to `new_switch_time_in_call` and `new_switch_for_call` will be effectively set to `TRUE`.
`new_mgmt_p1`	Resource allocation value for level 1 (replaces `new_cpu_p1`): `EMPHASIS` - specifies the resource percentage at the first level `RATIO` - specifies the weight of resource usage
`new_mgmt_p2`	Resource allocation value for level 2 (replaces `new_cpu_p2`) `EMPHASIS` - specifies the resource percentage at the second level `RATIO` - non-applicable
`new_mgmt_p3`	Resource allocation value for level 3 (replaces `new_cpu_p3`) `EMPHASIS` - specifies the resource percentage at the third level `RATIO` - non-applicable
`new_mgmt_p4`	Resource allocation value for level 4 (replaces `new_cpu_p4`) `EMPHASIS` - specifies the resource percentage at the fourth level `RATIO` - non-applicable
`new_mgmt_p5`	Resource allocation value for level 5 (replaces `new_cpu_p5`) `EMPHASIS` - specifies the resource percentage at the fifth level `RATIO` - non-applicable
`new_mgmt_p6`	Resource allocation value for level 6 (replaces `new_cpu_p6`) `EMPHASIS` - specifies the resource percentage at the sixth level `RATIO` - non-applicable
`new_mgmt_p7`	Resource allocation value for level 7 (replaces `new_cpu_p7`) `EMPHASIS` - specifies the resource percentage at the seventh level `RATIO` - non-applicable
`new_mgmt_p8`	Resource allocation value for level 8 (replaces `new_cpu_p8`) `EMPHASIS` - specifies the resource percentage at the eighth level `RATIO` - non-applicable
`new_switch_io_megabytes`	Specifies the amount of I/O (in MB) that a session can issue before an action is taken. Default is `NULL`, which means unlimited.
`new_switch_io_reqs`	Specifies the number of I/O requests that a session can issue before an action is taken. Default is `NULL`, which means unlimited.
`new_switch_for_call`	Specifies that if an action is taken because of the `new_switch_time`, `new_switch_io_megabytes`, or `new_switch_io_reqs` parameters, the consumer group is restored to its original consumer group at the end of the top call. Default is `FALSE`, which means that the original consumer group is not restored at the end of the top call.
`new_max_utilization_limit`	Specifies absolute maximum CPU utilization percentage permitted for the consumer group. This value overrides any level allocations for CPU (`mgmt_p1` through `mgmt_p8`), and also imposes a limit on total CPU utilization when unused allocations are redistributed. You can specify this attribute and leave `mgmt_p1` through `mgmt_p8` `NULL`. You cannot specify this attribute for a subplan.

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.
To clear (zero or nullify) any numeric parameter in a resource plan directive, set it to -1 using the UPDATE_PLAN_DIRECTIVE Procedure.

VALIDATE_PENDING_AREA Procedure

This procedure validates pending changes for the resource manager.

Syntax

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;