How to invoke Oracle clean up process to remove INACTIVE sessions?

  • From: <arul.kumar@xxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 10 Nov 2004 12:11:23 -0000

Hi DBAs,
My system makes huge number of calls to remote databases through db links. The 
sessions made through the db links are open at the remote databases for a long 
time. Though, Oracle could clean up the INACTIVE sessions, it "considers" the 
sessions as INACTIVE only after approximately 4 - 5 hours which sounds like too 
much time for us.

By any chance can we control this "auto-cleanup" timing and bring it down to 
around 30 minutes or so. Every INACTIVE session after 30 minutes, should be 
killed? Please let me know your views on the same.

And, can the remote databases apply any upper limit on the connections and once 
it is reached, re-use the existing connections for further requests without 
using MTS? ( we tried pre spawned server processes (listener.ora) but it is 
still not enforcing the re-use of existing connections!)

1st snapshot

SELECT s.machine "Machine", count(*), min(s.logon_time) "Connect Time",

max(SYSDATE - (s.last_call_et / 86400)) "Last Call", min(SYSDATE - 
(s.last_call_et / 86400)) "Min_Last_Call"

FROM v$session s, v$process p, SYS.v_$sess_io si

WHERE s.paddr = p.addr(+) AND si.SID(+) = s.SID

and machine like '%e10k%'

group by s.machine

ORDER BY 1 DESC

Machine,     COUNT(*),    Connect Time,            Last Call,                   
   Min(Last_Call)

A ,                94,                09/11/2004 22:12:11, 10/11/2004 11:17:21, 
10/11/2004 06:38:12 

B ,                91,                 09/11/2004 22:05:04, 10/11/2004 
11:17:21, 10/11/2004 07:55:42

C ,               85,                  09/11/2004 22:05:49, 10/11/2004 
11:17:21, 10/11/2004 09:46:19

After a while.... 2nd snapshot 

Machine,     COUNT(*),    Connect Time,            Last Call,                   
   Min(Last_Call)

A ,               91,                 09/11/2004 22:12:11, 10/11/2004 11:26:26, 
10/11/2004 06:42:35

B ,              90,                  09/11/2004 22:05:04, 10/11/2004 11:26:26, 
10/11/2004 07:55:51

C ,             77,                   09/11/2004 22:05:49, 10/11/2004 11:26:26, 
10/11/2004 09:46:28

Note - The Last column reveals that the INACTIVE sessions in the first snapshot 
are disappearing VERY slowly.

Environment

RDBMS : 8.1.7

OS : Solaris at source and different OS at remote databases.

Thanks,

Arul.


--
//www.freelists.org/webpage/oracle-l

Other related posts: