Tuning Oracle Resources


Allocating System Resources to Users using Consumer Groups

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.

  1. Resource Consumer Groups - groups users into similar usage requirements groups based on CPU usage percentages.

  2. Resource Plans - plans are used to allocate resources to different consumer groups.

  3. 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.

  4. Resource Plan Directives

    1. Assign consumer groups and subplans to plans.

    2. 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.

An Example Resource Consumer Group Setup

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;

This Sets it Up


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;