Oracle DBMS_RESOURCE_MANAGER

Version 10.2

General Note: Resource Manager requires a massive over-demand on CPU before the expected behaviour starts to show: Generally speaking a 300% or 400% CPU load to see that the actual split gets closer to expectation, with the lower-privileged processes losing time in a wait state whose name includes ‘resmgr: ….{something}..’ Source {ORACLE_HOME}/rdbms/admin/dbmsrmad.sql First Available 8.1.5 Constants Name Data Type Value client_machine VARCHAR2(30) ‘CLIENT_MACHINE’ client_os_user VARCHAR2(30) ‘CLIENT_OS_USER’ client_program VARCHAR2(30) ‘CLIENT_PROGRAM’ module_name VARCHAR2(30) ‘MODULE_NAME’ module_name_action VARCHAR2(30) ‘MODULE_NAME_ACTION’ oracle_user VARCHAR2(30) ‘ORACLE_USER’ service_module VARCHAR2(30) ‘SERVICE_MODULE’ service_module_action VARCHAR2(30) ‘SERVICE_MODULE_ACTION’ service_name VARCHAR2(30) ‘SERVICE_NAME’ Dependencies dba_rsrc_consumer_groups dbms_sql dba_rsrc_consumer_group_privs dbms_sys_error dba_rsrc_group_mappings dbms_sys_sql dba_rsrc_plans default_consumer_group dba_users gv_$rsrc_consumer_group dbms_assert resource_consumer_group$ dbms_prvtrmie resource_plan$ dbms_resource_manager_privs resource_plan_directive$ dbms_rmin v_$rsrc_consumer_group_cpu_mth CLEAR_PENDING_AREA Clears the work area for the resource manager dbms_resource_manager.clear_pending_area; exec dbms_resource_manager.clear_pending_area; CREATE_CONSUMER_GROUP Create entries that define resource consumer groups dbms_resource_manager.create_consumer_group( consumer_group IN VARCHAR2, comment        IN VARCHAR2, cpu_mth        IN VARCHAR2 DEFAULT ‘ROUND-ROBIN’); – alternate cpu_mth is RUN-TO-COMPLETION See Demo CREATE_PENDING_AREA Creates a work area for changes to resource manager objects dbms_resource_manager.create_pending_area; See Demo CREATE_PLAN Create entries that define resource plans dbms_resource_manager.create_plan( plan                      IN VARCHAR2, comment                   IN VARCHAR2, cpu_mth                   IN VARCHAR2 DEFAULT ‘EMPHASIS’, active_sess_pool_mth      IN VARCHAR2 DEFAULT ‘ACTIVE_SESS_POOL_ABSOLUTE’, parallel_degree_limit_mth IN VARCHAR2 DEFAULT ‘PARALLEL_DEGREE_LIMIT_ABSOLUTE’, queueing_mth              IN VARCHAR2 DEFAULT ‘FIFO_TIMEOUT’); cpu_mth: Use ‘EMPHASIS’ for multi-level plans and ‘RATIO’ for single level plans See Demo CREATE_PLAN_DIRECTIVE Create resource plan directives dbms_resource_mananger.create_plan_directive( plan                     IN VARCHAR2, group_or_subplan         IN VARCHAR2, comment                  IN VARCHAR2, cpu_p1                   IN NUMBER DEFAULT NULL, cpu_p2                   IN NUMBER DEFAULT NULL, cpu_p3                   IN NUMBER DEFAULT NULL, cpu_p4                   IN NUMBER DEFAULT NULL, cpu_p5                   IN NUMBER DEFAULT NULL, cpu_p6                   IN NUMBER DEFAULT NULL, cpu_p7                   IN NUMBER DEFAULT NULL, cpu_p8                   IN NUMBER DEFAULT NULL, active_sess_pool_p1      IN NUMBER DEFAULT NULL, queueing_p1              IN NUMBER DEFAULT NULL, parallel_degree_limit_p1 IN NUMBER DEFAULT NULL, switch_group             IN VARCHAR2 DEFAULT NULL, switch_time              IN NUMBER   DEFAULT NULL, switch_estimate          IN BOOLEAN  DEFAULT FALSE, max_est_exec_time        IN NUMBER DEFAULT NULL, undo_pool                IN NUMBER DEFAULT NULL, max_idle_time            IN NUMBER DEFAULT NULL, max_idle_blocker_time    IN NUMBER DEFAULT NULL, switch_time_in_call      IN NUMBER DEFAULT NULL); See Demo CREATE_SIMPLE_PLAN Create a single-level resource plan containing up to eight consumer groups in one step dbms_resource_manager.create_simple_plan( SIMPLE_PLAN     IN VARCHAR2 DEFAULT, CONSUMER_GROUP1 IN VARCHAR2 DEFAULT, GROUP1_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP2 IN VARCHAR2 DEFAULT, GROUP2_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP3 IN VARCHAR2 DEFAULT, GROUP3_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP4 IN VARCHAR2 DEFAULT, GROUP4_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP5 IN VARCHAR2 DEFAULT, GROUP5_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP6 IN VARCHAR2 DEFAULT, GROUP6_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP7 IN VARCHAR2 DEFAULT, GROUP7_CPU      IN NUMBER   DEFAULT, CONSUMER_GROUP8 IN VARCHAR2 DEFAULT, GROUP8_CPU      IN NUMBER   DEFAULT); TBD DELETE_CONSUMER_GROUP Delete entries that define resource consumer groups dbms_resource_manager.delete_consumer_group( consumer_group IN VARCHAR2); See Demo DELETE_PLAN Deletes the specified plan as well as all the plan directives to which it refers dbms_resource_manager.delete_plan(plan IN VARCHAR2); exec dbms_resource_manager.delete_plan(‘UW_PLAN’); DELETE_PLAN_CASCADE Deletes the specified plan as well as well as its descendants (plan directives, subplans, consumer groups) dbms_resource_manager.delete_plan_cascade(plan IN VARCHAR2); See Demo DELETE_PLAN_DIRECTIVE Delete resource plan directives dbms_resource_manager.delete_plan_directive( plan             IN VARCHAR2, group_or_subplan IN VARCHAR2); See Demo SET_CONSUMER_GROUP_MAPPING Adds, deletes, or modifies entries that map sessions to consumer groups, based on the session’s login and runtime attributes dbms_resource_manager.set_consumer_group_mapping( attribute      IN VARCHAR2, value          IN VARCHAR2, consumer_group IN VARCHAR2 DEFAULT NULL); TBD SET_CONSUMER_GROUP_MAPPING_PRI Creates the session attribute mapping priority list dbms_resource_manager.set_consumer_group_mapping_pri( explicit              IN NUMBER, oracle_user           IN NUMBER, service_name          IN NUMBER, client_os_user        IN NUMBER, client_program        IN NUMBER, client_machine        IN NUMBER, module_name           IN NUMBER, module_name_action    IN NUMBER, service_module        IN NUMBER, service_module_action IN NUMBER); TBD SET_INITIAL_CONSUMER_GROUP Assigns the initial resource consumer group for a user dbms_resource_manager.set_initial_consumer_group( user           IN VARCHAR2, consumer_group IN VARCHAR2); See Demo SUBMIT_PENDING_AREA Submits pending changes for the resource manager dbms_resource_manager.submit_pending_area; See Demo SWITCH_CONSUMER_GROUP_FOR_SESS Changes the resource consumer group of a specific session dbms_resource_manager.switch_consumer_group_for_sess( session_id     IN NUMBER, session_serial IN NUMBER, consumer_group IN VARCHAR2); TBD SWITCH_CONSUMER_GROUP_FOR_USER Changes the resource consumer group for all sessions with a given user name dbms_resource_manager.switch_consumer_group_for_user( user           IN VARCHAR2, consumer_group IN VARCHAR2); exec dbms_resource_manager.switch_consumer_group_for_user( ‘UWCLASS’, ‘rpt_writers_grp’); SWITCH_PLAN Sets the current resource manager plan dbms_resource_manager.switch_plan( plan_name                     IN VARCHAR2, sid                           IN VARCHAR2 DEFAULT ‘*’, allow_scheduler_plan_switches IN BOOLEAN  DEFAULT TRUE); TBD UPDATE_CONSUMER_GROUP Update entries that define resource consumer groups dbms_resource_manager.update_consumer_group( consumer_group IN VARCHAR2, new_comment    IN VARCHAR2 DEFAULT NULL, new_cpu_mth    IN VARCHAR2 DEFAULT NULL); See Demo UPDATE_PLAN Update entries that define resource plans dbms_resource_manager.update_plan( plan                          IN VARCHAR2, new_comment                   IN VARCHAR2 DEFAULT NULL, new_cpu_mth                   IN VARCHAR2 DEFAULT NULL, new_active_sess_pool_mth      IN VARCHAR2 DEFAULT NULL, new_parallel_degree_limit_mth IN VARCHAR2 DEFAULT NULL, new_queueing_mth              IN VARCHAR2 DEFAULT NULL); TBD UPDATE_PLAN_DIRECTIVE Update resource plan directives dbms_resource_manager.update_plan_directive( plan                         IN VARCHAR2, group_or_subplan             IN VARCHAR2, new_comment                  IN VARCHAR2 DEFAULT NULL, new_cpu_p1                   IN NUMBER DEFAULT NULL, new_cpu_p2                   IN NUMBER DEFAULT NULL, new_cpu_p3                   IN NUMBER DEFAULT NULL, new_cpu_p4                   IN NUMBER DEFAULT NULL, new_cpu_p5                   IN NUMBER DEFAULT NULL, new_cpu_p6                   IN NUMBER DEFAULT NULL, new_cpu_p7                   IN NUMBER DEFAULT NULL, new_cpu_p8                   IN NUMBER DEFAULT NULL, new_active_sess_pool_p1      IN NUMBER DEFAULT NULL, new_queueing_p1              IN NUMBER DEFAULT NULL, new_parallel_degree_limit_p1 IN NUMBER DEFAULT NULL, new_switch_group             IN VARCHAR2 DEFAULT NULL, new_switch_time              IN NUMBER DEFAULT NULL, new_switch_estimate          IN BOOLEAN DEFAULT FALSE, new_max_est_exec_time        IN NUMBER DEFAULT NULL, new_undo_pool                IN NUMBER DEFAULT NULL, new_max_idle_time            IN NUMBER DEFAULT NULL, new_max_idle_blocker_time    IN NUMBER DEFAULT NULL, new_switch_time_in_call      IN NUMBER DEFAULT NULL); TBD VALIDATE_PENDING_AREA Validates pending changes for the resource manager dbms_resource_manager.validate_pending_area; See Demo Demos Create Plan 1 Demonstration conn / as sysdba desc dba_users SELECT username, initial_rsrc_consumer_group FROM dba_users ORDER BY 1; GRANT select ON dba_rsrc_consumer_groups TO uwclass; — create pointy-haired boss user CREATE USER phb IDENTIFIED BY phb DEFAULT TABLESPACE uwdata TEMPORARY TABLESPACE temp QUOTA 1M ON uwdata; GRANT create session TO phb; SELECT username, initial_rsrc_consumer_group FROM dba_users ORDER BY 1; desc dba_rsrc_consumer_group_privs SELECT * FROM dba_rsrc_consumer_group_privs; set linesize 121 col plan format a20 col cpu_method format a10 col status format a10 col comments format a40 desc dba_rsrc_plans SELECT plan, cpu_method, comments, status, mandatory FROM dba_rsrc_plans; col cpu_method format a15 col consumer_group format a25 col comments format a45 desc dba_rsrc_consumer_groups SELECT * FROM dba_rsrc_consumer_groups; col value format a20 desc dba_rsrc_group_mappings SELECT * FROM dba_rsrc_group_mappings; exec dbms_resource_manager_privs.grant_system_privilege(‘UWCLASS’, ‘ADMINISTER_RESOURCE_MANAGER’, FALSE); conn uwclass/uwclass – create a pending area exec dbms_resource_manager.create_pending_area; – create two consumer groups: Workers and Managers exec dbms_resource_manager.create_consumer_group(‘Workers’, ‘Those that do actual work’); exec dbms_resource_manager.create_consumer_group(‘Managers’, ‘Those that don”t but take all the credit’); SELECT * FROM dba_rsrc_consumer_groups; exec dbms_resource_manager.update_consumer_group(‘Managers’, ‘The Cartesian products of the IT world’); SELECT * FROM dba_rsrc_consumer_groups; – create resource management plan exec dbms_resource_manager.create_plan(‘UW_PLAN’,'Demo Resource Plan’, ‘RATIO’); – create plan directives exec dbms_resource_manager.create_plan_directive(plan=>’UW_PLAN’,  group_or_subplan=>’Workers’, comment=>’Can Grab All The CPU’, cpu_p1=>100); exec dbms_resource_manager.create_plan_directive(plan=>’UW_PLAN’, group_or_subplan=>’Managers’, comment=>’Give Managers Little Weight’, cpu_p1=>1); – validate the pending area exec dbms_resource_manager.validate_pending_area; exec dbms_resource_manager.create_plan_directive(plan=>’UW_PLAN’, group_or_subplan=>’OTHER_GROUPS’, comment=>’Testing’, cpu_p2=>0); – validate the pending area exec dbms_resource_manager.validate_pending_area; – oops … go back and redo correctly – delete plan directives exec dbms_resource_manager.delete_plan_directive(‘UW_PLAN’, ‘Managers’); – recreate plan directives exec dbms_resource_manager.create_plan_directive(plan=>’UW_PLAN’, group_or_subplan=>’Managers’, comment=>’Infinite Weight’, cpu_p1=>0); – revalidate the pending area exec dbms_resource_manager.validate_pending_area; – submit the pending area exec dbms_resource_manager.submit_pending_area; SELECT plan, cpu_method, comments, status, mandatory FROM dba_rsrc_plans; SELECT * FROM dba_rsrc_consumer_groups; exec dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name=>’PHB’, consumer_group=>’Managers’, grant_option=>FALSE); exec dbms_resource_manager.set_initial_consumer_group(user => ‘SCHEDULE’, consumer_group=>’Managers’); alter system set resource_manager_plan = ‘UW_PLAN’; conn schedule/schedule /* So PHB is in a group that should have no CPU. Does this mean PHB can’t do anything? */ SELECT COUNT(*) FROM all_tables; /* PHB is fine. There’s plenty of free CPU so even though the group that PHB group belongs to “looks” like it should have no CPU, that doesn’t have any effect in this case. */ Create Plan 2 Demonstration /* Resource Manager is only effective (from a CPU point of view) once CPU utilization starts to max out. Oracle takes the attitude that if there is spare CPU capacity, there is no need to limit usage. Only when the resource becomes scarce does Oracle restrict access. */ BEGIN – create pending area dbms_resource_manager.create_pending_area; dbms_resource_manager.create_plan(plan=>’TEST2′, comment=>’Use 10g Feature’); dbms_resource_manager.create_plan_directive(plan=>’TEST2′, group_or_subplan=>’HAVES’, comment=>’Testing’, cpu_p1=>100); dbms_resource_manager.create_plan_directive(plan=>’TEST2′, group_or_subplan=>’NOTS’, comment=>’Testing’, cpu_p1=>0, max_est_exec_time=>0); /* Prevent the have Managers from running any operation that has an estimated execution time > 0. */ dbms_resource_manager.create_plan_directive(plan=>’TEST2′, group_or_subplan=>’OTHER_GROUPS’, comment=>’Testing’, cpu_p2=>100); dbms_resource_manager.validate_pending_area; dbms_resource_manager.submit_pending_area; dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name=>’PHB’, consumer_group=>’Managers’, grant_option=>FALSE); dbms_resource_manager.set_initial_consumer_group(user => ‘SCHEDULE’, consumer_group=>’Managers’); END; / ALTER SYSTEM SET resource_manager_plan = test2; conn schedule/schedule SELECT COUNT(*) FROM huge_table; ERROR at line 1: ORA-07455: estimated execution time (56 secs), exceeds limit (0 secs) /* However, the problem here is that Oracle kinda rounds *down* the estimate execution time so providing I’m not being too greedy with my resources, Oracle can be a little lenient … */ SELECT COUNT(*) FROM small_table; Drop Plan Demonstration conn / as sysdba – create pending area exec dbms_resource_manager.create_pending_area; – remove admin privilege from uwclass exec dbms_resource_manager_privs.revoke_system_privilege( ‘UWCLASS’, ‘ADMINISTER_RESOURCE_MANAGER’); — delete resource plan exec dbms_resource_manager.delete_plan_cascade(‘UW_PLAN’); — validate pending area exec dbms_resource_manager.validate_pending_area; SELECT username, initial_rsrc_consumer_group FROM dba_users ORDER BY 1; – switch consumer group for user SCHEDULES exec dbms_resource_manager.switch_consumer_group_for_user( ‘SCHEDULE’, ‘DEFAULT_CONSUMER_GROUP’); BEGIN dbms_resource_manager.create_pending_area; dbms_resource_manager.delete_plan_cascade(‘UW_PLAN’); dbms_resource_manager.validate_pending_area; END; / — submit pending area exec dbms_resource_manager.submit_pending_area; – revoke system privilege from user exec dbms_resource_manager_privs.revoke_system_privilege(‘UWCLASS’);

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)

• 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

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 to a pending area to validate changes before you implement them, it serves as a work area, when you submit the pending 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

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_group;

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;