Database Resource Manager(DRM)

Database Resource Manager(DRM)

Using the DRM, you can allocate CPU time percentages to different applications and users. You could assign a DSS a lower priority than an OLTP system, and vice versa. In essence, you have the ability to configure your production environment based upon various application priorities.
DRM can partition the CPU to a maximum of eight levels. Uses percentages to specify how to partition CPU at each level.
DRM can also be used to limit the degree of parallelism for a set of users. By setting the max degree of parallelism for a consumer group in a plan, you can manage parallelism at the plan level.
Elements of the Database Resource manager
Resource Consumer Group - user sessions that are grouped based upon similar resource usage requirements.
Resource Plan – specifies which resources such as CPU and parallelism to allocate to each Resource Consumer Group. Many can be defined, but only one is used by an instance at a particular point in time.
Resource Allocation Method – What the DRM uses when allocating a particular resource. These methods are “used” by both Resource Consumer Groups and Resource Plans.
Resource Plan Directive – The administrator uses this to allocate resources among Resource Consumer groups and to associate Resource Consumer Groups with Resource Plans.
The following order represents the approach for Database Resource Management:

1. Create resource plans
   
2. Create resource consumer groups
   
3. Create resource plan directives
   
4. Assign users to consumer groups
   
5. Specify plan to be used by an instance

DBMS_RESOURCE_MANAGER Package – Use to maintain Resource Consumer Groups, Resource Plans and Plan Directives, as well as group changes to the plan schema. Need SYSTEM privilege to admin the DRM.
Uses the following packages to perform its functions:

• create_plan
  
• update_plan
  
• delete_plan
  
• delete_plan_cascade
  
• create_consumer_group
  
• update_consumer_group
  
• delete_consumer_group
  
• create_plan_directive
  
• update_plan_directive
  
• delete_plan_directive
  

Use a scratch area called the pending area to make changes to plan schema. The process is 1) create the pending area, 2) make changes, and 3) submit the changes. Validating the changes is an optional step. The submit_pending_area procedure must complete successfully in order for changes to take effect.
Use these 4 procedures in this order:

1. dbms_resource_manager.create_pending_area
2. dbms_resource_manager.validate_pending_area (optional)
3. dbms_resource_manager.clear_pending_area
4. dbms_resource_manager.submit_pending_area

DATABASE_RESOURCE_MANAGER package is also used to assign Resource Consumer Groups to users using the following procedure:
set_initial_consumer_group(user in varchar2, consumer_group in varchar2)
Use the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure along with the OTHER_GROUPS consumer group to create a plan directive to share for all user sessions that are not explicitly assigned a directive in a resource plan. This procedure includes the Resource Plan, Consumer Group and other rules.
The DBMS_RESOURCE_MANAGER_PRIVS package is used to maintain privileges that are associated to Resource Consumer Groups. In essence, grant and revoke classes to users. Procedures are executed with the privileges of the individual procedure caller.

• use to grant switch privileges
  
• use to revoke switch privileges
  

Use the DBMS_SESSION package to your current Resource Consumer group, using the following procedure:
switch_current_consumer_group