Oracle Database Resource Manager (the Resource Manager) enables you to optimize resource allocation among the many concurrent database sessions.
Resource consumer group
A group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions.
Resource plan
A container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.
Resource plan directive
Associates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.
Resource Consumer Groups
A resource consumer group (consumer group) is a collection of user sessions that are grouped together based on their processing needs. When a session is created, it is automatically mapped to a consumer group based on mapping rules that you set up. As a database administrator (DBA), you can manually switch a session to a different consumer group. Similarly, an application can run a PL/SQL package procedure that switches its session to a particular consumer group.
Because the Resource Manager allocates resources (such as CPU) only to consumer groups, when a session becomes a member of a consumer group, its resource allocation is then determined by the allocation for the consumer group. By default, each session in a consumer group shares the resources allocated to that group with other sessions in the group in a round robin fashion.
There are three special consumer groups that are always present in the data dictionary. They cannot be modified or deleted. They are:
- SYS_GROUP
- This is the initial consumer group for all sessions created by user accounts SYS or SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules.
- DEFAULT_CONSUMER_GROUP
- This is the initial consumer group for all sessions started by user accounts other than SYS and SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules. DEFAULT_CONSUMER_GROUP cannot be named in a resource plan directive.
- OTHER_GROUPS
- This group applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan, including sessions that belong to DEFAULT_CONSUMER_GROUP. OTHER_GROUPS must have a resource plan directive specified in every plan. It cannot be explicitly assigned to sessions through mapping rules.
Procedure
|
Description
|
GRANT_SYSTEM_PRIVILEGE
|
Grants the ADMINISTER_RESOURCE_MANAGER system privilege to a user or role.
|
REVOKE_SYSTEM_PRIVILEGE
|
Revokes the ADMINISTER_RESOURCE_MANAGER system privilege from a user or role.
|
The following PL/SQL block grants the administrative privilege to user SCOTT, but does not grant SCOTT the ADMIN option. Therefore, RESMGR can execute all of the procedures in the DBMS_RESOURCE_MANAGER package, but SCOTT cannot use the GRANT_SYSTEM_PRIVILEGE procedure to grant the administrative privilege to others.
BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
GRANTEE_NAME => 'RSMGR,
PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
ADMIN_OPTION => FALSE);
END;
/
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
GRANTEE_NAME => 'RSMGR,
PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
ADMIN_OPTION => FALSE);
END;
/
The following PL/SQL block creates a simple resource plan with two user-specified consumer groups:
SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan like '%SIMPLE%' order by 2;
PLAN GROUP_OR_SUBPLAN CPU_P1 CPU_P2 CPU_P3 MGMT_P1 MGMT_P2 MGMT_P3
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
MY_SIMPLE_PLAN MY_SIMPLE_GROUP1 0 80 0 0 80 0
MY_SIMPLE_PLAN MY_SIMPLE_GROUP2 0 20 0 0 20 0
MY_SIMPLE_PLAN OTHER_GROUPS 0 0 100 0 0 100
MY_SIMPLE_PLAN SYS_GROUP 100 0 0 100 0 0
SHAIKDB>begin
2 dbms_resource_manager.create_simple_plan(simple_plan=>'MY_SIMPLE_PLAN',
3 CONSUMER_GROUP1=>'MY_SIMPLE_GROUP1',GROUP1_PERCENT=>80,
4 CONSUMER_GROUP2=>'MY_SIMPLE_GROUP2',GROUP2_PERCENT=>20);
5 END;
6 /
PL/SQL procedure successfully completed.
Consumer Group
|
Level 1
|
Level 2
|
Level 3
|
SYS_GROUP
|
100%
|
-
|
-
|
MYGROUP1
|
-
|
80%
|
-
|
MYGROUP2
|
-
|
20%
|
-
|
OTHER_GROUPS
|
-
|
-
|
100%
|
A complex resource plan is any resource plan that is not created with the DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN procedure.
for a more complex resource plan, you must create the plan, with its directives and consumer groups, in a staging area called the pending area, and then validate the plan before storing it in the data dictionary.
Step 1: Create a pending area.
Step 2: Create, modify, or delete consumer groups.
Step 3: Create the resource plan.
Step 4: Create resource plan directives.
Step 5: Validate the pending area.
Step 6: Submit the pending area.
Pending Area
The pending area is a staging area where you can create a new resource plan, update an existing plan, or delete a plan without affecting currently running applications. When you create a pending area, the database initializes it and then copies existing plans into the pending area so that they can be updated
SHAIKDB>exec dbms_resource_manager.create_pending_area();
PL/SQL procedure successfully completed.
Creating a Resource Consumer Group
The following PL/SQL block creates a consumer group called OLTP with the default (ROUND-ROBIN) method of allocating resources to sessions in the group:
SHAIKDB>exec dbms_resource_manager.create_consumer_group('CRITICAL','Group for Critical Apps');
PL/SQL procedure successfully completed.
Available Procedures under Resource_manager:
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
|
Creating a Resource Plan
Parameter
|
Description
|
PLAN
|
Name to assign to the plan.
|
COMMENT
|
Any descriptive comment.
|
CPU_MTH
|
Deprecated. Use MGMT_MTH.
|
ACTIVE_SESS_POOL_MTH
|
Active session pool resource allocation method. 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 the order in which queued inactive sessions are removed from the queue and added to the active session pool. FIFO_TIMEOUT is the default and only method available.
|
MGMT_MTH
|
Resource allocation method for specifying how much CPU each consumer group or subplan gets. 'EMPHASIS', the default method, is for single-level or multilevel plans that use percentages to specify how CPU is distributed among consumer groups. 'RATIO' is for single-level plans that use ratios to specify how CPU is distributed.
|
SUB_PLAN
|
If TRUE, the plan cannot be used as the top plan; it can be used as a subplan only. Default is FALSE.
|
The following shows resource plan options:
PLAN
|
Name of the resource plan to which the directive belongs.
|
GROUP_OR_SUBPLAN
|
Name of the consumer group or subplan to which to allocate resources.
|
COMMENT
|
Any comment.
|
CPU_P1
|
Deprecated. Use MGMT_P1.
|
CPU_P2
|
Deprecated. Use MGMT_P2.
|
CPU_P3
|
Deprecated. Use MGMT_P3.
|
CPU_P4
|
Deprecated. Use MGMT_P4.
|
CPU_P5
|
Deprecated. Use MGMT_P5.
|
CPU_P6
|
Deprecated. Use MGMT_P6.
|
CPU_P7
|
Deprecated. Use MGMT_P7.
|
CPU_P8
|
Deprecated. Use MGMT_P8.
|
ACTIVE_SESS_POOL_P1
|
Specifies the maximum number of concurrently active sessions for a consumer group. Other sessions await execution in an inactive session queue. Default is UNLIMITED.
|
QUEUEING_P1
|
Specifies time (in seconds) after which a session in an inactive session queue (waiting for execution) times out and the call is aborted. Default is UNLIMITED.
|
PARALLEL_DEGREE_LIMIT_P1
|
Specifies a limit on the degree of parallelism for any operation. Default is UNLIMITED.
|
SWITCH_GROUP
|
Specifies the consumer group to which a session is switched if switch criteria are met. If the group name is 'CANCEL_SQL', then the current call is canceled when switch criteria are met. If the group name is 'KILL_SESSION', then the session is killed when switch criteria are met. Default is NULL. If the group name is 'CANCEL_SQL', the SWITCH_FOR_CALL parameter is always set to TRUE, overriding the user-specified setting.
|
SWITCH_TIME
|
Specifies the time (in CPU seconds) that a call can execute before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.
|
SWITCH_ESTIMATE
|
If TRUE, the database estimates the execution time of each call, and if estimated execution time exceeds SWITCH_TIME, the session is switched to the SWITCH_GROUP before beginning the call. Default is FALSE. The execution time estimate is obtained from the optimizer. The accuracy of the estimate is dependent on many factors, especially the quality of the optimizer statistics. In general, you should expect statistics to be no more accurate than ± 10 minutes.
|
MAX_EST_EXEC_TIME
|
Specifies the maximum execution time (in CPU seconds) allowed for a call. If the optimizer estimates that a call will take longer than MAX_EST_EXEC_TIME, the call is not allowed to proceed and ORA-07455 is issued. If the optimizer does not provide an estimate, this directive has no effect. Default is UNLIMITED. The accuracy of the estimate is dependent on many factors, especially the quality of the optimizer statistics. In general, you should expect statistics to be no more accurate than ± 10 minutes.
|
UNDO_POOL
|
Sets a maximum in kilobytes (K) on the total amount of undo for uncommitted transactions that can be generated by a consumer group. Default is UNLIMITED.
|
MAX_IDLE_TIME
|
Indicates the maximum session idle time, in seconds. Default is NULL, which implies unlimited.
|
MAX_IDLE_BLOCKER_TIME
|
Indicates the maximum session idle time of a blocking session, in seconds. Default is NULL, which implies unlimited.
|
SWITCH_TIME_IN_CALL
|
Deprecated. Use SWITCH_FOR_CALL.
|
MGMT_P1
|
For a plan with the MGMT_MTH parameter set to EMPHASIS, specifies the CPU percentage to allocate at the first level. For MGMT_MTH set to RATIO, specifies the weight of CPU usage. Default is NULL for all MGMT_Pn parameters.
|
MGMT_P2
|
For EMPHASIS, specifies CPU percentage to allocate at the second level. Not applicable for RATIO.
|
MGMT_P3
|
For EMPHASIS, specifies CPU percentage to allocate at the third level. Not applicable for RATIO.
|
MGMT_P4
|
For EMPHASIS, specifies CPU percentage to allocate at the fourth level. Not applicable for RATIO.
|
MGMT_P5
|
For EMPHASIS, specifies CPU percentage to allocate at the fifth level. Not applicable for RATIO.
|
MGMT_P6
|
For EMPHASIS, specifies CPU percentage to allocate at the sixth level. Not applicable for RATIO.
|
MGMT_P7
|
For EMPHASIS, specifies CPU percentage to allocate at the seventh level. Not applicable for RATIO.
|
MGMT_P8
|
For EMPHASIS, specifies CPU percentage to allocate at the eighth level. Not applicable for RATIO.
|
SWITCH_IO_MEGABYTES
|
Specifies the number of megabytes of I/O that a session can transfer (read and write) before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.
|
SWITCH_IO_REQS
|
Specifies the number of I/O requests that a session can execute before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.
|
SWITCH_FOR_CALL
|
If TRUE, a session that was automatically switched to another consumer group (according to SWITCH_TIME, SWITCH_IO_MEGABYTES, or SWITCH_IO_REQS) is returned to its original consumer group when the top level call completes. Default is NULL.
|
MAX_UTILIZATION_LIMIT
|
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.
|
Validating the Pending Area:
The following PL/SQL block validates the pending area.
BEGIN
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
END;
/
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
END;
/
Submitting the Pending Area:
The following PL/SQL block submits the pending area:
BEGIN
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
Clearing the Pending AreaDBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
There is also a procedure for clearing the pending area at any time. This PL/SQL block causes all of your changes to be cleared from the pending area and deactivates the pending area:
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/
Switching a Single Session
The SWITCH_CONSUMER_GROUP_FOR_SESS procedure causes the specified session to immediately be moved into the specified resource consumer group. In effect, this procedure can raise or lower priority of the session.
The following PL/SQL block switches a specific session to a new consumer group. The session identifier (SID) is 17, the session serial number (SERIAL#) is 12345, and the new consumer group is the HIGH_PRIORITY consumer group.
BEGIN
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('17', '12345',
'HIGH_PRIORITY');
END;
/
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('17', '12345',
'HIGH_PRIORITY');
END;
/
Switching All Sessions for a User
The SWITCH_CONSUMER_GROUP_FOR_USER procedure changes the resource consumer group for all sessions pertaining to the specified user name. The following PL/SQL block switches all sessions that belong to user SCOTT to the LOW_GROUP consumer group:
BEGIN
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('SCOTT',
'LOW_GROUP');
END;
/
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('SCOTT',
'LOW_GROUP');
END;
/
Disabling the Resource Manager
To disable the Resource Manager, complete the following steps:
- Issue the following SQL statement:
- ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
The following statement in a text initialization parameter file activates the Resource Manager upon database startup and sets the top plan as mydb_plan.
RESOURCE_MANAGER_PLAN = mydb_plan
You can also activate or deactivate the Resource Manager, or change the current top plan, using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure or the ALTER SYSTEM statement.
The following SQL statement sets the top plan to mydb_plan, and activates the Resource Manager if it is not already active:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';
Deleting a Plan
The DELETE_PLAN procedure deletes the specified plan as well as all the plan directives associated with it. The pending area must be created first, and then submitted after the plan is deleted.
The following PL/SQL block deletes the great_bread plan and its directives.
BEGIN
DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'SIMPLE');
END;
/
DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'SIMPLE');
END;
/
Updating a Resource Plan Directive
Use the UPDATE_PLAN_DIRECTIVE procedure to update plan directives. The pending area must be created first, and then submitted after the resource plan directive is updated. If you do not specify an argument for the UPDATE_PLAN_DIRECTIVE procedure, its corresponding parameter in the directive remains unchanged.
The following example adds a comment to a directive:
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
PLAN => 'SIMPLE_PLAN1',
GROUP_OR_SUBPLAN => 'MYGROUP1',
NEW_COMMENT => 'Higher priority'
);
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
PLAN => 'SIMPLE_PLAN1',
GROUP_OR_SUBPLAN => 'MYGROUP1',
NEW_COMMENT => 'Higher priority'
);
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
To clear (nullify) a comment, pass a null string (''). To clear (zero or nullify) any numeric directive parameter, set its new value to -1:
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
PLAN => 'SIMPLE_PLAN1',
GROUP_OR_SUBPLAN => 'MYGROUP1',
NEW_MAX_EST_EXEC_TIME => -1
);
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
PLAN => 'SIMPLE_PLAN1',
GROUP_OR_SUBPLAN => 'MYGROUP1',
NEW_MAX_EST_EXEC_TIME => -1
);
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;
SELECT PLAN,STATUS, COMMENTS FROM DBA_RSRC_PLANS;
SQL> SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION;
SELECT NAME, IS_TOP_PLAN FROM V$RSRC_PLAN;
View
|
Description
|
DBA_RSRC_CONSUMER_GROUP_PRIVS
USER_RSRC_CONSUMER_GROUP_PRIVS
|
DBA view lists all resource consumer groups and the users and roles to which they have been granted. USER view lists all resource consumer groups granted to the user.
|
DBA_RSRC_CONSUMER_GROUPS
|
Lists all resource consumer groups that exist in the database.
|
DBA_RSRC_MANAGER_SYSTEM_PRIVS
USER_RSRC_MANAGER_SYSTEM_PRIVS
|
DBA view lists all users and roles that have been granted Resource Manager system privileges. USER view lists all the users that are granted system privileges for the DBMS_RESOURCE_MANAGER package.
|
DBA_RSRC_PLAN_DIRECTIVES
|
Lists all resource plan directives that exist in the database.
|
DBA_RSRC_PLANS
|
Lists all resource plans that exist in the database.
|
DBA_RSRC_GROUP_MAPPINGS
|
Lists all of the various mapping pairs for all of the session attributes.
|
DBA_RSRC_MAPPING_PRIORITY
|
Lists the current mapping priority of each attribute.
|
DBA_HIST_RSRC_PLAN
|
Displays historical information about resource plan activation. This view contains AWR snapshots of V$RSRC_PLAN_HISTORY.
|
DBA_HIST_RSRC_CONSUMER_GROUP
|
Displays historical statistical information about consumer groups. This view contains AWR snapshots of V$RSRC_CONS_GROUP_HISTORY.
|
DBA_USERS
USERS_USERS
|
DBA view contains information about all users of the database. It contains the initial resource consumer group for each user. USER view contains information about the current user. It contains the current user's initial resource consumer group.
|
V$ACTIVE_SESS_POOL_MTH
|
Displays all available active session pool resource allocation methods.
|
V$PARALLEL_DEGREE_LIMIT_MTH
|
Displays all available parallel degree limit resource allocation methods.
|
V$QUEUEING_MTH
|
Displays all available queuing resource allocation methods.
|
V$RSRC_CONS_GROUP_HISTORY
|
For each entry in the view V$RSRC_PLAN_HISTORY, contains an entry for each consumer group in the plan showing the cumulative statistics for the consumer group.
|
V$RSRC_CONSUMER_GROUP
|
Displays information about active resource consumer groups. This view can be used for tuning.
|
V$RSRC_CONSUMER_GROUP_CPU_MTH
|
Displays all available CPU resource allocation methods for resource consumer groups.
|
V$RSRCMGRMETRIC
|
Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past minute.
|
V$RSRCMGRMETRIC_HISTORY
|
Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past hour on a minute-by-minute basis. If a new resource plan is enabled, the history is cleared.
|
V$RSRC_PLAN
|
Displays the names of all currently active resource plans.
|
V$RSRC_PLAN_CPU_MTH
|
Displays all available CPU resource allocation methods for resource plans.
|
V$RSRC_PLAN_HISTORY
|
Shows when Resource Manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time.
|
V$RSRC_SESSION_INFO
|
Displays Resource Manager statistics for each session. Shows how the session has been affected by the Resource Manager. Can be used for tuning.
|
V$SESSION
|
Lists session information for each current session. Specifically, lists the name of the resource consumer group of each current session.
|
Demo:
SHAIKDB>create user resmgr identified by resmgr default tablespace tbs1 temporary tablespace temp;
User created.
SHAIKDB>grant create session,create table,select any table,create procedure,create trigger to resmgr;
Grant succeeded.
SHAIKDB>SELECT GRANTEE,PRIVILEGE FROM DBA_RSRC_MANAGER_SYSTEM_PRIVS;
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
SYS ADMINISTER RESOURCE MANAGER
DBA ADMINISTER RESOURCE MANAGER
EXP_FULL_DATABASE ADMINISTER RESOURCE MANAGER
IMP_FULL_DATABASE ADMINISTER RESOURCE MANAGER
APPQOSSYS ADMINISTER RESOURCE MANAGER
SHAIKDB>BEGIN DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE('RESMGR','ADMINISTER_RESOURCE_MANAGER',FALSE);
END;
/
PL/SQL procedure successfully completed.
USERNAME PRIVILEGE ADM
---------- ------------------------------ ---
RESMGR ADMINISTER RESOURCE MANAGER NO
RESMGR CREATE TRIGGER NO
RESMGR SELECT ANY TABLE NO
RESMGR CREATE PROCEDURE NO
RESMGR CREATE SESSION NO
RESMGR CREATE TABLE NO
6 rows selected.
Granting the RESOURCE_MANAGER_ADMIN privilege using GRID Control:
Delete the plans:
SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP('CRITICAL');
PL/SQL procedure successfully completed.
If the pending area is not active:
SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN('MY_SIMPLE_PLAN');
BEGIN DBMS_RESOURCE_MANAGER.DELETE_PLAN('MY_SIMPLE_PLAN'); END;
*
ERROR at line 1:
ORA-29371: pending area is not active
ORA-06512: at "SYS.DBMS_RMIN", line 105
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 180
ORA-06512: at line 1
Create the pending Area:
SHAIKDB>BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/ 2 3 4
PL/SQL procedure successfully completed.
Delete the PLAN:
SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan like '%SIMPLE%' order by 2;
PLAN GROUP_OR_SUBPLAN CPU_P1 CPU_P2 CPU_P3 MGMT_P1 MGMT_P2 MGMT_P3
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
MY_SIMPLE_PLAN MY_SIMPLE_GROUP1 0 80 0 0 80 0
MY_SIMPLE_PLAN MY_SIMPLE_GROUP2 0 20 0 0 20 0
MY_SIMPLE_PLAN OTHER_GROUPS 0 0 100 0 0 100
MY_SIMPLE_PLAN SYS_GROUP 100 0 0 100 0 0
SHAIKDB>exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
PL/SQL procedure successfully completed.
SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN('MY_SIMPLE_PLAN');
PL/SQL procedure successfully completed.
SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
PL/SQL procedure successfully completed.
SHAIKDB>show errors
No errors.
SHAIKDB>exec dbms_resource_manager.submit_pending_Area();
PL/SQL procedure successfully completed.
SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan like '%SIMPLE%' order by 2;
no rows selected
SHAIKDB>
DEMO-2:
- Creating:
- Pending area
- Create consumer groups CRITICAL,MEDIUM,LOW
- Create PLAN - CRITICAL
- Create PLAN directives
- Validate PLAN
- Submit PLAN.
SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan=’CRITICAL_PLAN’ order by 2;
no rows selected
SHAIKDB>exec dbms_resource_manager.create_pending_area();
PL/SQL procedure successfully completed.
SHAIKDB>exec dbms_resource_manager.create_consumer_group('CRITICAL','Group for Critical Apps');
PL/SQL procedure successfully completed.
SHAIKDB>exec dbms_resource_manager.create_plan('CRITICAL_PLAN','Plan fr critical queries');
PL/SQL procedure successfully completed.
SHAIKDB>exec dbms_resource_manager.create_plan_directive(PLAN=>'CRITICAL_PLAN',GROUP_OR_SUBPLAN=>'CRITICAL',COMMENT=>'Allocate 60% CPU',MGMT_P1=>60);
PL/SQL procedure successfully completed.
SHAIKDB>exec dbms_resource_manager.create_consumer_group('MEDIUM','Medium critical Apps');
PL/SQL procedure successfully completed.
SHAIKDB>exec dbms_resource_manager.create_consumer_group('LOW','Low critical Apps');
PL/SQL procedure successfully completed.
SHAIKDB>exec dbms_resource_manager.create_plan_directive(PLAN=>'CRITICAL_PLAN',GROUP_OR_SUBPLAN=>'MEDIUM', COMMENT=>'ALLOCATE CPU 20%',MGMT_P1=>20,PARALLEL_DEGREE_LIMIT_P1=>2,ACTIVE_SESS_POOL_P1=>2);
PL/SQL procedure successfully completed.
SHAIKDB>exec dbms_resource_manager.create_plan_directive(PLAN=>'CRITICAL_PLAN',GROUP_OR_SUBPLAN=>'LOW', COMMENT=>'Allocate 10% CPU',MGMT_P1=>10,PARALLEL_DEGREE_LIMIT_P1=>1,ACTIVE_SESS_POOL_P1=>1);
PL/SQL procedure successfully completed.
SHAIKDB>exec dbms_resource_manager.create_plan_directive(PLAN=>'CRITICAL_PLAN',GROUP_OR_SUBPLAN=>'OTHER_GROUPS',COMMENT=>'ALLOCATE CPU 10% FOR OTHER GROUPS',MGMT_P1=>10);
PL/SQL procedure successfully completed.
SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
PL/SQL procedure successfully completed.
SHAIKDB>SHOW ERRORS
No errors.
SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA
PL/SQL procedure successfully completed.
SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan='CRITICAL_PLAN' order by 3 desc;
PLAN GROUP_OR_SUBPLAN CPU_P1 CPU_P2 CPU_P3 MGMT_P1 MGMT_P2 MGMT_P3
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
CRITICAL_PLAN CRITICAL 60 0 0 60 0 0
CRITICAL_PLAN MEDIUM 20 0 0 20 0 0
CRITICAL_PLAN LOW 10 0 0 10 0 0
CRITICAL_PLAN OTHER_GROUPS 10 0 0 10 0 0
Assign consumer groups to USERS:
SHAIKDB>create user test1 identified by test1 default tablespace tbs1 temporary tablespace temp;
User created.
SHAIKDB>grant create session to test1;
Grant succeeded.
SHAIKDB>grant select any table to test1;
Grant succeeded.
SHAIKDB>select username,account_status,INITIAL_RSRC_CONSUMER_GROUP from dba_users where username='TEST1';
USERNAME ACCOUNT_STATUS INITIAL_RSRC_CONSUMER_GROUP
------------------------------ -------------------------------- ------------------------------
TEST1 OPEN DEFAULT_CONSUMER_GROUP
SHAIKDB>exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'TEST1',consumer_group=>'LOW',grant_option=>TRUE);
PL/SQL procedure successfully completed.
SHAIKDB>exec dbms_resource_manager.set_initial_consumer_group(user=>'TEST1',consumer_group=>'LOW');
PL/SQL procedure successfully completed.
SHAIKDB>SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;
GRANTEE GRANTED_GROUP GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC DEFAULT_CONSUMER_GROUP YES YES
SYSTEM SYS_GROUP NO YES
TEST1 LOW YES YES
PUBLIC LOW_GROUP NO NO
SHAIKDB>select username,account_status,INITIAL_RSRC_CONSUMER_GROUP from dba_users where username='TEST1';
USERNAME ACCOUNT_STATUS INITIAL_RSRC_CONSUMER_GROUP
------------------------------ -------------------------------- ------------------------------
TEST1 OPEN LOW
SHAIKDB>SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION where username='TEST1';
no rows selected
SHAIKDB>/
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
50 2546 TEST1 OTHER_GROUPS
SHAIKDB>/
SWITCHING CONSUMER_GROUPS:
SHAIKDB>exec dbms_resource_manager.switch_consumer_group_for_sess('50','2546','MEDIUM');
PL/SQL procedure successfully completed.
SHAIKDB>SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION where username='TEST1';
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
50 2546 TEST1 MEDIUM
SHAIKDB>exec dbms_resource_manager.switch_consumer_group_for_user('TEST1','CRITICAL');
PL/SQL procedure successfully completed.
SHAIKDB>SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION where username='TEST1';
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
50 2546 TEST1 CRITICAL
Revoking Switch Privileges
SHAIKDB>exec dbms_resource_manager_privs.revoke_switch_consumer_group(revokee_name=>'TEST1',consumer_group=>'LOW');
PL/SQL procedure successfully completed.
Documentation:
Documentation:
Oracle® Database PL/SQL Packages and Types Reference --> 116 DBMS_RESOURCE_MANAGER
Oracle® Database Administrator's Guide 11g Release 2 (11.2) -->
26 Managing Resource Allocation with Oracle Database Resource Manager
No comments:
Post a Comment