Oracle Resource Manager

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)

resource consumer group - group similar users together who have a similar resource needs.
resource plan - lays out how resource consumer groups are allocated resources, only one plan can be active and can have up to 8 levels but no sub plans
resource allocation method - dictates the specific method you choose to use to allocate resources like CPU.
resource plan directive - links a resource plan to a specific resource consumer group, can have sub-levels.

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

Create a pending area
Create a resource consumer group
Create a resource plan
Create a plan directive
Validate the pending area
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

consumer_group - the name of the consumer group
comment - any useful comment
cpu_mth - values are round-robin (default) or run-to-completion, affects the users session

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

creating	dbms_resource_manager.create_consumer_group( consumer_group=> 'developers', comment=> 'application developers');
modifying	dbms_resource_manager.update_consumer_group( consumer_group=>'developers', cpu_mth=>'run-to-completion');
removing	dbms_resource_manager.delete_consumer_group( consumer_group=>'developers');
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

CPU_MTH	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;
creating	exec dbms_resource_manager.create_plan( plan=>'day', comment=>'plan for normal working hours' cpu_mth=>'ratio');
removing	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 ( plan=>'prod_plan', group_or_subgroup=>'dss_group', comment=>'Limit idle time', max_idle_time=>900, max_idle_blocker_time=>300);
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;