Database Resource Manager (DRM)

Although you can manage resources via profiles a better option is to use the resource manager, by creating resource plans which specify how much of your resources should go to the various consumer groups, you can prioritize users and jobs. The resource manager can kill long running jobs, switch jobs to higher priority all automatically, the DBA can also manually switch users between resource groups.

There are four elements to the database resource manager (DRM)

In order to administer the db resource manager you must require the administer_resource_manager privilege, by default the dba role has this privilege.

There are two API's that can be used to administer the db resource manager, Enterprise Manager also uses these API's.

DBMS_RESOURCE_MANAGER_PRIVS used to put/remove users into consumer groups and grant the system privilege
DBMS_RESOURCE_MANAGER used to create consumer groups, plans and directives, switching active groups

The following steps are what you need to use the resource manager

  1. Create a pending area
  2. Create a resource consumer group
  3. Create a resource plan
  4. Create a plan directive
  5. Validate the pending area
  6. Submit the pending area

Pending Area

You need a pending area to validate changes before you implement them, it serves as a work area, when you submit the pending area it will be stored in the data dictionary, until then you make the changes to the pending area.

Creating exec dbms_resource_manager.create_pending_area;
Clearing exec dbms_resource_manager.clear_pending_area;

Resource Consumer Group

A resource group is a container were you can group together similar users that use similar resources. Users are initially assigned one group but can be switched to another group. You need the following three parameters to create the group

There are a number of default groups available

sys_group is for the database administrators
default_consumer_group users who have not been specifically granted membership of any other group, by default all users (not sys or system) are in this group and membership is active when they first create a session
other_groups used as a catch-all for any session not listed in a group
low_group is used for low priority sessions
auto_task_consumer_group used for running system maintenance jobs


  consumer_group=> 'developers',
comment=> 'application developers');



removing dbms_resource_manager.delete_consumer_group(
displaying select consumer_group, status from dba_rsrc_consumer_groups;

Resource Plans and Plan Directives

The resource plan lays out how resource consumer groups are allocated resources, they can be multi-level (contains sub-plans) or single-level, resource plans can set limits of the following


allocate cpu usage among the resource consumer groups, the default is emphasis (multi-level) and it uses percentages to allocate CPU among various consumer groups. The alternative method called ratio (multi-level and single-level) uses ratios instead.

Note: CPU_MTH option is the only option that can be added to a sub-plan

ACTIVE_SESS_POOL_MTH determines the limit on number of active sessions in a resource consumer group
PARALLEL_DEGREE_LIMIT_MTH determines the degree of parallelism within the consumer group, however a user can requests all and you have no control, only the consumer group is limited not the users inside the group.
QUEUEING_MTH determines the order in which queued sessions will execute.

There are a number of default resource plans that already exist

internal_plan (default) one directive that states other_groups can have 100% cpu time, means all users are equal
system_plan sys_group level1 100%
other_groups level2 100%
low_group level3 100%
internal_quiesce freezes all sessions except those of the sys_group, basically set all groups to zero sessions

There are a number of plan directive resources that can be controlled

CPU You can use multiple levels of CPU resource allocation to prioritize CPU usage.
SESSIONS limit the number of open sessions within the resource group
DEGREE_OF_PARALLELISM used to run commands in parallel but you have no control over this, so if a user requests 50 he gets parallel processes but you can limit the total parallel execution servers within a group
AUTOMATIC CONSUMER GROUP SWITCHING you can specify that under some conditions the database will automatically switch sessions to another consumer group.
UNDO USAGE transactions will hang for all users of that group once the undo has reached its limit.
IDLE TIME LIMIT can be idle time and idle time / if it holds open any record locks. The session is terminated and any transactions are rollback
EXECUTION TIME can limit a jobs execution time basically cancels the job

Displaying plan in use select value from v$parameter where name = ‘resource_manager_plan’;
select * from v$rsrc_plan;

exec dbms_resource_manager.create_plan(
  comment=>'plan for normal working hours'



exec dbms_resource_manager.delete_plan(plan=>'day');
exec dbms_resource_manager.delete_plan_cascade(plan=>'day');

Note: the cascade option will remove all sub-plans as well

Modifying exec dbms_resource_manager.update_plan( plan=>'day', new_comment=>'New Comment');
Creating plan directive

exec dbms_resource_manager.create_plan_directive(plan=>'day', group_or_subplan=>'developers', cpu_p2=>50);

exec dbms_resource_manager.create_plan_directive (
  comment=>'Limit idle time',

updating plan directive exec dbms_resource_manager.update_plan_directive(plan=>'day', group_or_subplan=>'developers', new_switch_estimate=>false);
delete plan directive exec dbms_resource_manager.delete_plan_directive(plan=>'day', group_or_subplan=>'developers');
activating the plan alter system set resource_manager_plan = daytime;
forcing the plan

alter system set resource_manager_plan = 'force:daytime';

Note: restricts the setting from being changed by the scheduler, it has to be done manually

Switching groups

Switch User exec dbms_resource_manager_privs.grant_switch_consumer_group('vallep','developers',false);
Switch role exec dbms_resource_manager_privs.grant_switch_consumer_group('prog_role','developers',false);
Revoke switch

exec dbms_resource_manager_privs.revoke_switch_consumer_group('prog_role','developers');

Switch all users

exec dbms_resource_manager.switch_consumer_group_for_user(user=>'TEST', consumer_group=>'OLTP');

Switch particular session exec dbms_resource_manager.switch_consumer_group_for_sess(session_id=>209, session_serial=>10223, consumer_group=>'OLTP');
use dbms_session

exec dbms_session.switch_current_consumer_group('marketing', original_group, false);

Note: (original_group stores old group info)

Do some stuff

exec dbms_session.switch_current_consumer_group(original_group, junk, false);

Setup initial group exec dbms_resource_manager.set_initial_consumer_group('TEST', 'OLTP');
Display resource group currently assigned to select username, resource_consumer_group from v$session;

Useful Views
DBA_RSRC_PLANS what you put into action
DBA_RSRC_CONSMER_GROUP groups are associated with above plans, users are put into
DBA_RSRC_PLAN_DIRECTIVES allocates the resources via the consumer groups
DBA_RSRC_CONSUMER_GROUP_PRIVS all users consumer privileges
USER_RSRC_MANAGER_GROUP_PRIVS what consumer groups a user can access
DBA_RSRC_MANAGER_SYSTEM_PRIVS who has access to resource manager
USER_RSRC_MANAGER_SYSTEM_PRIVS does the user have resource manager privilege
DBA_RSRC_GROUP_MAPPINGS user mapping priority
DBA_RSRC_MAPPING_PRIORITY priority mappings when a user is in more than one consumer group
V$RSRC_PLAN shows all currently active resource plans

Example - putting it all together

clearing and creating the pending area

exec dbms_resource_manager.clear_pending_area;
exec dbms_resource_manager.create_pending_area;

Note: by clearing the pending area you are actually deleting it.

create the consumer groups

exec dbms_resource_manager.create_plan (plan=>’DAYTIME’,comment=>’plan for normal working hours’);

exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’SYS_GROUP’, cpu_p1=>100,comment=>’give sys_group users top priority’);

exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’OLTP’, cpu_p2=>100,comment=>’give oltp users next priority’);

exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’DDS’, cpu_p3=>50,comment=>’give dds half at next priority’);

exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’BATCH’, cpu_p3=>50,comment=>’give batch half at next priority’);

exec dbms_resource_manager.create_plan_directive(plan=>’DAYTIME’,group_or_subplan=>’other_groups’, cpu_p4=>100,comment=>’anything left they can have it!’);

validate and submit the resource plan

exec dbms_resource_manager.validate_pending_area;
exec dbms_resource_manager.submit_pending_area;

Note when you submit the pending area you are actually validating, submitting and clearing the pnding area

getting the resource plan in to action

alter system set resource_manager_plan=daytime; (dynamic)
select * from v$rsrc_plan;