0 Replies Latest reply: Sep 24, 2013 11:38 AM by scarneiro RSS

    Resource Manager does not switch to correctly consumer group

    scarneiro

      Hi,

       

      I would like some help to undestand why a particular osuser do not have been assigned to a expected resource plan that we wrote to prevent some lock in our database.

       

      Following is my script executed as SYS:

       

       

       

      begin
      
      dbms_resource_manager.clear_pending_area();
      dbms_resource_manager.create_pending_area();
      
      dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>'GISS_SYNC_GROUP',COMMENT=>'Sync Consumer group');
      
      dbms_resource_manager.create_plan(PLAN=>'GISS_SYNC_PLAN',COMMENT=>'Sync Plan');
      dbms_resource_manager.create_plan_directive(PLAN=> 'GISS_SYNC_PLAN',GROUP_OR_SUBPLAN=>'GISS_SYNC_GROUP',COMMENT=>'Kill blocker sessions greater than 1',MAX_IDLE_BLOCKER_TIME=>1);
      dbms_resource_manager.create_plan_directive(PLAN=> 'GISS_SYNC_PLAN',GROUP_OR_SUBPLAN=>'OTHER_GROUPS',COMMENT=>'Leave another sessions alone',cpu_p1=>100);
      
      dbms_resource_manager.set_consumer_group_mapping(attribute=>DBMS_RESOURCE_MANAGER.CLIENT_OS_USER,value=> 'scarneiro' ,consumer_group => 'GISS_SYNC_GROUP');
      
      dbms_resource_manager.validate_pending_area();
      dbms_resource_manager.submit_pending_area();
      
      end;
      /
      
      
      

       

      After that, we activated the new resouce plan as SYS:

       

      ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='GISS_SYNC_PLAN';
      
      
      

       

      So, when we connect to the database from "scarneiro" osuser and we expected it was assigned to a GISS_SYNC_PLAN, although the osuser is assign to OTHER_GROUP.

       

       

       

       

      SQL> select inst_id,osuser,username,resource_consumer_group from gv$session where osuser='scarneiro';
      
      I OS User          Username         RESOURCE_CONSUMER_GROUP
      -- ---------------- ---------------- --------------------------------
      1 scarneiro        GISS             OTHER_GROUPS
      1 scarneiro        GISS             OTHER_GROUPS
      1 scarneiro        GISS             OTHER_GROUPS
      
      
      

       

      What I have to do to force the osuser "scarneiro" be assigning to GISS_SYNC_PLAN?

       

      Thanks in advance