News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Using the 10g Resource Manager to manage INACTIVE sessions

  • Posted by Gavin Soorma
  • On June 25, 2009
  • 0 Comments

CREATE THE PLAN

 begin
dbms_resource_manager.create_pending_area();
end;
/
 begin
dbms_resource_manager.create_plan( plan => 'TEST_PLAN', comment => 'Resource plan/method for Idle
time kill sessions');
end;
/

CREATE THE CONSUMER GROUPS

begin
dbms_resource_manager.create_consumer_group( consumer_group => 'LONG_RUNNING',
comment =>'Privileged Users');
dbms_resource_manager.create_consumer_group( consumer_group => 'SHORT_RUNNING',
comment => 'Under Privileged Users');
end;
/

CREATE DIRECTIVES FOR THE PLAN

  • Consumer Group LONG_RUNNING will have idle time set to 5 MINUTES (300 seconds)
  • Consumer Group SHORT_RUNNING will have idle time set to 1 Minute (60 seconds)
  • Users falling in the Consumer Group OTHER_GROUPS will have idle time set to 1 Hour
  • begin
     dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan =>
    'LONG_RUNNING', comment => 'Limit idle time to 5 minutes', max_idle_time => 300);
    dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan =>
    'SHORT_RUNNING', comment => 'Limit idle time to 1 minute', max_idle_time => 60);
    dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan =>
     'OTHER_GROUPS', comment => 'Limit idle time to 1 Hour', max_idle_time => 3600);
     end;
     /
    

    VALIDATE AND SUBMIT THE PLAN

    begin
    dbms_resource_manager.validate_pending_area();
    end;
    /
    
    begin
    dbms_resource_manager.submit_pending_area();
    end;
    /
    

    ALLOW OR DISALLOW CONSUMER GROUP SWITCHING

    begin
    dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'SYSTEM',
    consumer_group => 'LONG_RUNNING', grant_option => FALSE);
    dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'SH',
    consumer_group => 'SHORT_RUNNING', grant_option => FALSE);
    end;
    /
    

    ASSIGN DATABASE USERS A DEFAULT INITIAL CONSUMER GROUP AT CONNECTION TIME

  • User SYSTEM is made member of the group LONG_RUNNING
  • User SH is made member of the group SHORT_RUNNING
  • 
    begin
    dbms_resource_manager.create_pending_area();
    end;
    /
    
    
    begin
    dbms_resource_manager.set_initial_consumer_group( user => 'SYSTEM',
    consumer_group => 'LONG_RUNNING');
    dbms_resource_manager.set_initial_consumer_group( user => 'SH',
    consumer_group => 'SHORT_RUNNING');
    end;
     /
    
    

    VALIDATE AND SUBMIT THE LAST ACTIONS MADE TO THE PLAN

     begin
    dbms_resource_manager.validate_pending_area();
    end;
    /
    
    
    begin
    dbms_resource_manager.submit_pending_area();
    end;
    /
    
    

    MAKE THE NEW PLAN THE ACTIVE ONE FOR THE DATABASE

    SQL> alter system set resource_manager_plan=’TEST_PLAN’;

    TEST

    This query will show the sessions that are killed when the idle time thresholds set for the plan are crossed.

     SQL> 	select NAME, ACTIVE_SESSIONS_KILLED, IDLE_SESSIONS_KILLED
    	FROM V$RSRC_CONSUMER_GROUP WHERE NAME='SHORT_RUNNING';
    
    NAME                             ACTIVE_SESSIONS_KILLED IDLE_SESSIONS_KILLED
    -------------------------------- ---------------------- --------------------
    SHORT_RUNNING                                         0                    0
    
    

    Initially this will show no sessions are killed

    Connect as user SH from another session

    
    apex:/u01/oracle/scripts> sqlplus sh/SH
    
    SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 12 10:26:54 2008
    
    Copyright (c) 1982, 2007, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL>
    

    After ONE Minute has elapsed from the first session run the same query again

    SQL> 	select NAME, ACTIVE_SESSIONS_KILLED, IDLE_SESSIONS_KILLED
    	FROM V$RSRC_CONSUMER_GROUP WHERE NAME='SHORT_RUNNING';
    
    NAME                             ACTIVE_SESSIONS_KILLED IDLE_SESSIONS_KILLED
    -------------------------------- ---------------------- --------------------
    SHORT_RUNNING                                         0                    1
    

    It now shows that one session has been killed

    Confirm that the session has actually been killed

    SQL> quit
    ERROR:
    ORA-02396: exceeded maximum idle time, please connect again
    
    
     

    0 Comments

    Leave Reply

    Your email address will not be published. Required fields are marked *