Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E23448-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

119 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


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:


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


Constants

Table 119-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:

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 using 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 119-2 DBMS_RESOURCE_MANAGER Package Subprograms

Subprogram Description

BEGIN_SQL_BLOCK Procedure

Indicates the start of a block of SQL statements to be treated as a group by resource manager

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

END_SQL_BLOCK Procedure

Indicates the end of a block of SQL statements that should be treated as a group by resource manager

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



BEGIN_SQL_BLOCK Procedure

This procedure, to be used with parallel statement queuing, indicates the start of a block of SQL statements that should be treated as a group by resource manager.

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

Syntax

DBMS_RESOURCE_MANAGER.BEGIN_SQL_BLOCK;

Usage Notes

For more information, see "Parallel Statement Queuing" and "Managing Parallel Statement Queuing with Resource Manager" in Oracle Database VLDB and Partitioning Guide.


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

See Also:

Oracle Database Performance Tuning Guide for more information about calibration

Examples

Example of using I/O Calibration procedure

SET SERVEROUTPUT ON
DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
 
  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
  DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/

View for I/O calibration results

SQL> desc V$IO_CALIBRATION_STATUS
  Name                                      Null?    Type
  ----------------------------------------- -------- ----------------------------
  STATUS                                             VARCHAR2(13)
  CALIBRATION_TIME                                   TIMESTAMP(3)
 
SQL> desc gv$io_calibration_status
  Name                                      Null?    Type
  ----------------------------------------- -------- ----------------------------
  INST_ID                                            NUMBER
  STATUS                                             VARCHAR2(13)
  CALIBRATION_TIME                                   TIMESTAMP(3)
 
Column explanation:
-------------------
STATUS:
  IN PROGRESS   : Calibration in Progress (Results from previous calibration
                  run displayed, if available)
  READY         : Results ready and available from earlier run
  NOT AVAILABLE : Calibration results not available.
 
CALIBRATION_TIME: End time of the last calibration run

DBA table that stores I/O Calibration results

SQL> desc DBA_RSRC_IO_CALIBRATE
  Name                                      Null?    Type
  ----------------------------------------- -------- ----------------------------
  START_TIME                                         TIMESTAMP(6)
  END_TIME                                           TIMESTAMP(6)
  MAX_IOPS                                           NUMBER
  MAX_MBPS                                           NUMBER
  MAX_PMBPS                                          NUMBER
  LATENCY                                            NUMBER
  NUM_PHYSICAL_DISKS                                 NUMBER
 
comment on table DBA_RSRC_IO_CALIBRATE is
'Results of the most recent I/O calibration'
/
comment on column DBA_RSRC_IO_CALIBRATE.START_TIME is
'start time of the most recent I/O calibration'
/
comment on column DBA_RSRC_IO_CALIBRATE.END_TIME is
'end time of the most recent I/O calibration'
/
comment on column DBA_RSRC_IO_CALIBRATE.MAX_IOPS is
'maximum number of data-block read requests that can be sustained per second'
/
comment on column DBA_RSRC_IO_CALIBRATE.MAX_MBPS is
'maximum megabytes per second of maximum-sized read requests that can be
sustained'
/
comment on column DBA_RSRC_IO_CALIBRATE.MAX_PMBPS is
'maximum megabytes per second of large I/O requests that
can be sustained by a single process'
/
comment on column DBA_RSRC_IO_CALIBRATE.LATENCY is
'latency for data-block read requests'
/
comment on column DBA_RSRC_IO_CALIBRATE.NUM_PHYSICAL_DISKS is
'number of physical disks in the storage subsystem (as specified by user)'
/

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.

Syntax

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

Parameters

Table 119-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 119-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 have made are valid. You do not have to perform 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:

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 one or more problems 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 119-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 multilevel plans that use percentages to specify how I/O resources are distributed among consumer groups

  • RATIO - for single-level plans that use ratios to specify how I/O 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 to specify it when creating or updating a plan.


CREATE_PLAN_DIRECTIVE Procedure

This procedure creates resource plan directives.

Note:

The functionality associated with the parallel_target_percentage and parallel_queue_timeout parameters is available starting with Oracle Database 11g Release 2 (11.2.0.2).

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,
   parallel_target_percentage   IN NUMBER   DEFAULT NULL, 
   parallel_queue_timeout       IN NUMBER   DEFAULT NULL); 

Parameters

Table 119-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 CPU 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 CPU 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

Limits the size in kilobytes of the undo records corresponding to uncommitted transactions by this consumer group

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 is set to switch_time_in_call (in seconds) and switch_for_call is effectively set to TRUE. It is better to use switch_time and switch_for_call.

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 the maximum percentage of CPU that this Consumer Group or Sub-Plan can utilize. Valid values are 0% to 100%. NULL implies that there is no limit, or equivalently 100%. You can specify this attribute and leave mgmt_p1 through mgmt_p8 NULL.

parallel_target_percentage

Specifies the maximum percentage of the target number of parallel servers in an Oracle RAC environment that a consumer group can use. Any additional parallel statements that are launched from this consumer group will be queued. The default is NULL, which means that the limit is 100% of the target number. Valid values for queuing are in the range of 0 to 100 (%). For updates to the plan directive, the value of -1 will reset the value to NULL.

If a consumer group does not have any parallel statements running within an Oracle RAC database, the first parallel statement is allowed to exceed this limit.

The target number of parallel servers in an Oracle RAC environment is the sum of the parameter parallel_server_target across all instances.

parallel_queue_timeout

Specifies the time (in seconds) that a query may remain in its Consumer Group's parallel statement queue before it is removed and terminated with an error (ORA- 07454).


Usage Notes


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 119-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 119-9 DELETE_CATEGORY Procedure Parameters

Parameter Description

category

Name of consumer group category



DELETE_CONSUMER_GROUP Procedure

This procedure deletes entries which define resource consumer groups.

Syntax

DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (
   consumer_group IN VARCHAR2); 

Parameters

Table 119-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 119-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 119-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 the operation, 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 119-13 DELETE_PLAN_DIRECTIVE Procedure Parameters

Parameter Description

plan

Name of the resource plan

group_or_subplan

Name of the group or subplan



END_SQL_BLOCK Procedure

This procedure, to be used with parallel statement queuing, indicates the end of a block of SQL statements that should be treated as a group by resource manager.

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

Syntax

DBMS_RESOURCE_MANAGER.END_SQL_BLOCK;

Usage Notes

For more information, see "Parallel Statement Queuing" and "Managing Parallel Statement Queuing with Resource Manager" in Oracle Database VLDB and Partitioning Guide.


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

Parameters Description

attribute

Mapping attribute to add or modify. It can be one of the Constants listed.

value

Attribute value to match. This includes both absolute mapping and regular expressions.

consumer_group

Name of the mapped consumer group, or NULL to delete a mapping


Usage Notes


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


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

Parameters Description

user

Name of the user

consumer_group

User's initial consumer group


Usage Notes


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 119-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 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_USER (
   user            IN VARCHAR2, 
   consumer_group  IN VARCHAR2);

Parameters

Table 119-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


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 119-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 an 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 reenable 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 119-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 119-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_mgmt_mth                       IN VARCHAR2 DEFAULT NULL,
   new_sub_plan                       IN BOOLEAN DEFAULT FALSE); 

Parameters

Table 119-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 multilevel plans that use percentages to specify how I/O resources are distributed among consumer groups.

  • RATIO - for single-level plans that use ratios to specify how I/O resources are distributed.

new_sub_plan

New setting for whether the plan is only intended for use as a sub-plan


Usage Notes


UPDATE_PLAN_DIRECTIVE Procedure

This procedure updates resource plan directives.

Note:

The functionality associated with the new_parallel_target_percentage and new_parallel_queue_timeout parameters is available starting with Oracle Database 11g Release 2 (11.2.0.2).

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,
   new_parallel_target_percentage   IN NUMBER   DEFAULT NULL, 
   new parallel_queue_timeout       IN NUMBER   DEFAULT NULL); 

Parameters

Table 119-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 are 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 CPU 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 CPU 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

Limits the size in kilobytes of the undo records corresponding to uncommitted transactions by this consumer group

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 the maximum percentage of CPU that this Consumer Group or Sub-Plan can utilize. Valid values are 0% to 100%. To unset the limit, use -1.

new_parallel_target_percentage

Specifies the maximum percentage of the target number of parallel servers in an Oracle RAC environment a consumer group can use. Any additional parallel statements that are launched from this consumer group will be queued. The default is NULL, which means that the limit is 100% of the target number. Valid values for queuing are in the range of 0 to 100 (%). For updates to the plan directive, the value of -1 will reset the value to NULL.

If a consumer group does not have any parallel statements running within an Oracle RAC database, the first parallel statement is allowed to exceed this limit.

The target number of parallel servers in an Oracle RAC environment is the sum of the parameter parallel_server_target across all instances.

new_parallel_queue_timeout

Specifies the time (in seconds) that a query may remain in its Consumer Group's parallel statement queue before it is removed and terminated with an error (ORA- 07454).


Usage Notes


VALIDATE_PENDING_AREA Procedure

This procedure validates pending changes for the resource manager.

Syntax

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;