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:
Deprecated Subprograms
Security Model
Constants
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:
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 121, "DBMS_RESOURCE_MANAGER_PRIVS".
Table 120-1 DBMS_RESOURCE_MANAGER Constants
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
|
Name of the computer from which the client is making the connection |
|
|
|
|
Operating system user name of the client that is logging in |
|
|
|
|
Name of the client program used to log in to the server |
|
|
|
|
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 |
|
|
|
|
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). |
|
|
|
|
Function the session is currently executing. Valid functions are the |
|
|
|
|
Oracle Database user name |
|
|
|
|
Combination of service and module names in this form: service_name.module_name |
|
|
|
|
Combination of service name, module name, and action name, in this form: service_name.module_name.action_name |
|
|
|
|
Service name used by the client to establish a connection |
|
|
|
|
Oracle Database user name |
Table 120-2 DBMS_RESOURCE_MANAGER Package Subprograms
| Subprogram | Description |
|---|---|
|
Indicates the start of a block of SQL statements to be treated as a group by resource manager |
|
|
Calibrates the I/O capabilities of storage |
|
|
Clears the work area for the resource manager |
|
|
Creates a new resource consumer group category |
|
|
Creates entries which define resource consumer groups |
|
|
Creates a work area for changes to resource manager objects |
|
|
Creates entries which define resource plans |
|
|
Creates resource plan directives |
|
|
Creates a single-level resource plan containing up to eight consumer groups in one step |
|
|
Deletes an existing resource consumer group category |
|
|
Deletes entries which define resource consumer groups |
|
|
Deletes the specified plan as well as all the plan directives it refers to |
|
|
Deletes the specified plan as well as all its descendants (plan directives, subplans, consumer groups) |
|
|
Deletes resource plan directives |
|
|
Indicates the end of a block of SQL statements that should be treated as a group by resource manager |
|
|
Adds, deletes, or modifies entries for the login and run-time attribute mappings |
|
|
Creates the session attribute mapping priority list |
|
|
Assigns the initial resource consumer group for a user (Caution: Deprecated Subprogram) |
|
|
Submits pending changes for the resource manager |
|
|
Changes the resource consumer group of a specific session |
|
|
Changes the resource consumer group for all sessions with a given user name |
|
|
Sets the current resource manager plan |
|
|
Updates an existing resource consumer group category |
|
|
Updates entries which define resource consumer groups |
|
|
Updates entries which define resource plans |
|
|
Updates resource plan directives |
|
|
Validates pending changes for the resource manager |
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).For more information, see "Parallel Statement Queuing" and "Managing Parallel Statement Queuing with Resource Manager" in Oracle Database VLDB and Partitioning Guide.
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.
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);
Table 120-3 CALIBRATE_IO Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Approximate number of physical disks in the database storage |
|
|
Maximum tolerable latency in milliseconds for database-block-sized IO requests |
|
|
Maximum number of I/O requests per second that can be sustained. The I/O requests are randomly-distributed, database-block-sized reads. |
|
|
Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O requests are randomly-distributed, 1 megabyte reads. |
|
|
Average latency of database-block-sized I/O requests at |
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 (Oracle RAC) database, the workload is simultaneously generated from all instances.
See Also:
Oracle Database Performance Tuning Guide for more information about calibrationExample 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)' /
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.
This procedure creates entries which define resource consumer groups.
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');
Table 120-5 CREATE_CONSUMER_GROUP Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the consumer group |
|
|
User's comment |
|
|
Name of CPU resource allocation method (deprecated) |
|
|
Name of CPU resource allocation method |
|
|
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 |
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.
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:
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.
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 one or more problems and reissue the validate or submit procedures.
This procedure creates entries which define resource plans.
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,
max_iops IN NUMBER DEFAULT NULL,
max_mbps IN NUMBER DEFAULT NULL);
Table 120-6 CREATE_PLAN Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the resource plan |
|
|
User's comment |
|
|
Allocation method for CPU resources (deprecated) |
|
|
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. |
|
|
Resource allocation method for specifying a limit on the degree of parallelism of any operation. |
|
|
Queuing resource allocation method. Controls order in which queued inactive sessions will execute. |
|
|
Resource allocation method for specifying how much resources (for example, CPU or I/O) each consumer group or sub-plan gets
|
|
|
If |
|
|
Nonoperative |
|
|
Nonoperative |
This procedure creates resource plan directives.
Note:
The functionality associated with theparallel_target_percentage and parallel_queue_timeout parameters is available starting with Oracle Database 11g Release 2 (11.2.0.2).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);
Table 120-7 CREATE_PLAN_DIRECTIVE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the resource plan |
|
|
Name of the consumer group or subplan |
|
|
Comment for the plan directive |
|
|
-- deprecated: use |
|
|
-- deprecated: use |
|
|
-- deprecated: use |
|
|
-- deprecated: use |
|
|
-- deprecated: use |
|
|
-- deprecated: use |
|
|
-- deprecated: use |
|
|
-- deprecated: use |
|
|
Specifies maximum number of concurrently active sessions for a consumer group. Default is |
|
|
Specified time (in seconds) after which a job in the inactive session queue (waiting for execution) will time out. Default is |
|
|
Specifies a limit on the degree of parallelism for any operation. Default is |
|
|
Specifies consumer group to switch to, once a switch condition is met. If the group name is ' |
|
|
Specifies time (in CPU seconds) that a session can execute before an action is taken. Default is |
|
|
If |
|
|
Specifies the maximum execution time (in CPU seconds) allowed for a session. If the optimizer estimates that an operation will take longer than |
|
|
Limits the size in kilobytes of the undo records corresponding to uncommitted transactions by this consumer group |
|
|
Indicates the maximum session idle time. Default is |
|
|
Maximum amount of time in seconds that a session can be idle while blocking another session's acquisition of a resource |
|
|
Deprecated. If this parameter is specified, |
|
|
Resource allocation value for level 1 (replaces
|
|
|
Resource allocation value for level 2 (replaces
|
|
|
Resource allocation value for level 3 (replaces
|
|
|
Resource allocation value for level 4 (replaces
|
|
|
Resource allocation value for level 5 (replaces
|
|
|
Resource allocation value for level 6 (replaces
|
|
|
Resource allocation value for level 7 (replaces
|
|
|
Resource allocation value for level 8 (replaces
|
|
|
Specifies the amount of I/O (in MB) that a session can issue before an action is taken. Default is |
|
|
Specifies the number of I/O requests that a session can issue before an action is taken. Default is |
|
|
Specifies that if an action is taken because of the |
|
|
Specifies the maximum percentage of CPU that this Consumer Group or Sub-Plan can utilize. Valid values are 0% to 100%. |
|
|
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 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 |
|
|
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). |
All parameters default to NULL. However, for the EMPHASIS CPU resource allocation method, this case would severely limit resources to 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.
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.
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);
Table 120-8 CREATE_SIMPLE_PLAN Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the resource plan |
|
|
Name of the consumer group |
|
|
Percentage for group (deprecated) |
|
|
Name of the consumer group |
|
|
Percentage for group (deprecated) |
|
|
Name of the consumer group |
|
|
Percentage for group (deprecated) |
|
|
Name of the consumer group |
|
|
Percentage for group (deprecated) |
|
|
Name of the consumer group |
|
|
Percentage for group (deprecated) |
|
|
Name of the consumer group |
|
|
Percentage for group (deprecated) |
|
|
Name of the consumer group |
|
|
Percentage for group (deprecated) |
|
|
Name of the consumer group |
|
|
Percentage for group (deprecated) |
|
|
Percentage of resources allocated for this consumer group |
|
|
Percentage of resources allocated for this consumer group |
|
|
Percentage of resources allocated for this consumer group |
|
|
Percentage of resources allocated for this consumer group |
|
|
Percentage of resources allocated for this consumer group |
|
|
Percentage of resources allocated for this consumer group |
|
|
Percentage of resources allocated for this consumer group |
|
|
Percentage of resources allocated for this consumer group |
This procedure deletes an existing resource consumer group category.
DBMS_RESOURCE_MANAGER.DELETE_CATEGORY ( category IN VARCHAR2, new_comment IN VARCHAR2 DEFAULT NULL);
This procedure deletes entries which define resource consumer groups.
This procedure deletes the specified plan as well as all the plan directives to which it refers.
This procedure deletes the specified plan and all of its descendants (plan directives, subplans, consumer groups). Mandatory objects and directives are not deleted.
This procedure deletes resource plan directives.
DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE ( plan IN VARCHAR2, group_or_subplan IN VARCHAR2);
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).For more information, see "Parallel Statement Queuing" and "Managing Parallel Statement Queuing with Resource Manager" in Oracle Database VLDB and Partitioning Guide.
This procedure adds, deletes, or modifies entries that map sessions to consumer groups, based on the session's login and runtime attributes.
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( attribute IN VARCHAR2, value IN VARCHAR2, consumer_group IN VARCHAR2 DEFAULT NULL);
Table 120-14 SET_CONSUMER_GROUP_MAPPING Procedure Parameters
| Parameters | Description |
|---|---|
|
|
Mapping attribute to add or modify. It can be one of the Constants listed. |
|
|
Attribute value to match. This includes both absolute mapping and regular expressions. |
|
|
Name of the mapped consumer group, or |
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.
The subprogram supports simple regex expressions for the value parameter. It implements the same semantics as the SQL 'LIKE' operator. Specifically, it uses '%' as amulticharacter wildcard and '_' as a single character wildcard. The '\' character can be used to escape the wildcards. Note that wildcards can only be used if the attribute is one of the following:
CLIENT_OS_USER
CLIENT_PROGRAM
CLIENT_MACHINE
MODULE_NAME
MODULE_NAME_ACTION
SERVICE_MODULE
SERVICE_MODULE_ACTION
Consumer group mapping comparisons for DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM are performed by stripping the @ sign and and following characters from V$SESSION.PROGRAM before comparing it to to the CLIENT_PROGRAM value supplied.
Multiple attributes of a session can be used to map the session to a consumer group. This procedure prioritizes the attribute mappings.
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);
Table 120-15 SET_CONSUMER_GROUP_MAPPING_PRI Procedure Parameters
| Parameters | Description |
|---|---|
|
|
Priority of the explicit mapping |
|
|
Priority of the Oracle user name mapping |
|
|
Priority of the client service name mapping |
|
|
Priority of the client operating system user name mapping |
|
|
Priority of the client program mapping |
|
|
Priority of the client machine mapping |
|
|
Priority of the application module name mapping |
|
|
Priority of the application module name and action mapping |
|
|
Priority of the service name and application module name mapping |
|
|
Priority of the service name, application module name, and application action mapping |
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.
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.
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP ( user IN VARCHAR2, consumer_group IN VARCHAR2);
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 forALTER 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.
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 toSUBMIT_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.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.
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ( session_id IN NUMBER, session_serial IN NUMBER, consumer_group IN VARCHAR2);
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.
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ( user IN VARCHAR2, consumer_group IN VARCHAR2);
The SWITCH_CONSUMER_GROUP_FOR_SESS Procedure and the SWITCH_CONSUMER_GROUP_FOR_USER procedures let you 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.
This procedure sets the current resource manager plan.
DBMS_RESOURCE_MANAGER.SWITCH_PLAN( plan_name IN VARCHAR2, sid IN VARCHAR2 DEFAULT '*', allow_scheduler_plan_switches IN BOOLEAN DEFAULT TRUE);
Table 120-19 SWITCH_PLAN Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the plan to which to switch. Passing in an empty string ('') for the |
|
|
The |
|
|
|
This procedure updates an existing resource consumer group category.
DBMS_RESOURCE_MANAGER.UPDATE_CATEGORY ( category IN VARCHAR2, new_comment IN VARCHAR2 DEFAULT NULL);
This procedure updates entries which define resource consumer groups.
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);
Table 120-21 UPDATE_CONSUMER_GROUP Procedure Parameter
| Parameter | Description |
|---|---|
|
|
Name of consumer group |
|
|
New user's comment |
|
|
Name of new method for CPU resource allocation (deprecated) |
|
|
Name of new method for CPU resource allocation |
|
|
New consumer group category |
This procedure updates entries which define resource plans.
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);
Table 120-22 UPDATE_PLAN Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of resource plan |
|
|
New user's comment |
|
|
Name of new allocation method for CPU resources (deprecated) |
|
|
Name of new method for maximum active sessions |
|
|
Name of new method for degree of parallelism |
|
|
Specifies type of queuing policy to use with active session pool feature |
|
|
Resource allocation method for specifying how much resources (for example, CPU or I/O) each consumer group or sub-plan gets
|
|
|
New setting for whether the plan is only intended for use as a sub-plan |
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 to specify it when creating or updating a plan.
This procedure updates resource plan directives.
Note:
The functionality associated with thenew_parallel_target_percentage and new_parallel_queue_timeout parameters is available starting with Oracle Database 11g Release 2 (11.2.0.2).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);
Table 120-23 UPDATE_PLAN_DIRECTIVE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the resource plan |
|
|
Name of the consumer group or subplan |
|
|
Comment for the plan directive |
|
|
First parameter for the CPU resources allocation method ((deprecated - use |
|
|
Parameter for the CPU resources allocation method ((deprecated - use |
|
|
Parameter for the CPU resources allocation method (deprecated - use |
|
|
Parameter for the CPU resources allocation method (deprecated- use |
|
|
Parameter for the CPU resources allocation method (deprecated - use |
|
|
Parameter for the CPU resources allocation method (deprecated- use |
|
|
Parameter for the CPU resources allocation method (deprecated- use |
|
|
Parameter for the CPU resources allocation method (deprecated- use |
|
|
Specifies maximum number of concurrently active sessions for a consumer group. Default is |
|
|
Specified time (in seconds) after which a job in the inactive session queue (waiting for execution) will time out. Default is |
|
|
Specifies a limit on the degree of parallelism for any operation. Default is |
|
|
Specifies consumer group to which this session is switched if other switch criteria are met. Default is |
|
|
Specifies time (in CPU seconds) that a session can execute before an action is taken. Default is |
|
|
If |
|
|
Specifies the maximum execution time (in CPU seconds) allowed for a session. If the optimizer estimates that an operation will take longer than |
|
|
Limits the size in kilobytes of the undo records corresponding to uncommitted transactions by this consumer group |
|
|
Indicates the maximum session idle time. Default is |
|
|
Maximum amount of time in seconds that a session can be idle while blocking another session's acquisition of a resource |
|
|
Deprecated. If this parameter is specified, |
|
|
Resource allocation value for level 1 (replaces
|
|
|
Resource allocation value for level 2 (replaces
|
|
|
Resource allocation value for level 3 (replaces
|
|
|
Resource allocation value for level 4 (replaces
|
|
|
Resource allocation value for level 5 (replaces
|
|
|
Resource allocation value for level 6 (replaces
|
|
|
Resource allocation value for level 7 (replaces
|
|
|
Resource allocation value for level 8 (replaces
|
|
|
Specifies the amount of I/O (in MB) that a session can issue before an action is taken. Default is |
|
|
Specifies the number of I/O requests that a session can issue before an action is taken. Default is |
|
|
Specifies that if an action is taken because of the |
|
|
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. |
|
|
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 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 |
|
|
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). |
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.