CPU usage limit according to User/System with Resource Manager in Oracle

With help of resource manager, you can limit the CPU resources available to the user or group of user. You can category based on OLTP database or night jobs etc.

Example allocate more CPU usage to OLTP environment instead of night batch jobs. Creating two user OLTP_USER and BATCH_USER for controlling CPU limit with Resource Manager in Oracle

Example to Control CPU LIMIT for User

1. Create pending area

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;
END;
/

2. Create a new resource plan

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN
  (
  PLAN => 'General_plan', 
  COMMENT => 'Covered both OLTP and Batch jobs'
  );
END;
/

3. Create consumer groups for OLTP or Batch user.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
  (
  CONSUMER_GROUP => 'OLTP_group', 
  COMMENT => 'OLTP Processing'
  );

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
  (
  CONSUMER_GROUP => 'Batch_group', 
  COMMENT => 'Batch processing'
  );
END;
/

4. Assign consumer group to plan and add other_plan directive for rest load.

BEGIN   
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'OLTP_plan',
GROUP_OR_SUBPLAN => 'OLTP_group',
COMMENT => 'OLTP Processing need more CPU', 
MGMT_P1 => 80,
MGMT_P2=> 0,
PARALLEL_DEGREE_LIMIT_P1 => 8);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'Batch_plan', 
GROUP_OR_SUBPLAN => 'Batch_group', 
COMMENT => 'batch jobs need less cpu', 
MGMT_P1 => 20, 
MGMT_P2 => 0,
PARALLEL_DEGREE_LIMIT_P1 => 4);

Note: OTHER_GROUPS - all sessions that aren't mapped to a consumer group

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(PLAN => 'other_plan', 
GROUP_OR_SUBPLAN => 'others_group',
COMMENT => 'rest of session', 
MGMT_P1 => 0, 
MGMT_P2 => 100);
END;
/

5. Validate and apply the resource plan.

Begin
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

6. Applied to users OLTP_USER or BATCH_USER

BEGIN
  -- Assign users to consumer groups
  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'OLTP_USER',
    consumer_group => 'OLTP_group',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'batch_user',
    consumer_group => 'BATCH_GROUP',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('OLTP_USER', 'OLTP_group');

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('batch_user','BATCH_GROUP');
END;
/

Consumer plan applied at instance level

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'general_plan';

Verify the Consumer plan is used by users with V$session view. disconnect and connect user again to verify

SELECT username, resource_consumer_group FROM v$session;

Verify the presence of resource plan and its contribution of CPU

-- Resource Plan 
select plan, cpu_method, status from dba_rsrc_plans
 order by 1;

--Resource consumer group
col consumer_group format a20
col cpu_method format a10
col status format a10
select consumer_group,cpu_method, status from dba_rsrc_consumer_groups order by 1;

--Resource plan Directives
select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4, status  
from dba_rsrc_plan_directives  
order by 1,2,3,4,5,6;  

Disable or Delete resource consumer plan

--unset the instance parameter
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

-- Delete the Resource plan 

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  -- Delete the Resource plan
  DBMS_RESOURCE_MANAGER.delete_plan_cascade(plan => 'general_plan');

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

Example for limit the CPU utilization at system level

One more example to limit overall CPU and rest leave for other application running on system.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    PLAN    => 'CPU_PLAN',
    COMMENT => 'Limit database CPU');
   
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN              => 'CPU_PLAN',
    GROUP_OR_SUBPLAN  => 'OTHER_GROUPS',
    COMMENT           => 'This group is mandatory',
    MAX_UTILIZATION_LIMIT => 80);

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/