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
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 |
creating | dbms_resource_manager.create_consumer_group( |
modifying | dbms_resource_manager.update_consumer_group( |
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(
|
removing | exec dbms_resource_manager.delete_plan(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 ( |
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); 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; 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; 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) |