Oracle uses the Database Resource Manager to allow for the allocation of resources in a queue type management style to different users or groups of users. Resources can be allocated to different groups of users based on CPU percentage times with attached processing priorities. Also different resource groups or plans can be activated at different times of day for instance to allow for differences between day-time and night-time processing requirements. As part of this concept of resource management there are four distinct definitions required.
Resource Consumer Groups - groups users into similar usage requirements groups based on CPU usage percentages.
Resource Plans - plans are used to allocate resources to different consumer groups.
Resource Allocation Methods - policies applied to consumer groups and plans where different levels of priority contain different percentages of CPU use where remaining resources fall from low to high level without being allocated to other consumer groups.
Resource Plan Directives
Assign consumer groups and subplans to plans.
Allocation of resources amongst resource consumer groups in the currently active database plan (one plan per database) by specifying parameters for each resource allocation method.
Basically resource consumer groups contain user sessions. Resource plans use resource consumer groups to allocate processing resources through resource consumer groups to individual user sessions. More than resource plan allows changing of the state of the database, for instance day-time and night-time as mentioned above. Also resource plans can contain subplans which are subset specialisations of their parent plans. It should be apparent to see the usefulness of sharing resources between users by allocating resources to users in a round-robin like fashion. However, does not an operating system like UNIX do this sort of thing anyway; probably at the level of Oracle processes rather than queries within Oracle. Obviously multiple leveled resource allocation plans would complicate but could be tuned to more effectively allocate resources based on requirements at any specific time. In general process or resource allocation switching would allow Oracle to perform it's own resource scheduling, Oracle should know a lot more about what is required of Oracle than the operating system.
There is a default consumer group for any user called DEFAULT_CONSUMER_GROUP. Any user not belonging to a resource consumer group will be part of OTHER_GROUPS. SYS_GROUP contains sessions for the SYS and SYSTEM users and LOW_GROUP is used for low priority sessions.
Two packages, DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER_PRIVS contain all procedures for administration of reources. DBMS_RESOURCE_MANAGER is used to maintain consumer groups, plans, plan directives and pending areas for resource manager changes. DBMS_RESOURCE_MANAGER_PRIVS can be used to grant and revoke system privileges plus consumer group switches.
Below is SQL*Plus code and it's execution showing all the aspects of a resource grouping setup.
set wrap off; set head on; set linesize 300; set pagesize 132; spool c:\tmp\rsrc.txt; select PLAN,NUM_PLAN_DIRECTIVES,CPU_METHOD,MAX_ACTIVE_SESS_TARGET_MTH,PARALLEL_DEGREE_LIMIT_MTH,substr(COMMENTS,1,64) "COMMENTS",STATUS,MANDATORY from dba_rsrc_plans order by plan; select CONSUMER_GROUP,CPU_METHOD,STATUS,MANDATORY,substr(COMMENTS,1,64) "COMMENTS" from DBA_RSRC_CONSUMER_GROUPS order by consumer_group; select PLAN,GROUP_OR_SUBPLAN,TYPE,CPU_P1,CPU_P2,CPU_P3,CPU_P4,CPU_P5,CPU_P6,CPU_P7,CPU_P8,MAX_ACTIVE_SESS_TARGET_P1,PARALLEL_DEGREE_LIMIT_P1,STATUS,MANDATORY,substr(COMMENTS,1,64) from DBA_RSRC_PLAN_DIRECTIVES order by PLAN,GROUP_OR_SUBPLAN,TYPE; select * from DBA_RSRC_CONSUMER_GROUP_PRIVS order by grantee; select * from DBA_RSRC_MANAGER_SYSTEM_PRIVS order by grantee; spool off; --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL> select PLAN,NUM_PLAN_DIRECTIVES,CPU_METHOD,MAX_ACTIVE_SESS_TARGET_MTH,PARALLEL_DEGREE_LIMIT_MTH,substr(COMMENTS,1,64) "COMMENTS",STATUS,MANDATORY from dba_rsrc_plans order by plan; rows will be truncated rows will be truncated PLAN NUM_PLAN_DIRECTIVES CPU_METHOD MAX_ACTIVE_SESS_TARGET_MTH PARALLEL_DEGREE_LIMIT_MTH COMMENTS ------------------------------ ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------ SYSTEM_PLAN 3 EMPHASIS MAX_ACTIVE_SESS_ABSOLUTE PARALLEL_DEGREE_LIMIT_ABSOLUTE Plan to give system sessions priority SQL> select CONSUMER_GROUP,CPU_METHOD,STATUS,MANDATORY,substr(COMMENTS,1,64) "COMMENTS" from DBA_RSRC_CONSUMER_GROUPS order by consumer_group; CONSUMER_GROUP CPU_METHOD STATUS MAN COMMENTS ------------------------------ ------------------------------ ------------------------------ --- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DEFAULT_CONSUMER_GROUP ROUND-ROBIN ACTIVE YES consumer group for users not assigned to any group LOW_GROUP ROUND-ROBIN ACTIVE NO Group of low priority sessions OTHER_GROUPS ROUND-ROBIN ACTIVE YES consumer group for users not included in any group in the active SYS_GROUP ROUND-ROBIN ACTIVE NO Group of system sessions SQL> select PLAN,GROUP_OR_SUBPLAN,TYPE,CPU_P1,CPU_P2,CPU_P3,CPU_P4,CPU_P5,CPU_P6,CPU_P7,CPU_P8,MAX_ACTIVE_SESS_TARGET_P1,PARALLEL_DEGREE_LIMIT_P1,STATUS,MANDATORY,substr(COMMENTS,1,64) from DBA_RSRC_PLAN_DIRECTIVES order by PLAN,GROUP_OR_SUBPLAN,TYPE; PLAN GROUP_OR_SUBPLAN TYPE CPU_P1 CPU_P2 CPU_P3 CPU_P4 CPU_P5 CPU_P6 CPU_P7 CPU_P8 MAX_ACTIVE_SESS_TARGET_P1 PARALLEL_DEGREE_LIMIT_P1 STATUS MAN SUBSTR(COMMENTS,1,64) ------------------------------ ------------------------------ -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------- ------------------------ ------------------------------ --- ------------------------------------------------- SYSTEM_PLAN LOW_GROUP CONSUMER_GROUP 0 0 100 0 0 0 0 0 1000000 1000000 ACTIVE NO Other sessions at lowest priority SYSTEM_PLAN OTHER_GROUPS CONSUMER_GROUP 0 100 0 0 0 0 0 0 1000000 1000000 ACTIVE NO Other sessions at lower priority SYSTEM_PLAN SYS_GROUP CONSUMER_GROUP 100 0 0 0 0 0 0 0 1000000 1000000 ACTIVE NO System sessions at high priority SQL> select * from DBA_RSRC_CONSUMER_GROUP_PRIVS order by grantee; GRANTEE GRANTED_GROUP GRA INI ------------------------------ ------------------------------ --- --- CUSTDBREADERS DEFAULT_CONSUMER_GROUP NO YES CUSTDBWRITERS DEFAULT_CONSUMER_GROUP NO YES DBA SYS_GROUP NO NO DBSNMP SYS_GROUP NO NO DEVELOPER DEFAULT_CONSUMER_GROUP NO YES DBREAD DEFAULT_CONSUMER_GROUP NO YES USER1 DEFAULT_CONSUMER_GROUP NO YES -- OEM DEFAULT_CONSUMER_GROUP NO YES OEM SYS_GROUP NO NO OEM_MONITOR SYS_GROUP NO NO OUTLN LOW_GROUP NO NO PUBLIC DEFAULT_CONSUMER_GROUP YES YES -- PUBLIC LOW_GROUP NO NO SYSMAN SYS_GROUP NO NO SYSTEM SYS_GROUP NO YES TKPROFER LOW_GROUP NO NO TRACESVR LOW_GROUP NO NO DBWRITE DEFAULT_CONSUMER_GROUP NO YES 18 rows selected. SQL> select * from DBA_RSRC_MANAGER_SYSTEM_PRIVS order by grantee; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA ADMINISTER RESOURCE MANAGER YES EXP_FULL_DATABASE ADMINISTER RESOURCE MANAGER NO IMP_FULL_DATABASE ADMINISTER RESOURCE MANAGER NO SQL> spool off;
spool ../logs/resourcePlans.log;
connect internal;
--
--OLTP_GROUP Consumer Group
--
execute dbms_resource_manager.clear_pending_area();
execute dbms_resource_manager.create_pending_area();
execute dbms_resource_manager.create_consumer_group(consumer_group => 'OLTP_GROUP', comment => ' ');
execute dbms_resource_manager.submit_pending_area();
execute dbms_resource_manager_privs.grant_switch_consumer_group('DBWRITE', 'OLTP_GROUP', false);
execute dbms_resource_manager_privs.grant_switch_consumer_group('DBREAD', 'OLTP_GROUP', false);
execute dbms_resource_manager_privs.grant_switch_consumer_group('USER1', 'OLTP_GROUP', false);
execute dbms_resource_manager.set_initial_consumer_group('DBWRITE', 'OLTP_GROUP');
execute dbms_resource_manager.set_initial_consumer_group('DBREAD', 'OLTP_GROUP');
execute dbms_resource_manager.set_initial_consumer_group('USER1', 'OLTP_GROUP');
--
--DBA_GROUP Consumer Group
--
execute dbms_resource_manager.clear_pending_area();
execute dbms_resource_manager.create_pending_area();
execute dbms_resource_manager.create_consumer_group(consumer_group => 'DBA_GROUP', comment => ' ');
execute dbms_resource_manager.submit_pending_area();
execute dbms_resource_manager_privs.grant_switch_consumer_group('DBSNMP', 'DBA_GROUP', false);
execute dbms_resource_manager_privs.grant_switch_consumer_group('OEM', 'DBA_GROUP', false);
execute dbms_resource_manager_privs.grant_switch_consumer_group('OUTLN', 'DBA_GROUP', false);
execute dbms_resource_manager_privs.grant_switch_consumer_group('SYSMAN', 'DBA_GROUP', false);
execute dbms_resource_manager_privs.grant_switch_consumer_group('TRACESVR', 'DBA_GROUP', false);
execute dbms_resource_manager.set_initial_consumer_group('DBSNMP', 'DBA_GROUP');
execute dbms_resource_manager.set_initial_consumer_group('OEM', 'DBA_GROUP');
execute dbms_resource_manager.set_initial_consumer_group('OUTLN', 'DBA_GROUP');
execute dbms_resource_manager.set_initial_consumer_group('SYSMAN', 'DBA_GROUP');
execute dbms_resource_manager.set_initial_consumer_group('TRACESVR', 'DBA_GROUP');
--
--OLTP Plan
--
execute dbms_resource_manager.clear_pending_area();
execute dbms_resource_manager.create_pending_area();
execute dbms_resource_manager.create_plan('OLTP', 'OLTP daytime resource plan');
execute dbms_resource_manager.create_plan_directive(plan => 'OLTP',group_or_subplan => 'OLTP_GROUP',comment => ' ',cpu_p1 => 80, cpu_p2 => 40, cpu_p3 => 20, cpu_p4 => 10,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'OLTP',group_or_subplan => 'SYS_GROUP',comment => ' ',cpu_p1 => 10, cpu_p2 => 25, cpu_p3 => 35, cpu_p4 => 40,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'OLTP',group_or_subplan => 'DBA_GROUP',comment => ' ',cpu_p1 => 5, cpu_p2 => 25, cpu_p3 => 35, cpu_p4 => 40,cpu_p5 =v 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'OLTP',group_or_subplan => 'LOW_GROUP',comment => ' ',cpu_p1 => 0, cpu_p2 => 10, cpu_p3 => 10, cpu_p4 => 10,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'OLTP',group_or_subplan => 'OTHER_GROUPS',comment => ' ',cpu_p1 => 5, cpu_p2 => 0, cpu_p3 => 0, cpu_p4 => 0,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.submit_pending_area();
--
--BACKUP Plan
--
execute dbms_resource_manager.clear_pending_area();
execute dbms_resource_manager.create_pending_area();
execute dbms_resource_manager.create_plan('BACKUP', 'BACKUP night-time backup plan');
execute dbms_resource_manager.create_plan_directive(plan => 'BACKUP',group_or_subplan => 'DBA_GROUP',comment => ' ',cpu_p1 => 80, cpu_p2 => 40, cpu_p3 => 20, cpu_p4 => 10,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'BACKUP',group_or_subplan => 'SYS_GROUP',comment => ' ',cpu_p1 => 10, cpu_p2 => 20, cpu_p3 => 40, cpu_p4 => 45,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'BACKUP',group_or_subplan => 'OLTP_GROUP',comment => ' ',cpu_p1 => 5, cpu_p2 => 20, cpu_p3 => 40, cpu_p4 => 45,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'BACKUP',group_or_subplan => 'OTHER_GROUPS',comment => ' ',cpu_p1 => 5, cpu_p2 => 0, cpu_p3 => 0, cpu_p4 => 0,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.submit_pending_area();
--
--MAINTENANCE Plan
--
execute dbms_resource_manager.clear_pending_area();
execute dbms_resource_manager.create_pending_area();
execute dbms_resource_manager.create_plan('MAINTENANCE', 'MAINTENANCE database structural maintenance plan');
execute dbms_resource_manager.create_plan_directive(plan => 'MAINTENANCE',group_or_subplan => 'DBA_GROUP',comment => ' ',cpu_p1 => 45, cpu_p2 => 30, cpu_p3 => 30, cpu_p4 => 25,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'MAINTENANCE',group_or_subplan => 'SYS_GROUP',comment => ' ',cpu_p1 => 45, cpu_p2 => 30, cpu_p3 => 30, cpu_p4 => 25,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'MAINTENANCE',group_or_subplan => 'OLTP_GROUP',comment => ' ',cpu_p1 => 5, cpu_p2 => 20, cpu_p3 => 40, cpu_p4 => 50,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.create_plan_directive(plan => 'MAINTENANCE',group_or_subplan => 'OTHER_GROUPS',comment => ' ',cpu_p1 => 5, cpu_p2 => 0, cpu_p3 => 0, cpu_p4 => 0,cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,parallel_degree_limit_p1 => 0);
execute dbms_resource_manager.submit_pending_area();
ALTER SYSTEM SET resource_manager_plan = 'OLTP';
disconnect;
spool off;