Oracle Resource Management

Oracle System parameter resource_limit to true with following command

alter system set resource_limit=TRUE;

PLAN

Resource Plan	PLN_DAY				PLN_NIGHT
Consumer Groups	GRP_CONNECT	GRP_QUERY	GRP_REPORT	Others	GRP_CONNECT	GRP_QUERY	GRP_REPORT	Others

Following is the plan directives for PLN_DAY

GRP_CONNECT CPU_P1 => 5
SWITCH_GROUP => 'GRP_QUERY',
SWITCH_TIME => 5
switch_for_call => FALSE

Around 5% of CPU will be used for all the SQL that will complete within 5 second. If SQL goes beyond 5 seconds, it will be switch to group GRP_REPORT. 

GRP_QUERY CPU_P1 => 75 
SWITCH_GROUP => 'GRP_REPORT',
SWITCH_TIME => 60
switch_for_call => TRUE

Around 75% of CPU will be used for all the SQL that will complete execution between 6 second to 60 second. If SQL goes beyond 60 seconds, it will be switch to group GRP_REPORT.

GRP_REPORT CPU_P1 => 10

Around 10% of CPU will be used for all the SQL that will complete execution beyond 60 second. 

OTHER_GROUPS CPU_P1 => 10

For rest of all the users only 10% of CPU will be used.

Following is the plan directives for PLN_NIGHT

GRP_CONNECT CPU_P1 => 5
SWITCH_GROUP => 'GRP_QUERY',
SWITCH_TIME => 5
switch_for_call => FALSE

Around 5% of CPU will be used for all the SQL that will complete within 5 second. If SQL goes beyond 5 seconds, it will be switch to group GRP_REPORT. Switch_for_call determines the return back of to original consumer group after the SQL is completed.

GRP_QUERY CPU_P1 => 10
SWITCH_GROUP => 'GRP_REPORT',
SWITCH_TIME => 60
switch_for_call => TRUE

Around 60% of CPU will be used for all the SQL that will complete execution between 6 second to 60 second. If SQL goes beyond 60 seconds, it will be switch to group GRP_REPORT.

GRP_REPORT CPU_P1 => 75
Around 75% of CPU will be used for all the SQL that will complete execution beyond 60 second.

OTHER_GROUPS CPU_P1 => 10 For rest of all the users only 10% of CPU will be used.

Create pending area

-- Creating pending area 
begin
dbms_resource_manager.create_pending_area(); 
end;
/
-- Clearing pending area 
exec dbms_resource_manager.clear_pending_area(); 
-- Validating pending area 
exec dbms_resource_manager.validate_pending_area();

Create Plan

begin 
dbms_resource_manager.create_plan( 
PLAN => 'PLN_DAY', 
COMMENT => 'This plan will be for 8AM to 8PM' 
); 

dbms_resource_manager.create_plan( 
PLAN => 'PLN_NIGHT', 
COMMENT => 'This plan will be for 8PM to 8AM' 
); 
end; 
/

Create consumer groups

begin 
dbms_resource_manager.create_consumer_group( 
CONSUMER_GROUP => 'GRP_CONNECT', 
COMMENT => 'This group is initial group and lasts for 5 sec.' 
); 
dbms_resource_manager.create_consumer_group( 
CONSUMER_GROUP => 'GRP_QUERY', 
COMMENT => 'The user switch to GRP_QUERY if the query stays more than 5 sec and lasts for 60 sec' 
); 
dbms_resource_manager.create_consumer_group( 
CONSUMER_GROUP => 'GRP_REPORT', 
COMMENT => 'This group is last group and user having query executed for more than 65 sec will be stays here' 
); 
end; 
/ 

Create plan directives

begin 
dbms_resource_manager.create_plan_directive( 
PLAN => 'PLN_DAY', 
GROUP_OR_SUBPLAN => 'GRP_CONNECT', 
COMMENT => 'This is plan directive for user in GRP_CONNECT group', 
CPU_P1 => 5, 
SWITCH_GROUP => 'GRP_QUERY', 
SWITCH_TIME => 5, 
switch_for_call => FALSE 
); 
dbms_resource_manager.create_plan_directive( 
PLAN => 'PLN_DAY', 
GROUP_OR_SUBPLAN => 'GRP_QUERY', 
COMMENT => 'This is plan directive for user in GRP_QUERY group', 
CPU_P1 => 75, 
SWITCH_GROUP => 'GRP_REPORT', 
SWITCH_TIME => 60, 
switch_for_call => TRUE 
); 
dbms_resource_manager.create_plan_directive( 
PLAN => 'PLN_DAY', 
GROUP_OR_SUBPLAN => 'GRP_REPORT', 
COMMENT => 'This is plan directive for user in GRP_REPORT group', 
CPU_P1 => 10 
); 
dbms_resource_manager.create_plan_directive( 
PLAN => 'PLN_DAY', 
GROUP_OR_SUBPLAN => 'OTHER_GROUPS', 
COMMENT => 'This is plan directive for user in GRP_REPORT group', 
CPU_P1 => 10 
); 
dbms_resource_manager.create_plan_directive( 
PLAN => 'PLN_NIGHT', 
GROUP_OR_SUBPLAN => 'GRP_CONNECT', 
COMMENT => 'This is plan directive for user in GRP_CONNECT group', 
CPU_P1 => 5, 
SWITCH_GROUP => 'GRP_QUERY', 
SWITCH_TIME => 5, 
switch_for_call => FALSE 
); 
dbms_resource_manager.create_plan_directive( 
PLAN => 'PLN_NIGHT', 
GROUP_OR_SUBPLAN => 'GRP_QUERY', 
COMMENT => 'This is plan directive for user in GRP_QUERY group', 
CPU_P1 => 10, 
SWITCH_GROUP => 'GRP_REPORT', 
SWITCH_TIME => 60, 
switch_for_call => TRUE 
); 
dbms_resource_manager.create_plan_directive( 
PLAN => 'PLN_NIGHT', 
GROUP_OR_SUBPLAN => 'GRP_REPORT', 
COMMENT => 'This is plan directive for user in GRP_REPORT group', 
CPU_P1 => 75 
); 
dbms_resource_manager.create_plan_directive( PLAN => 'PLN_NIGHT', 
GROUP_OR_SUBPLAN => 'OTHER_GROUPS', 
COMMENT => 'This is plan directive for user in GRP_REPORT group', 
CPU_P1 => 10 
); 
end; 
/

Validate and submit the pending area

begin 
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); 
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); 
end; 
/

Grant user to switch to consumer_group

begin
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'dilli',
consumer_group => 'GRP_CONNECT',
grant_option => FALSE);
end;
/

Mapping user to group created

begin
dbms_resource_manager.create_pending_area(); 
end;
/

begin
dbms_resource_manager.set_consumer_group_mapping(
attribute => DBMS_RESOURCE_MANAGER.oracle_user,
value => 'dilli',
consumer_group => 'GRP_CONNECT' 
);
end;
/

begin 
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); 
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); 
end; 
/

Activating the resource plan

alter system set resource_manager_plan='FORCE:PLN_DAY';

Setting session to group

begin
dbms_resource_manager.switch_consumer_group_for_sess(
session_id => 139,
session_serial => 256,
consumer_group => 'GRP_REPORT'
);
end;
/