The Oracle Database Resource Manager (the Resource Manager) is an infrastructure that provides granular control of database resources allocated to users, applications, and services. The Oracle Database Resource Manager (RM) enables you to manage multiple workloads that are contending for system and database resources.
Resources are allocated to users according to a resource plan specified by the database administrator. The plan specifies how the resources are to be distributed among resource consumer groups, which are user sessions grouped by resource requirements. A resource plan directive associates a resource consumer group with a plan and specifies how resources are to be allocated to the group. DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks.
Oracle Documentation on Oracle Database Resource Manager
For 12cR1 - Oracle® Database Administrator's Guide 12c Release 1 (12.1) - Part Number E17636-17
Managing Resources with Oracle Database Resource Manager
DBMS_RESOURCE_MANAGER - Reference
Using Oracle Resource Manager on pluggable databases (PDBs) in a multitenant container database (CDB)
For 11gR2 - Oracle® Database Administrator's Guide 11g Release 2 (11.2) - Part Number E25494-02
Managing Resources with Oracle Database Resource Manager
For 11gR1 - Oracle® Database Administrator's Guide 11g Release 1 (11.1) - Part Number B28310-04
Managing Resource Allocation with Oracle Database Resource Manager
DBMS_RESOURCE_MANAGER - Reference
Please refer the Note 1119407.1 for the Video Tutorial on Resource Manager, which includes both Concepts and Lab practices
The DBMS_RESOURCE_MANAGER package is to be used to setup and configure resourcemanager. 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.
You must have the system privilege ADMINISTER_RESOURCE_MANAGER to administer the Resource Manager. This privilege (with the ADMIN option) is granted to database administrators through the DBA role. GRANT_SYSTEM_PRIVILEGE and REVOKE_SYSTEM_PRIVILEGE procedures of DBMS_RESOURCE_MANAGER_PRIVS PL/SQL package are used to grant & revoke this.
The ADMINISTER_RESOURCE_MANAGER system privilege can only be granted or revoked using the DBMS_RESOURCE_MANAGER_PRIVS package. It cannot be granted or revoked through the SQL GRANT or REVOKE statements.
In order to assign users to resource consumer groups other than default, the users must have been granted the system privilege grant_switch_consumer_group in order to be able to switch to that consumer group. GRANT_SWITCH_CONSUMER_GROUP and REVOKE_SWITCH_CONSUMER_GROUP procedures of DBMS_RESOURCE_MANAGER_PRIVS PL/SQL package are used to grant & revoke this.
There are many types of resources, which are managed by the RM like CPU, Idle time, Undo pool, etc. Distribute CPU is the widely used RM feature. Complete list of resources managed by the RM is listed in the documentation below
As explained in the concepts section, Resources are allocated to users according to a resource plan, distributed among resource consumer groups and resource plan directive used to specify how resources are to be allocated to the group. So The elements of the Resource Manager are Resource consumer group, Resource plan, Resource plan directive. These elements are stored in tables in the data dictionary. So you can view information about them with Resource manager related data dictionary views.
You can enable the Database Resource Manager by setting the RESOURCE_MANAGER_PLAN initialization parameter.
This parameter specifies the top plan, identifying the plan schema to be used for this instance. If no
plan is specified with this parameter, the Database Resource Manager is not activated. The following
example activates the Database Resource Manager and assigns the top plan as mydb_plan.
RESOURCE_MANAGER_PLAN = mydb_plan
You can also activate or deactivate the Database Resource Manager, or change the current top plan, using
the ALTER SYSTEM statement. In this example, the top plan is specified as mydb_plan.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan;
An error message is returned if the specified plan does not exist in the data dictionary.
To deactivate the Database Resource Manager, issue the following statement:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
A database can have multiple plans defined, but only one can be active at a given time per instance. Different instance of a RAC can activate different plans, if required.
If you use Resource Manager and have up to eight consumer groups and needed only CPU resource allocation to control then simple resource plan is adequate, using the DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN procedure. When your situation calls 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 (Complex Resource Plan). Resource plans can be associated with scheduler windows or jobs, So Integration with Oracle Database Scheduler (DBMS_SCHEDULER) is possible. Resource plans do not impose a hard limit, meaning if there is no other session is taking resource allocated, then this session can take all the available resource and not just the limit allocated. Please refer Note 1119407.1 for the Video Tutorials on Resource Manager, which contains both Concepts and Lab practices
You can specify up to eight consumer groups with this procedure. The only resource allocation method supported is CPU. The plan uses the EMPHASIS CPU allocation policy (the default) and each consumer group uses the ROUND_ROBIN scheduling policy (also the default). The following PL/SQL block creates a simple resource plan with two user-specified consumer groups:
Executing the preceding statements creates the following plan:
|Consumer Group||Level 1||Level 2||Level 3|
Each consumer group specified in the plan is allocated its CPU percentage at level 2. Also implicitly included in the plan are SYS_GROUP (a system-defined group that is the initial consumer group for the users SYS and SYSTEM) and OTHER_GROUPS. The SYS_GROUP consumer group is allocated 100% of the CPU at level 1, and OTHER_GROUPS is allocated 100% of the CPU at level 3. Refer Oracle Documentation for "Creating a Simple Resource Plan" mentioned below
A complex resource plan is any resource plan that is not created with the CREATE_SIMPLE_PLAN procedure. Note that any resource plan created by the Simple Resource Plan method using CREATE_SIMPLE_PLAN can also be created by the Complex Resource Plan (this) method, also creating resource plan with this method give more flexibility and can have more granularity. When your situation calls 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. You can create sub-plans (plan within plans) for more granular resource management. If a consumer-group does not use its CPU-percentage, the remainder falls through to the next level, and the administrator can define explicit what to do with it. Up to eight levels can be specified.
Here is a general guideline of the steps in implementing a Database Resource Manager plan:
1. Create resource plans
2. Create resource consumer groups
3. Create resource plan directives
4. Grant switch privilege for resource consumer groups to users or roles
5. Assign users to resource consumer groups
6. Specify a plan to be used by the instance
In addition to the above steps, we need to create these elements in a staging area called the pending area, so the below steps are related to the pending area, which can be repeated each time to create, validate and submit this pending area.
i. Create pending area
ii. Validate Pending area
iii. Submit pending area
Following example assign 80% of the CPU-resources to sessions in the OLTP-group, and 10% to ADHOC- and remaining 10% to BATCH-group each. assuming we have created 3 users, ADHOC, BATCH, and OLTP and assigned a consumer group for each of them. The CPU-resources are allocated across all consumer-groups by the percentages given to each group. Specifying a zero percentage for a consumer-group means that it should not get any processing resources at that level, given a CPU consumption of 100%. This can force sessions for that consumer-group to wait forever in an overloaded system. It does not mean that users belonging to OTHER_GROUPS (having 0%) never are able to run, but for a fully loaded system, sessions for OTHER_GROUPS have to wait for the others to finish work. If this is the only one active session running, it is of course allowed to utilize all available CPU resources up to 100%. But if another session is started in another group, Resource Manager will balance CPU resources according to the plan directives.
---Create pending area for plan, consumer group and directives
---1. Create resource plans
---2. Create resource consumer groups
---3. Create resource plan directives
---Validate Pending area for plan, consumer group and directives
---submit pending area for plan, consumer group and directives
---Next, we have to grant privileges in order to assign consumer groups to users:
---We need to again create a pending area, till we submit the pending area
---Create pending area for privilages, roles and assign users
---4. Grant switch privilege for resource consumer groups to users or roles
---5. Assign users to resource consumer groups
---Validate Pending area for privilages, roles and assign users
---submit pending area for privilages, roles and assign users
6. Specify a plan to be used by the instance
Refer Oracle Documentation for "Creating a Complex Resource Plan" mentioned below and the Note 106948.1
We can use dynamic performance views to help us monitor the results of our Oracle Database Resource Manager
select plan, cpu_method, status from dba_rsrc_plans order by 1;
PLAN CPU_METHOD STATUS
------------- ----------- ---------
MYDB_PLAN EMPHASIS ACTIVE
1 row selected.
Resource Consumer Groups:
select consumer_group,cpu_method, status from dba_rsrc_consumer_groups order by 1;
CONSUMER_GROUP CPU_METHOD STATUS
------------------------------ ------------------------------ ------------------------------
ADHOC_GROUP ROUND-ROBIN ACTIVE
BATCH_GROUP ROUND-ROBIN ACTIVE
DEFAULT_CONSUMER_GROUP ROUND-ROBIN ACTIVE
OLTP_GROUP ROUND-ROBIN ACTIVE
OTHER_GROUPS ROUND-ROBIN ACTIVE
5 rows selected.
Resource Plan Directives:
select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4, status
from dba_rsrc_plan_directives order by 1,2,3,4,5,6;
PLAN GROUP_OR_SUBPLAN TYPE CPU_P1 CPU_P2 CPU_P3 CPU_P4 STATUS
---------------------- -------------------- -------------- ---------- ---------- ---------- ---------- -------
MYDB_PLAN ADHOC_GROUP CONSUMER_GROUP 10 0 0 0 ACTIVE
MYDB_PLAN BATCH_GROUP CONSUMER_GROUP 10 0 0 0 ACTIVE
MYDB_PLAN OLTP_GROUP CONSUMER_GROUP 80 0 0 0 ACTIVE
MYDB_PLAN OTHER_GROUPS CONSUMER_GROUP 0 0 0 0 ACTIVE
4 rows selected.
Resource consumer group privileges:
select * from dba_rsrc_consumer_group_privs;
GRANTEE GRANTED_GROUP GRA INI
------------------------------ ------------------------------ --- ---
BATCH BATCH_GROUP NO YES
OLTP OLTP_GROUP NO YES
PUBLIC DEFAULT_CONSUMER_GROUP YES YES
ADHOC ADHOC_GROUP NO YES
4 rows selected.
These above users have the privilege to switch to a consumer group other than default.
Default consumer groups for users:
select username, initial_rsrc_consumer_group from dba_users;
7 rows selected.
Monitor CPU Usage and Waits by Consumer Group:
In this example, the server has 16 CPUs which means that a maximum of 16 sessions can run at any time. Refer Note 1338988.1
select to_char(m.begin_time, 'HH:MI') time, m.consumer_group_name, m.cpu_consumed_time / 60000 avg_running_sessions, m.cpu_wait_time / 60000 avg_waiting_sessions, d.mgmt_p1*(select value from v$parameter where name = 'cpu_count')/100 allocation from v$rsrcmgrmetric_history m, dba_rsrc_plan_directives d, v$rsrc_plan p where m.consumer_group_name = d.group_or_subplan and p.name = d.plan order by m.begin_time, m.consumer_group_name;
TIME NAME AVG_RUNNING AVG_WAITING ALLOCATION
----- ------------------ ----------- ----------- ----------
13:34 ADHOC_GROUP 1.76 8.4 .8
13:34 BATCH_GROUP 2.88 .4 2.4
13:34 ETL_GROUP 0 0 2.4
13:34 INTERACTIVE_GROUP 10.4 6.8 5.6
13:34 OTHER_GROUPS .32 .08 .8
13:34 SYS_GROUP 0 0 4
The Below Articles gives more Scripts/Tips for Monitoring Oracle Resource Manager
Note 1338988.1 Scripts and Tips for Monitoring CPU Resource Manager
Note 106948.1 Database Resource Manager samples includes scripts and quires to check/monitor
Note 240877.1 Script to monitor PX limits from Resource Manager for active sessions
Note 428325.1 How to Find the Sessions Killed by MAX_IDLE_BLOCKER_TIME Plan Directive
Monitoring Oracle Database Resource Manager
Resource Manager Data Dictionary Views
In order to delete the current plan, first it has to be `switched off' or another plan has to be selected as current.
---Create pending area for delete plan and consumer group
--Delete plan (assuming we have created 2 plans - MYDB_PLAN, ANY_OTHER_CREATED_PLAN)
--Delete consumer group
---Validate Pending area for delete plan and consumer group
---submit pending area for delete plan and consumer group
--Clearing only the Pending Area, if you dont want to validate and submit
Resource Manager can be used in various ways to control and distribute the resources in Oracle Database Environment. The below Articles provides more samples/examples using resource manager for the database for different scenarios.
Note 106948.1 Database Resource Manager samples
Note 471265.1 Example: How to control CPU Resources using the Resource Manager and Scheduler
Note 1266650.1 How To Switch Users Between Resource Plans Based On Schedule Window
Note 1168574.1 How to Force RESOURCE_MANAGER_PLAN with the Scheduler window boundaries
Note 971991.1 How To Incorporate Pre-Defined jobs into your Resource Manager Plan
Note 277419.1 Prevention of long-running queries with RM MAX_EST_EXEC_TIME Plan Directive
Note 1359043.1 Configuring Parallel Statement Queuing to Manage Mixed Priority Workloads in a DataWarehouse
Note 470764.1 How to Control Resources used by a Statisics Job using the Resource Manager
Note 848666.1 How to Limit max number of concurrently active sessions with Resource Manager
Note 1321099.1 How to limit the Parallel Degree for PX Queries with Resource Manager
Note 1345429.1 Parallel Statement Queuing in 18.104.22.168
Note 1269321.1 Automatic Degree of Parallelism in 22.214.171.124
Note 1388634.1 Reverse Engineering a Resource Manager Plan From Within the Database
Note 1674897.1 How to Generate Resource Manager Configuration DDLs Using Dbms_metadata Packages
Note 987436.1 How to Identify the Session that Started the Resource Manager Pending Area
Note 398985.1 How to set Resource Manager to Disconnect Idle Users after a Predetermined time?
Note 233651.1 Why Resource Manager Is Not Distributing CPU As Per Resource Plan
Note 1557657.1 Using Resource Manager to Detect and Kill Idle Sessions
Note 786346.1 Resource Manager and Sql Tuning Advisory DEFAULT_MAINTENANCE_PLAN
Note 1296086.1 Using PL/SQL To Execute SQL or Stored Procedure/Function Calls for a Set of Users
Note 727062.1 Configuring and using Calibrate I/O
Note 1393405.1 How To Delete Calibrate I/O In The Instance
Note 884082.1 Resource Manager Enhancements in Oracle Database 11g
Note 451889.1 Logical Standby and Resource Manager
Note 1339769.1 Master Note for Oracle Database Resource Manager for EXADATA
Note 1358709.1 Configuring Resource Manager for Mixed Workloads in a Database
Note 1362445.1 Configuring and Monitoring Instance Caging
Oracle Database Resource Manager Examples
Oracle Resource Manager can be used to allocate resources to pluggable databases (PDBs) in a multitenant container database (CDB).
In a non-CDB, you can use Resource Manager to manage multiple workloads that are contending for system and database resources. However, in a CDB, you can have multiple workloads within multiple PDBs competing for system and CDB resources.
Please refer the Oracle Documentation Below:
Using Oracle Resource Manager for PDBs with SQL*Plus
Using Oracle Resource Manager for PDBs with Cloud Control
The Oracle Database Resource Manager solves many resource allocation problems that an operating system does not manage well like Excessive overhead, Inefficient scheduling, Inappropriate allocation of resources, Inability to manage database-specific resources.
The Resource Manager helps overcome these problems by giving the database more control over allocation of hardware resources and enabling you to prioritize work within the database. You can classify sessions into groups based on session attributes, and then allocate resources to these groups to optimize hardware utilization. Refer the Note 1102753.1 mentioned below, if you want to enable Oracle Resource Manager and an Operating System Resource Manager together on one system
Database Resource Manager can map services to consumer groups. Therefore, you can automatically manage the priority of one service relative to others. You can use consumer groups to define relative priority in terms of either ratios or resource consumption. Services are integrated with the Resource Manager, which enables you to restrict the resources that are used by the users who connect to an instance by using a service. The Resource Manager enables you to map a consumer group to a service so that users who connect to an instance using that service are members of the specified consumer group.
The following Troubleshooting Guide (TSG) can assist you for investigating the knownissues and will help you to fix those.
The following Notes will list out the recommended patches for Oracle Database Resource Manager, so that you can use RM without issues.
Note 1340180.1 Recommended Patches for Parallel Statement Queuing
Note 1340181.1 Recommended Patches for Exadata I/O Resource Manager