We are having three tier architecture, 2 web server + 10 app server + 1 DB. and we do have some online request + batch request coming to the database from the app servers. I want to use 'database consumer group' for deviding the requests , such that i can have different 'timeouts/cpus/no of active sessions/parallel degrees' for online and batch requests, that a better control over Database so that unnecessary batch should not eat 100% CPU during day time resulting bad online user experience and reverse during night. And for all of tha application request(online+batch), oracle user is same that is 'app_user'. So i cant restrict the timout for them by deviding them using 'oracle user', because timeout should be fifferent for online and batch. So one thing comes into my mind, during the database call, i am getting different client_machine name i.e. app server names from which request are coming. So if i can group the app server machines into two bunch, one for online and other for batch request and put them into two different database consumer group, then i can restrict different time out for each of them


Issue:
------
Current issue i am facing, issue with assigning machines into different consumer group. Even if i am giving particular machine name in the procedure 'dbms_resource_manager.set_consumer_group_mapping' and expecting the consumer group will be mapped to that particular machine during run time, if any request comes from that machine. but its not working that way . The consumer group gets assigned to the oracle user, which is mentioned in 'dbms_resource_manager_privs.grant_switch_consumer_group' procedure, even if machine name is different.

So just want to know, if my understanding of deviding machines based on database consumer groups is implementable as per the current oracle version 11.2.0.3.0. Or there is some issue with my script somewhere?

Here i have taken the oracle user 'test' in place of 'app_user', just for testing purpose.

PHP Code:
Begin
-- create the pending area
dbms_resource_manager.create_pending_area();
END;
/
BEGIN
-- Create the consumer group
dbms_resource_manager.create_consumer_group(
    CONSUMER_GROUP=>'ONLINE_USERS_LIMITED_EXEC_TIME',
    COMMENT=>'This is the consumer group that has limited execution time per statement'
    );
END;
/

BEGIN
--  We need a consumer group that maps to the desired machine:
  dbms_resource_manager.set_consumer_group_mapping(
    attribute =>  dbms_resource_manager.client_machine,
    value => 'Wabcd',
    consumer_group =>'ONLINE_USERS_LIMITED_EXEC_TIME'
  );
END;
/

/*
begin
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
   explicit          => 6, 
   oracle_user       => 2, 
   service_name      => 3, 
   client_os_user    => 4, 
   client_program    => 5, 
   client_machine    => 1, 
   module_name       => 7, 
   module_name_action=> 8,
   service_module    => 9,
   service_module_action  => 10);
end;
/
*/
   
BEGIN
  -- Now create a resource plan:
  dbms_resource_manager.create_plan(
    PLAN=> 'LIMITED_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time'
  );
END;
/
-- Now let's create a plan directive for that special user group , the plan will cancel the current SQL if it runs for more than 120 sec
-- You can define multiple directives depending on requirement

BEGIN
dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMITED_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'ONLINE_USERS_LIMITED_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time',
    SWITCH_GROUP=>'CANCEL_SQL',
    SWITCH_TIME=>15,
    SWITCH_ESTIMATE=>false
  );

  --Basically for each plan we need to define the plan directives for the OTHER_GROUPS also, what that means is determine limits for all other sessions other than those bound by the consumer group ONLINE_USERS_LIMITED_EXEC_TIME.

  dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMITED_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
    COMMENT=>'leave others alone',
    CPU_P1=>100
  );
  END;
/


exec dbms_resource_manager.validate_pending_area(); 

exec dbms_resource_manager.submit_pending_area(); 

exec dbms_resource_manager_privs.grant_switch_consumer_group('test','ONLINE_USERS_LIMITED_EXEC_TIME',false);
--exec dbms_resource_manager_privs.grant_switch_consumer_group('test1','ONLINE_USERS_LIMITED_EXEC_TIME',false);


--exec dbms_resource_manager.set_initial_consumer_group('test','ONLINE_USERS_LIMITED_EXEC_TIME');


--exec dbms_resource_manager.create_pending_area(); 

alter system set RESOURCE_MANAGER_PLAN='LIMITED_EXEC_TIME';