Oracle | DBMS_RESOURCE

Oracle DBMS_RESOURCE_MANAGER Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 jargon that translates into English as "you have a CDB."

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Mapping
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'
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'
service_module	VARCHAR2(30)	'SERVICE_MODULE'
service_module_action	VARCHAR2(30)	'SERVICE_MODULE_ACTION'
service_name	VARCHAR2(30)	'SERVICE_NAME'

Dependencies

DBA_CDB_RSRC_PLANS	DBMS_ASSERT
DBA_CDB_RSRC_PLAN_DIRECTIVES	DBMS_AUTO_TASK_EXPORT
DBA_HIST_RSRC_CONSUMER_GROUP	DBMS_RESOURCE_MANAGER_PRIVS
DBA_HIST_RSRC_PLAN	DBMS_OUTPUT
DBA_RSRC_CAPABILITY	DBMS_PRVTRMIE
DBA_RSRC_CATEGORIES	DBMS_SQL
DBA_RSRC_CONSUMER_GROUPS	DBMS_SYS_ERROR
DBA_RSRC_CONSUMER_GROUP_PRIVS	DBMS_SYS_SQL
DBA_RSRC_GROUP_MAPPINGS	DBMS_WLM
DBA_RSRC_INSTANCE_CAPABILITY	DEFAULT_CONSUMER_GROUP
DBA_RSRC_IO_CALIBRATE	GV_$RSRC_CONSUMER_GROUP
DBA_RSRC_MANAGER_SYSTEM_PRIVS	PRVTEMX_RSRCMGR
DBA_RSRC_MAPPING_PRIORITY	RESOURCE_CONSUMER_GROUP$
DBA_RSRC_PLANS	RESOURCE_PLAN$
DBA_RSRC_PLAN_DIRECTIVES	RESOURCE_PLAN_DIRECTIVE$
DBA_RSRC_STORAGE_POOL_MAPPING	V_$RSRC_CONSUMER_GROUP_CPU_MTH
DBA_USERS	WRI$_REPT_RSRCMGR

Documented Yes: Packages and Types Reference

First Available 8.1.5

Sample Plans

For each level the sum of allocation percentages must total 100%

Group	Level 1	Level 2	Level 3	Level 4	Max. Degree
DEFLT_GRP	20	45	40	0	10
PROD_GRP	50	24	30	100	-
SYS_GRP	30	30	0	0	-
OTHER_GRP	0	0	30	0	15
Total	100	100	100	100

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsrmad.sql

Subprograms

BEGIN_SQL_BLOCK	END_SQL_BLOCK
CALIBRATE_IO	SET_CONSUMER_GROUP_MAPPING
CLEAR_PENDING_AREA	SET_CONSUMER_GROUP_MAPPING_PRI
CREATE_CATEGORY	SET_INITIAL_CONSUMER_GROUP
CREATE_CDB_PLAN	SUBMIT_PENDING_AREA
CREATE_CDB_PLAN_DIRECTIVE	SWITCH_CONSUMER_GROUP_FOR_SESS
CREATE_CDB_PROFILE_DIRECTIVE	SWITCH_CONSUMER_GROUP_FOR_USER
CREATE_CONSUMER_GROUP	SWITCH_PLAN
CREATE_PENDING_AREA	UPDATE_CATEGORY
CREATE_PLAN	UPDATE_CDB_AUTOTASK_DIRECTIVE
CREATE_PLAN_DIRECTIVE	UPDATE_CDB_DEFAULT_DIRECTIVE
CREATE_SIMPLE_PLAN	UPDATE_CDB_PLAN
DELETE_CATEGORY	UPDATE_CDB_PLAN_DIRECTIVE
DELETE_CDB_PLAN	UPDATE_CDB_PROFILE_DIRECTIVE
DELETE_CDB_PLAN_DIRECTIVE	UPDATE_CONSUMER_GROUP
DELETE_CDB_PROFILE_DIRECTIVE	UPDATE_PLAN
DELETE_CONSUMER_GROUP	UPDATE_PLAN_DIRECTIVE
DELETE_PLAN	VALIDATE_PENDING_AREA
DELETE_PLAN_CASCADE	Demos
DELETE_PLAN_DIRECTIVE	Real Application Cluster (RAC)
DEQUEUE_PARALLEL_STATEMENT	Resource Manager Related Queries

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

Available categories are:
ADMINISTRATIVE, BATCH, INTERACTIVE, MAINTENANCE,
and OTHER 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;

      

      PL/SQL procedure successfully completed.

      
      

      exec dbms_resource_manager.create_category('ZZYZX', 'Test Category');

      

      PL/SQL procedure successfully completed.

      

      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

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

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, memory_min IN NUMBER DEFAULT NULL, memory_limit IN NUMBER DEFAULT NULL);

exec dbms_resource_manager.create_cdb_plan_directive('UW_CDB_PLAN', 'PDBDEV', memory_min=>30);

CREATE_CDB_PROFILE_DIRECTIVE

Create a consolidation resource plan profile directive dbms_resource_manager.create_cdb_profile_directive( plan IN VARCHAR2, profile IN VARCHAR2, comment IN VARCHAR2 DEFAULT '', shares IN NUMBER DEFAULT NULL, utilization_limit IN NUMBER DEFAULT NULL, parallel_server_limit IN NUMBER DEFAULT NULL, memory_min IN NUMBER DEFAULT NULL, memory_limit IN NUMBER DEFAULT NULL

exec dbms_resource_manager.create_cdb_profile_directive('UW_CDB_PLAN', 'PDBADMIN', memory_min=>30);

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

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, mgmt_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);

exec dbms_resource_manager.delete_category('ZZYZX');

      

      PL/SQL procedure successfully completed.

DELETE_CDB_PLAN

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

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_CDB_PROFILE_DIRECTIVE

Delete a consolidation resource plan profile directive dbms_resource_manager.delete_cdb_profile_directive( plan IN VARCHAR2, profile IN VARCHAR2);

exec dbms_resource_manager.delete_cdb_profile_directive('UW_CDB_PLAN', 'PDBADMIN');

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

DEQUEUE_PARALLEL_STATEMENT

Dequeues a parallel statement dbms_resource_manager.dequeue_parallel_statement( session_id IN PLS_INTEGER, session_serial IN PLS_INTEGER, inst_id IN PLS_INTEGER DEFAULT NULL, sql_id IN VARCHAR2 DEFAULT NULL);

exec dbms_resource_manager.dequeue_parallel_statement(270, 6174, 1, '72vv31kuthvzd');

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

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, new_memory_min IN NUMBER DEFAULT NULL, new_memory_limit IN NUMBER DEFAULT NULL);

exec dbms_resource_manager.update_cdb_autotask_directive('UW_CDB_PLAN', 'PDBDEV', 15)

UPDATE_CDB_DEFAULT_DIRECTIVE

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, new_memory_min IN NUMBER DEFAULT NULL, new_memory_limit IN NUMBER DEFAULT NULL);

exec dbms_resource_manager.update_cdb_plan_directive('UW_CDB_PLAN', new_shares=>10, new_memory_limit=>45);

UPDATE_CDB_PLAN

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

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, new_memory_min IN NUMBER DEFAULT NULL, new_memory_limit IN NUMBER DEFAULT NULL);

exec dbms_resource_manager.update_cdb_plan_directive('UW_CDB_PLAN', 'Another 
      Totally Comment', new_memory_min=>10, new_memory_limit=>25);

UPDATE_CDB_PROFILE_DIRECTIVE

Update a consolidation resource plan profile directive dbms_resource_manager.update_cdb_profile_directive( plan IN VARCHAR2, profile 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, new_memory_min IN NUMBER DEFAULT NULL, new_memory_limit IN NUMBER DEFAULT NULL);

exec dbms_resource_manager.update_cdb_profile_directive('UW_CDB_PLAN', 'Another Totally Comment', new_memory_min=>10, new_memory_limit=>25);

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

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 Managers from running an operation with 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@pdbdev 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 sys@pdbdev 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;