Oracle 10g - Implement a Resource Managment Plan

Step 1 - Creating the Pending Area
-----------------------------------
exec dbms_resource_manager.create_pending_area();

Step 2 - Creating the Consumer Resource Groups
-----------------------------------------------

begin
dbms_resource_manager.create_consumer_group(
'OLTP_GROUP','Incoming orders');
end;
/

begin
dbms_resource_manager.create_consumer_group(
'DAY_REPORTS_GROUP','DAYTIME REPORTS');
end;
/

begin
dbms_resource_manager.create_consumer_group(
'NIGHTLY_PROCESSING_GROUP','BULK LOADS, ETL, ETC.');
end;
/

begin
dbms_resource_manager.create_consumer_group(
'OLAP_REPORTS_GROUP','OFF HOURS REPORTS');
end;
/


Step 3 - Creating the Resource Plans
------------------------------------
begin
dbms_resource_manager.create_plan(
PLAN => 'DAY_PLAN',
COMMENT => 'GOVERNS NORMAL WORKING HOURS ');
end;
/

begin
dbms_resource_manager.create_plan(
PLAN => 'OLTP_PLAN',
COMMENT => 'ORDER ENTRY SUB-PLAN');
end;
/

begin
dbms_resource_manager.create_plan(
PLAN => 'OFF_HOURS_PLAN',
COMMENT => 'GOVERNS NON-WORKING HOURS',
CPU_MTH => 'RATIO');
end;
/


Step 4- Creating the Resource Plan Directives

4.1 Creating the OFF_HOURS_PLAN Plan Directives

               -------------------
                OFF_HOURS_PLAN 
                Ratio 10:5:2:1 
               -------------------
             /          /\          \
            /          /  \          \
           /          /    \          \
 -------------       /      \          ----------------
  SYS_GROUP       /        \          OTHER_GROUPS
   10            /          \          1          
 --------------   /            \       ----------------
                 /              \
----------------------------     ----------------------
 NIGHTLY_PROCESSING_GROUP       OLAP_REPORTS_GROUP
      5                               2           
------------------ ---------     ----------------------

Fig. OFF_HOURS_PLAN high-level design

begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 10);
end;
/

begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'NIGHTLY_PROCESSING_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY JOBS',
CPU_P1 => 5);
end;
/

begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'OLAP_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR NIGHTLY REPORTS',
CPU_P1 => 2);
end;
/

begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OFF_HOURS_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P1 => 1);
end;
/


The CPU allocation ratio for the OFF_HOURS_PLAN plan will be 10:5:2:1.


4.2 Creating the OLTP_PLAN Plan Directives

                           -------------
                           DAY_PLAN 
                                    
               -------    -------------  ----------------------
                                                            
                                                            
          -------------                                      
level 1    SYS_GROUP                                       
             100%                                          
          -------------                                      
                          -------------                       
level 2                    OLTP_PLAN                        
                             100%                           
                          -------------                       
                          /           \                      
       Sub-plan      ------------      -------------------    
       level 1        OLTP_GROUP      DAY_REPORT_GROUP   
                        90%             10%              
                     -------------     ------------------    
                                                             
       Sub-plan            ---------------                    
       level 2              OTHER_GROUP                     
                              100%                          
                           ---------------                    
level 3                                           ---------------
                                                   OTHER_GROUP
                                                     100%     
                                                  ---------------

Fig. DAY_PLAN high-level design




begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_GROUP',
COMMENT => 'CPU ALLOCATION FOR OLTP USERS',
CPU_P1 => 90);
end;
/


begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'DAY_REPORTS_GROUP',
COMMENT => 'CPU ALLOCATION FOR DAYTIME REPORTING',
CPU_P1 => 10);
end;
/

begin
dbms_resource_manager.create_plan_directive(
PLAN => 'OLTP_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P2 => 100);
end;
/



4.3 Creating the DAY_PLAN Plan Directives

begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
COMMENT => 'CPU ALLOCATION FOR SYS_GROUP',
CPU_P1 => 100);
end;
/

begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OLTP_PLAN',
COMMENT => 'CPU ALLOCATION FOR OLTP_PLAN SUB-PLAN',
CPU_P2 => 100);
end;
/

begin
dbms_resource_manager.create_plan_directive(
PLAN => 'DAY_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'CPU ALLOCATION FOR OTHER_GROUPS',
CPU_P3 => 100);
end;
/


Step 5 - Validating the Pending Area
--------------------------------------
sys@ORCL> exec dbms_resource_manager.validate_pending_area;

PL/SQL procedure successfully completed.


Step 6 - Submitting the Pending Area
------------------------------------
sys@ORCL> exec dbms_resource_manager.submit_pending_area;

PL/SQL procedure successfully completed.



Step 7 - Enabling the Resource Plans
-----------------------------------

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAY_PLAN' SCOPE = BOTH;

Step 8 - Switching the Enabled Resource Plan
--------------------------------------------

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:OFF_HOURS_PLAN';
By adding the prefix FORCE: to the name of the plan, Oracle will restrict the active plan
from being changed by the scheduler. The scheduler will still attempt to make the change,
but it will fail.

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'OFF_HOURS_PLAN';