Oracle regularly cleans up inactive sessions

2014-03-31 22:25 by Xiaoxiang Hermit, 21797 reads, 12 Comments, Favorites, compilation

Oracle database sessions have five states of active, INACTIVE, killed, CACHED, sniped. A inactive state session indicates that the session is inactive, idle, and waiting. For example, PL/SQL developer is connected to a database, and after executing a statement, the session is in the inactive state if the SQL statement is not resumed. In general, a small number of Inactvie sessions have no impact on the database, if due to some reasons such as program design causes the database to have a large number of sessions in inactive state, it will result in a large number of system resources are consumed, resulting in more sessions than the maximum system session, The ora-00018:maximum number of sessions exceeded error appears.

Sometimes it is necessary to clean up those conversations that have been in inactive state for a long time. It must be unrealistic to periodically check and kill such conversations, and to regularly clean up those long-inactive sessions, which can only be done through homework; It is also important to note that killing these sessions requires caution, and a little attention may kill some normal sessions. So how do we define this kind of conversation? Here's what I've defined in conjunction with business rules:

1: The status of the session must be inactive, if the session state is active, killed, CACHED, sniped state, do not consider.

2: The session must have been in the inactive state for a long time. For example, if you are in a inactive state that exceeds the two-hour session process, consider kill. Depending on the business or needs, it is possible to kill the session process in more than half an hour. As for how to calculate the time in the inactive session state, this can be identified by the V$session last_call_et field, you need to query the inactive state for two hours or more sessions, you can pass the query conditions S.last_call_et >= 60*60*2 Implementation, of course, preferably written s.last_call_et >= 7200

3: The program that connects to the session. For example, inactive sessions generated by a particular application are cleaned up. For example, the Toad tool, the PL/SQL developer tool. About program This needs to be set according to the current project situation, the following only uses TOAD.EXE, w3wp. EXE for illustrative purposes.

   From v$SESSION S

   In (' w3wp. EXE ')

   and S.last_call_et >= 60*60*2

   DESC;

In the case of a RAC environment, it is best to use the following SQL statement, using the global view gv$session.

   From gv$session S

   In (' w3wp. EXE ')

   and S.last_call_et >= 2 * 60*60

   by inst_id DESC

Next, create the stored procedure sys.db_kill_idle_clients. It is convenient to invoke this function to perform a kill inactive session. Note:the xxx section is replaced with the actual business program.

   3:    job_no number;

   4:    num_of_kills Number: = 0;

   8:        (module,status

  Ten:             NULL

  : and                  s.last_call_et >= 2*60*60                              

  +:         ASC

  :          ---------------------------------------------------------------------------

  :          ---------------------------------------------------------------------------

           Dbms_output. PUT (' LOCAL SID ' | | rec.sid | | ' (' | | | rec.module | | ‘)‘);

  : "'                             immediate '; 

  At:          Dbms_output. Put_Line ('. Killed locally ' | | job_no);

  :          num_of_kills: = num_of_kills + 1;

  :    END LOOP;

  A:    dbms_output. Put_Line (' number of killed XXXX system sessions: ' | | num_of_kills);

  END db_kill_idle_clients;

1: CREATE OR REPLACE PROCEDURE SYS. Db_kill_idle_clients AUTHID definer as

   2:    job_no number;

   3:    num_of_kills Number: = 0;

   7:        (module,status

   9:             NULL

  One: and                  s.last_call_et >= 2*60*60                              

' Killed '

  :             ) LOOP

           --Kill inactive sessions immediately

           Dbms_output. PUT (' LOCAL SID ' | | rec.sid | | ' (' | | | rec.module | | ‘)‘);

  : '                             immediate '; 

  A:          dbms_output. Put_Line ('. Killed locally ' | | job_no);

            Num_of_kills: = num_of_kills + 1;

  :    END LOOP;

  :    dbms_output. Put_Line (' number of killed system sessions: ' | | num_of_kills);

  END db_kill_idle_clients;

1: #!/bin/bash

   ">> $logfile 2>&1

   "START----' Date '" >> $logfile 2>&1

   9:sqlplus/nolog <<stats

  11:exec sys.db_kill_idle_clients;

  12:exit;

  "END------' Date '" >> $logfile 2>&1

Configure background jobs in crontab, run every 15 minutes, and clean up any idle sessions that meet the criteria.

0,15,30,45 * * * */home/oracle/cron/session/bin/killsession.sh >/dev/null 2>&1

Oracle regularly cleans up inactive sessions