RE: understanding orphaned processes (v$process

From: Singer, Phillip (P.W.) <psinger1_at_ford.com>
Date: Fri, 30 Apr 2004 16:12:22 -0400
Message-ID: <A45063A7D336504580F0161CEB7FEBE20149CA30@na1fcm60.dearborn.ford.com>

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_at_freelists.org on behalf of Singh, Ratnesh (GEI, GEFA, Contractor)
Sent:	Fri 4/30/2004 11:42 AM
To:	oracle-l_at_freelists.org
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.

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Apr 30 2004 - 15:13:00 CDT

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