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
- 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;
/