RE: understanding orphaned processes (v$process > v$session)

I'll reply with an experience we have had.   Situation:  ODBC connection 
pooling on IIS.  At unpredictable intervals, we would find many 'orphaned' 
connections.  Details inline.  

-----Original Message-----
From:   oracle-l-bounce@xxxxxxxxxxxxx on behalf of Singh, Ratnesh (GEI, GEFA, 
Contractor)
Sent:   Fri 4/30/2004 11:42 AM
To:     oracle-l@xxxxxxxxxxxxx
Cc:     
Subject:        understanding orphaned processes (v$process > v$session)
Hi=20

We are getting "com.ibm.websphere.ce.cm.StaleConnectionException: =
Connection is closed"  error in our Application.
The following qry returns me ~ 70 rows.
select count(*) from v$process where addr not in (select paddr from =
v$session);

We are not using MTS.
I believe it is these orphaned processes that are responsible for these =
errors.

1. I'm trying to understand what actions cause orphaned processes 

So would we.  After a migration to 9i, things happened, and I now believe that 
it is due to bad code:  Your code is not closing and destroying all your 
cursors.  Your envrionment can only clean up so much for you.  Eventually, the  
 Oracle Net/Web Server interface gets confused, and everything gets closed.


2. Can we create/reproduce orphaned processes at will ?

Can you?  We had a hard time doing it.  

3. Why isnt Oracle able to clean up these orphaned processes =
automatically ?

Because they were being dropped on the client.  To the database server, it 
looks like you have an inactive connection.  Try running netstats on both boxes.

4. How to clean up these orphaned processes on Oracle side ?

Fast way:  Kill each one by hand.

Slow way:  Turn on dead connection detection.  When Oracle realizes that the 
connection is truly gone, it will clean it up.  But, in the absense of DCD, it 
will never look.

5. How to inform the application connection pool that these sessions are =
no longer valid ?

From the application connection pool's point of view, it probably already knows 
that.  Our's did.  The TCP sockets were gone on the web server.  Are your 
processes still running?  The problem is that they were dropped unpolitely on 
the web server.

I hope something here helps.  I know this was a real puzzler, and I don't yet 
think I understand it fully.


thanks & regards


-- Binary/unsupported file stripped by Ecartis --
-- Type: application/ms-tnef
-- File: winmail.dat


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: