Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> How to invoke Oracle clean up process to remove INACTIVE sessions?

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

From: <arul.kumar_at_bt.com>
Date: Wed, 10 Nov 2004 12:11:23 -0000
Message-ID: <83FC2D1BC95D884894735B11B562A41C029B5348@i2km06-ukbr.domain1.systemhost.net>


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.

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 10 2004 - 06:05:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US