Database resource manager oracle 11g

Using create_simple_plan procedure

This below query will automatically create resource manager plan, resource manager groups if they dont exist and assign directives to each group. First group will get level2 75% usage and second group will get 25% usage under contention.

Resource_manager_plan= EDW_PLAN
DEFAULT_CONSUMER_GROUP = 75%
GG_GROUP = 25%

begin
dbms_resource_manager.create_simple_plan(simple_plan=>'EDW_PLAN',
CONSUMER_GROUP1=>'DEFAULT_CONSUMER_GROUP',GROUP1_PERCENT=>75,
CONSUMER_GROUP2=>'GG_GROUP',GROUP2_PERCENT=>25);
END;
/

After this assign the users to their respective groups

BEGIN
    dbms_resource_manager_privs.grant_switch_consumer_group(
        grantee_name => 'SRAVAN',
        consumer_group => 'GG_GROUP',
        grant_option => TRUE
    );
END;
BEGIN
    dbms_resource_manager.set_initial_consumer_group(
        user => 'SRAVAN',
        consumer_group => 'GG_GROUP'
    );
END;


After this activate the resource manager plan

alter system set resource_manager_plan="EDW_PLAN";

You can verify and monitor DBRM using below queries

SELECT name, is_top_plan FROM v$rsrc_plan;

SELECT name, active_sessions, queue_length,consumed_cpu_time, cpu_waits, cpu_wait_time FROM v$rsrc_consumer_group;

SELECT se.sid sess_id, co.name consumer_group, se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time FROM v$rsrc_session_info se, v$rsrc_consumer_group co WHERE se.current_consumer_group_id = co.id;

SELECT sequence# seq, name plan_name,to_char(start_time, 'DD-MON-YY HH24:MM') start_time,to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name FROM v$rsrc_plan_history;

select sequence# seq, name, cpu_wait_time, cpu_waits,consumed_cpu_time from V$RSRC_CONS_GROUP_HISTORY;



*****************************************************************************

Normal procedure of creating resource manager plan

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_plan (
      plan                         => 'EDW_PLAN'
     ,mgmt_mth                     => 'EMPHASIS'
     ,active_sess_pool_mth         => 'ACTIVE_SESS_POOL_ABSOLUTE'
     ,parallel_degree_limit_mth    => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE'
     ,queueing_mth                 => 'FIFO_TIMEOUT'
     ,comment                      => 'Allocate 75% to default consumer group and 25% to golden gate user under contention
'
     ,sub_plan                      => FALSE);
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'EDW_PLAN'
     ,group_or_subplan             => 'OTHER_GROUPS'
     ,switch_estimate              => FALSE
     ,mgmt_p2                      => 75
     ,mgmt_p3                      => 100
     ,switch_for_call              => FALSE
     ,comment                      => 'OTHER_GROUPS Level 3'  );
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'EDW_PLAN'
     ,group_or_subplan             => 'SYS_GROUP'
     ,switch_estimate              => FALSE
     ,mgmt_p1                      => 100
     ,switch_for_call              => FALSE
     ,comment                      => 'SYS Level 1'  );
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'GG_GROUP'
     ,comment                      => 'Level 2 Group 2');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'EDW_PLAN'
     ,group_or_subplan             => 'GG_GROUP'
     ,switch_estimate              => FALSE
     ,mgmt_p2                      => 25
     ,switch_for_call              => FALSE
     ,comment                      => 'Level 2 Group 2'  );
  sys.dbms_resource_manager.submit_pending_area();
end;



Drop all the plans, plan directives, groups

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.delete_plan_cascade ('EDW_PLAN');
  sys.dbms_resource_manager.submit_pending_area();
end;
/

Note:

You can use toad or enterprise manager to to create, alter any resource plans in easier way if you feel the above code is complicated.

Please also make sure you modify default_maintenance_plan because this will be activated during automatic maintenance windows and if you did not include your allocations it will stick to default ones.

You can limit parallelism, CPU% and other parameters using DBRM


Switching user group

BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'SXXXX',
    consumer_group => 'BATCH_GROUP',
    grant_option   => FALSE);
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
BEGIN
  SYS.DBMS_RESOURCE_MANAGER.set_initial_consumer_group(
    user           => 'SXXXX',
    consumer_group => 'BATCH_GROUP');
END;
/