Oracle DBMS_RESOURCE_MANAGER
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Maintains plans, consumer groups, and plan directives that control and limit CPU and I/O resource allocation between PDBs and User Consumer Groups. It also provides semantics so that you may group together changes to the plan schema.

You may note in the Oracle docs the phrase "It can only be called when consolidation is enabled." This is a jargon phrase that when translated into English means "you have a CDB."
AUTHID CURRENT_USER
Constants
Name Data Type Value
Mapping
asm_disk_group VARCHAR2(30) 'ASM_DISK_GROUP'
client_id VARCHAR2(30) 'CLIENT_ID'
client_machine VARCHAR2(30) 'CLIENT_MACHINE'
client_os_user VARCHAR2(30) 'CLIENT_OS_USER'
client_program VARCHAR2(30) 'CLIENT_PROGRAM'
dedicated_storage VARCHAR2(30) 'DEDICATED_STORAGE'
log_files VARCHAR2(30) 'LOG_FILES'
managed_files VARCHAR2(30) 'MANAGED_FILES'
module_name VARCHAR2(30) 'MODULE_NAME'
module_name_action VARCHAR2(30) 'MODULE_NAME_ACTION'
oracle_function VARCHAR2(30) 'ORACLE_FUNCTION'
oracle_user VARCHAR2(30) 'ORACLE_USER'
performance_class VARCHAR2(30) 'PERFORMANCE_CLASS'
recovery_area VARCHAR2(30) 'RECOVERY_AREA'
service_module VARCHAR2(30) 'SERVICE_MODULE'
service_module_action VARCHAR2(30) 'SERVICE_MODULE_ACTION'
service_name VARCHAR2(30) 'SERVICE_NAME'
shared_storage VARCHAR2(30) 'SHARED_STORAGE'
temp_files VARCHAR2(30) 'TEMP_FILES'
unmanaged_files VARCHAR2(30) 'UNMANAGED_FILES'
Dependencies
DBA_CDB_RSRC_PLANS DBA_RSRC_MAPPING_PRIORITY DBMS_RMIN_SYS
DBA_CDB_RSRC_PLAN_DIRECTIVES DBA_RSRC_PLANS DBMS_SQL
DBA_HIST_RSRC_CONSUMER_GROUP DBA_RSRC_PLAN_DIRECTIVES DBMS_SYS_ERROR
DBA_HIST_RSRC_PLAN DBA_RSRC_STORAGE_POOL_MAPPING DBMS_SYS_SQL
DBA_RSRC_CAPABILITY DBA_USERS DBMS_WLM
DBA_RSRC_CATEGORIES DBMS_ASSERT DEFAULT_CONSUMER_GROUP
DBA_RSRC_CONSUMER_GROUPS DBMS_AUTO_TASK_ADMIN GV_$RSRC_CONSUMER_GROUP
DBA_RSRC_CONSUMER_GROUP_PRIVS DBMS_AUTO_TASK_EXPORT RESOURCE_CONSUMER_GROUP$
DBA_RSRC_GROUP_MAPPINGS DBMS_PRVTRMIE RESOURCE_PLAN$
DBA_RSRC_INSTANCE_CAPABILITY DBMS_RESOURCE_MANAGER_PRIVS RESOURCE_PLAN_DIRECTIVE$
DBA_RSRC_IO_CALIBRATE DBMS_RMIN V_$RSRC_CONSUMER_GROUP_CPU_MTH
DBA_RSRC_MANAGER_SYSTEM_PRIVS    
Documented Yes
First Available 8.1.5
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsrmad.sql
Subprograms
 
BEGIN_SQL_BLOCK
Indicates the beginning of a block of statements for parallel statement queuing dbms_resource_manager.begin_sql_block;
exec dbms_resource_manager.begin_sql_block;
 
CALIBRATE_IO
Initiates an I/O calibration

Note: Depending on database size or available disk this may take a very long time
dbms_resource_manager.calibrate_io(
num_physical_disks IN  PLS_INTEGER DEFAULT 1,
max_latency        IN  PLS_INTEGER DEFAULT 20,
max_iops           OUT PLS_INTEGER,
max_mbps           OUT PLS_INTEGER,
actual_latency     OUT PLS_INTEGER);
conn / as sysdba

SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';

col name format a50

SELECT name, asynch_io
FROM gv$datafile f, gv$iostat_file i
WHERE f.file# = i.file_no
AND filetype_name = 'Data File';
-- asynch io must be configured

SELECT name, value
FROM gv$parameter
WHERE name = 'filesystemio_options';

ALTER SYSTEM SET filesystemio_options = 'SETALL' SCOPE=SPFILE;
ALTER SYSTEM SET filesystemio_options = 'NONE' SCOPE=SPFILE;
shutdown immediate;
start;

SELECT name, value
FROM gv$parameter
WHERE name = 'filesystemio_options';


set serveroutput on

DECLARE
 iops PLS_INTEGER;
 mbps PLS_INTEGER;
 alat PLS_INTEGER;
BEGIN
  dbms_resource_manager.calibrate_io(1, 100, iops, mbps, alat);
  dbms_output.put_line('Maximum IOPS: ' || TO_CHAR(iops));
  dbms_output.put_line('Maximum MBPS: ' || TO_CHAR(mbps));
  dbms_output.put_line('Actual Latency: ' || TO_CHAR(alat));
END;
/

SELECT *
FROM gv$io_calibration_status;

SELECT *
FROM dba_rsrc_io_calibrate;
 
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_CATEGORY
Create a new resource consumer group category dbms_resource_manager.create_category(
category IN VARCHAR2,
comment  IN VARCHAR2);
col category format a15
col comments format a60

SELECT consumer_group, category, comments
FROM dba_rsrc_consumer_groups
ORDER BY 1;

exec dbms_resource_manager.create_pending_area;
-- this produces an error undoubtedly the result of an internal bug
exec dbms_resource_manager.create_category('UW_CATX', 'Demo Category');

SELECT consumer_group, category, comments
FROM dba_rsrc_consumer_groups
ORDER BY 1;

exec dbms_resource_manager.update_category('UW_CAT', 'New Comment');

SELECT consumer_group, category, comments
FROM dba_rsrc_consumer_groups
ORDER BY 1;

exec dbms_resource_manager.delete_category('UW_CAT');

SELECT consumer_group, category, comments
FROM dba_rsrc_consumer_groups
ORDER BY 1;

exec dbms_resource_manager.submit_pending_area;
 
CREATE_CDB_PLAN (new 12.1)
Creates a consolidated resource plan  for the CDB dbms_resource_manager.create_cdb_plan(
plan    IN VARCHAR2,
comment IN VARCHAR2 DEFAULT '');
exec dbms_resource_manager.create_cdb_plan('UW_CDB_PLAN', NULL);
 
CREATE_CDB_PLAN_DIRECTIVE (new 12.1)
Create a consolidation plan directive dbms_resource_manager.create_cdb_plan_directive(
plan                  IN VARCHAR2,
pluggable_database    IN VARCHAR2,
comment               IN VARCHAR2 DEFAULT '',
shares                IN NUMBER   DEFAULT NULL,
utilization_limit     IN NUMBER   DEFAULT NULL,
parallel_server_limit IN NUMBER   DEFAULT NULL);
exec dbms_resource_manager.create_cdb_plan_directive('UW_CDB_PLAN', 'PDBDEV');
 
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,
mgmt_mth       IN VARCHAR2 DEFAULT 'ROUND-ROBIN',
category       IN VARCHAR2 DEFAULT 'OTHER');

-- alternate cpu_mth is RUN-TO-COMPLETION
See Demos Below
 
CREATE_PENDING_AREA
Create a work area for changes to resource manager objects dbms_resource_manager.create_pending_area;
See Demos Below
 
CREATE_PLAN
Create entries that define resource plans dbms_resource_manager.create_plan(
plan                      IN VARCHAR2,
comment                   IN VARCHAR2,
cpu_mth                   IN VARCHAR2 DEFAULT NULL,
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',
mgmt_mth                  IN VARCHAR2 DEFAULT 'EMPHASIS',
sub_plan                  IN BOOLEAN  DEFAULT FALSE,
max_iops                  IN NUMBER   DEFAULT NULL,
max_mbps                  IN NUMBER   DEFAULT NULL);

cpu_mth: Use 'EMPHASIS' for multi-level plans and 'RATIO' 
for single level plans
See Demos Below
 
CREATE_PLAN_DIRECTIVE (new 12.1 parameters)
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,
mgmt_p1                    IN NUMBER   DEFAULT NULL,
mgmt_p2                    IN NUMBER   DEFAULT NULL,
gmt_p3                     IN NUMBER   DEFAULT NULL,
mgmt_p4                    IN NUMBER   DEFAULT NULL,
mgmt_p5                    IN NUMBER   DEFAULT NULL,
mgmt_p6                    IN NUMBER   DEFAULT NULL,
mgmt_p7                    IN NUMBER   DEFAULT NULL,
mgmt_p8                    IN NUMBER   DEFAULT NULL,
switch_io_megabytes        IN NUMBER   DEFAULT NULL,
switch_io_reqs             IN NUMBER   DEFAULT NULL,
switch_for_call            IN BOOLEAN  DEFAULT NULL,
max_utilization_limit      IN NUMBER   DEFAULT NULL, -- max resource alloc. as a % cap
parallel_target_percentage IN NUMBER   DEFAULT NULL,
parallel_queue_timeout     IN NUMBER   DEFAULT NULL,
parallel_server_limit      IN NUMBER   DEFAULT NULL,
utilization_limit          IN NUMBER   DEFAULT NULL,
switch_io_logical          IN NUMBER   DEFAULT NULL,
switch_elapsed_time        IN NUMBER   DEFAULT NULL,
shares                     IN NUMBER   DEFAULT NULL,
parallel_stmt_critical     IN VARCHAR2 DEFAULT NULL);
See Demos Below
 
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 NULL,
CONSUMER_GROUP1 IN VARCHAR2 DEFAULT NULL,
GROUP1_CPU      IN NUMBER   DEFAULT NULL,  -- deprecated parameter
CONSUMER_GROUP2 IN VARCHAR2 DEFAULT NULL,
GROUP2_CPU      IN NUMBER   DEFAULT NULL,  -- deprecated parameter
CONSUMER_GROUP3 IN VARCHAR2 DEFAULT NULL,
GROUP3_CPU      IN NUMBER   DEFAULT NULL,  -- deprecated parameter
CONSUMER_GROUP4 IN VARCHAR2 DEFAULT NULL,
GROUP4_CPU      IN NUMBER   DEFAULT NULL,  -- deprecated parameter
CONSUMER_GROUP5 IN VARCHAR2 DEFAULT NULL,
GROUP5_CPU      IN NUMBER   DEFAULT NULL,  -- deprecated parameter
CONSUMER_GROUP6 IN VARCHAR2 DEFAULT NULL,
GROUP6_CPU      IN NUMBER   DEFAULT NULL,  -- deprecated parameter
CONSUMER_GROUP7 IN VARCHAR2 DEFAULT NULL,
GROUP7_CPU      IN NUMBER   DEFAULT NULL,  -- deprecated parameter
CONSUMER_GROUP8 IN VARCHAR2 DEFAULT NULL,
GROUP8_CPU      IN NUMBER   DEFAULT NULL,  -- deprecated parameter
GROUP1_PERCENT  IN NUMBER   DEFAULT NULL,
GROUP2_PERCENT  IN NUMBER   DEFAULT NULL,
GROUP3_PERCENT  IN NUMBER   DEFAULT NULL,
GROUP4_PERCENT  IN NUMBER   DEFAULT NULL,
GROUP5_PERCENT  IN NUMBER   DEFAULT NULL,
GROUP6_PERCENT  IN NUMBER   DEFAULT NULL,
GROUP7_PERCENT  IN NUMBER   DEFAULT NULL,
GROUP8_PERCENT  IN NUMBER   DEFAULT NULL);
TBD
 
DELETE_CATEGORY
Delete an existing resource consumer group category dbms_resource_manager.delete_category(category IN VARCHAR2);
See CREATE_CATEGORY Demo Above
 
DELETE_CDB_PLAN (new 12.1)
Drops a consolidated resource plan  for a Container DB dbms_resource_manager.delete_cdb_plan(plan IN VARCHAR2);
exec dbms_resource_manager.delete_cdb_plan('UW_CDB_PLAN');
 
DELETE_CDB_PLAN_DIRECTIVE (new 12.1)
Drops a consolidation plan directive dbms_resource_manager.delete_cdb_plan_directive(
plan               IN VARCHAR2,
pluggable_database IN VARCHAR2);
exec dbms_resource_manager.create_cdb_plan_directive('UW_CDB_PLAN', 'PDBDEV');
 
DELETE_CONSUMER_GROUP
Delete entries that define a resource consumer group dbms_resource_manager.delete_consumer_group(consumer_group IN VARCHAR2);
exec dbms_resource_manager.delete_consumer_group('Workers');
 
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 Demos Below
 
DELETE_PLAN_DIRECTIVE
Delete resource plan directives dbms_resource_manager.delete_plan_directive(
plan             IN VARCHAR2,
group_or_subplan IN VARCHAR2);
See Demos Below
 
END_SQL_BLOCK
Indicates the end of a block of statements for parallel statement queuing dbms_resource_manager.end_sql_block;
exec dbms_resource_manager.end_sql_block;
 
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);
-- extracted from $ORACLE_HOME/rdbms/adminexecrm.sql. Do not run this demo code
BEGIN
  dbms_rmin.install;
  -- set initial consumer group for SYS and SYSTEM to be SYS_GROUP
  dbms_resource_manager.create_pending_area;
  dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user, 'SYSTEM', 'SYS_GROUP');
  dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user, 'SYS', 'SYS_GROUP');
END;
/
 
SET_CONSUMER_GROUP_MAPPING_PRI
Creates the session attribute mapping priority list

The highest priority is 1, the lowest 10 (the docs are incorrect)
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,
client_id             IN NUMBER DEFAULT 11);
desc dba_rsrc_mapping_priority

SELECT *
FROM dba_rsrc_mapping_priority;

exec dbms_resource_manager.create_pending_area;

exec dbms_resource_manager.set_consumer_group_mapping_pri(1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

exec dbms_resource_manager.validate_pending_area;

exec dbms_resource_manager.submit_pending_area;

SELECT *
FROM dba_rsrc_mapping_priority;

exec dbms_resource_manager.create_pending_area;

exec dbms_resource_manager.set_consumer_group_mapping_pri(1, 7, 6, 9, 8, 10, 5, 4, 3, 2);

exec dbms_resource_manager.validate_pending_area;

exec dbms_resource_manager.submit_pending_area;
 
SET_INITIAL_CONSUMER_GROUP
Assigns the initial resource consumer group for a user: Obsolete dbms_resource_manager.set_initial_consumer_group(
user           IN VARCHAR2,
consumer_group IN VARCHAR2);
Deprecated
 
SUBMIT_PENDING_AREA
Submits pending changes for the resource manager dbms_resource_manager.submit_pending_area;
See Demos Below
 
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);
SELECT username, initial_rsrc_consumer_group
FROM dba_users
ORDER BY 1;

col schemaname format a15
col program format a20

SELECT sid, serial#, schemaname, osuser, program
FROM gv$session
WHERE service_name NOT LIKE '%BACK%';

exec dbms_resource_manager.switch_consumer_group_for_sess(147, 1883, 'SYS_GROUP');

SELECT username, initial_rsrc_consumer_group
FROM dba_users
ORDER BY 1;

exec dbms_resource_manager.switch_consumer_group_for_sess(147, 1883,  'DEFAULT_CONSUMER_GROUP');
 
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_CATEGORY
Update an existing resource consumer group category dbms_resource_manager.update_category(
category    IN VARCHAR2,
new_comment IN VARCHAR2 DEFAULT NULL);
See CREATE_CATEGORY Demo Above
 
UPDATE_CDB_AUTOTASK_DIRECTIVE (new 12.1)
Update a consolidation resource plan's autotask directive which applies to the autotask jobs that run in the root PDB dbms_resource_manager.update_cdb_autotask_directive(
plan                      IN VARCHAR2,
new_comment               IN VARCHAR2 DEFAULT NULL,
new_shares                IN NUMBER   DEFAULT NULL,
new_utilization_limit     IN NUMBER DEFAULT NULL,
new_parallel_server_limit IN NUMBER DEFAULT NULL);
 
 
UPDATE_CDB_DEFAULT_DIRECTIVE (new 12.1)
Update a consolidation resource plan's default directive which applies to each PDB that does not have a specific directive in the CDB plan dbms_resource_manager.update_cdb_default_directive(
plan                      IN VARCHAR2,
new_comment               IN VARCHAR2 DEFAULT NULL,
new_shares                IN NUMBER DEFAULT NULL,
new_utilization_limit     IN NUMBER DEFAULT NULL,
new_parallel_server_limit IN NUMBER DEFAULT NULL);
 
 
UPDATE_CDB_PLAN (new 12.1)
Update a consolidation resource plan dbms_resource_manager.update_cdb_plan(
plan        IN VARCHAR2,
new_comment IN VARCHAR2 DEFAULT NULL);
exec dbms_resource_manager.update_cdb_plan('UW_CDB_PLAN', 'Insightful Update Comment');
 
UPDATE_CDB_PLAN_DIRECTIVE (new 12.1)
Update a consolidation resource plan directive dbms_resource_manager.update_cdb_plan_directive(
plan                      IN VARCHAR2,
pluggable_database        IN VARCHAR2,
new_comment               IN VARCHAR2 DEFAULT NULL,
new_shares                IN NUMBER DEFAULT NULL,
new_utilization_limit     IN NUMBER DEFAULT NULL,
new_parallel_server_limit IN NUMBER DEFAULT NULL);
exec dbms_resource_manager.update_cdb_plan_directive('UW_CDB_PLAN', 'Another Relevant Comment');
 
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,
new_mgmt_mth   IN VARCHAR2 DEFAULT NULL,
new_category   IN VARCHAR2 DEFAULT NULL);
See Demos Below
 
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,
new_mgmt_mth                  IN VARCHAR2 DEFAULT NULL,
new_sub_plan                  IN BOOLEAN  DEFAULT FALSE,
new_max_iops                  IN NUMBER   DEFAULT NULL,
new_max_mbps                  IN NUMBER   DEFAULT NULL);
TBD
 
UPDATE_PLAN_DIRECTIVE (new 12.1 parameters)
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,
new_mgmt_p1                    IN NUMBER   DEFAULT NULL,
new_mgmt_p2                    IN NUMBER   DEFAULT NULL,
new_mgmt_p3                    IN NUMBER   DEFAULT NULL,
new_mgmt_p4                    IN NUMBER   DEFAULT NULL,
new_mgmt_p5                    IN NUMBER   DEFAULT NULL,
new_mgmt_p6                    IN NUMBER   DEFAULT NULL,
new_mgmt_p7                    IN NUMBER   DEFAULT NULL,
new_mgmt_p8                    IN NUMBER   DEFAULT NULL,
new_switch_io_megabytes        IN NUMBER   DEFAULT NULL,
new_switch_io_reqs             IN NUMBER   DEFAULT NULL,
new_switch_for_call            IN BOOLEAN  DEFAULT NULL,
new_max_utilization_limit      IN NUMBER   DEFAULT NULL,
new_parallel_target_percentage IN NUMBER   DEFAULT NULL,
new_parallel_queue_timeout     IN NUMBER   DEFAULT NULL,
new_parallel_server_limit      IN NUMBER   DEFAULT NULL,
new_utilization_limit          IN NUMBER   DEFAULT NULL,
new_switch_io_logical          IN NUMBER   DEFAULT NULL,
new_switch_elapsed_time        IN NUMBER   DEFAULT NULL,
new_shares                     IN NUMBER   DEFAULT NULL,
new_parallel_stmt_critical     IN VARCHAR2 DEFAULT NULL);
TBD
 
VALIDATE_PENDING_AREA
Validates pending changes for the resource manager dbms_resource_manager.validate_pending_area;
See Demos Below
 
Demos
Create Plan 1 Demonstration conn sys@pdbdev 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 a30
col cpu_method format a10
col comments format a60

desc dba_rsrc_plans

SELECT plan, cpu_method, comments, 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@pdbdev

-- 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');
 
Real Application Cluster (RAC)
Creating services and resource management groups for a RAC cluster -- server control portion
srvctl add service -d OPM01P -s node1primary -r OPM01P1 -a OPM01P3
srvctl add service -d OPM01P -s node2primary -r OPM01P2 -a OPM01P3
srvctl add service -d OPM01P -s node3primary -r OPM01P3 -a OPM01P1, OPM01P2

srvctl start service -d OPM01P -s node1primary
srvctl start service -d OPM01P -s node2primary
srvctl start service -d OPM01P -s node3primary

-- database portion: add mappings and mapping priority
BEGIN
  dbms_resource_manager.clear_pending_area();
  dbms_resource_manager.create_pending_area();

  dbms_resource_manager.set_consumer_group_mapping(
    attribute => 'CLIENT_PROGRAM',
    value => 'EtlEngine.exe',
    consumer_group => 'data_collection');

  dbms_resource_manager.set_consumer_group_mapping(
    attribute => 'CLIENT_PROGRAM',
    value => 'ReadData.exe',
    consumer_group => 'data_collection');

  dbms_resource_manager.set_consumer_group_mapping_pri(
    explicit              =>  1,
    oracle_user           =>  4,
    service_name          =>  5,
    client_os_user        =>  6,
    client_program        =>  2,
    client_machine        =>  7,
    module_name           =>  3,
    module_name_action    =>  8,
    service_module        =>  9,
    service_module_action => 10);

  dbms_resource_manager.validate_pending_area();
  dbms_resource_manager.submit_pending_area();
END;
/
 
Resource Manager Related Queries
Resource Manager Throttling Queries SELECT dbms_utility.current_instance, TO_CHAR(begin_time, 'HH:MI') time,
60 * (SELECT value FROM v$osstat WHERE stat_name = 'NUM_CPUS') total,
60 * (SELECT value FROM v$parameter WHERE name = 'cpu_count') db_total,
SUM(cpu_consumed_time) / 1000 consumed, SUM(cpu_wait_time) / 1000 throttled
FROM v$rsrcmgrmetric_history
GROUP BY begin_time
ORDER BY 1;

SELECT TO_CHAR(begin_time, 'HH:MI') time,
ROUND(SUM(cpu_consumed_time) / 60000,2) avg_running,
ROUND(SUM(cpu_wait_time) / 60000,2) avg_throttled
FROM gv$rsrcmgrmetric_history
GROUP BY TO_CHAR(begin_time, 'HH:MI')
ORDER BY 1;

Related Topics
DBMS_RESOURCE_MANAGER_PRIVS
DBMS_RMGR_PACT_EXPORT
DBMS_RMIN
DBMS_SCHEDULER
Packages
Profiles
Users

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved