| 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'); |