Re: Orphan sessions

From: Mladen Gogala <>
Date: Wed, 22 Jun 2011 13:30:39 +0000 (UTC)
Message-ID: <>

On Wed, 22 Jun 2011 04:55:51 -0700, David Budac wrote:

> Hello,
> I have a question regarding "dead" inactive, orphan sessions. Sometimes,
> there's a situation in which a client application (a webserver on a
> remote machine) dies horribly and leaves an orphan session (dedicated
> server mode) in the database. The user process at the webserver no
> longer exists, but the shadow process, the database session, remains
> inactive, holding all the resources and locks - which is a bit of a
> problem as it usually blocks other transactions. In such cases, we have
> to manually identify these sessions and kill them off ourselves. The
> PMON then takes over and releases the resources and locks, as described
> in 1020720.102 and a few other notes, and leaves the sessions in the
> Killed state.
> Now, I understand that this is correct behaviour, but I'm wondering if
> there's a way or a feature that would look for such cases automatically.
> The only sort of applicable thing I found is the SQLNET.EXPIRE_TIME=xxx
> parameter but it doesn't really work for us in this case.
> The databases are Oracle (on 64bit Win2k3 machines), the
> clients are your typical IIS's using ODP.NET, using their own
> connection pooling and connecting in dedicated server mode.
> Thanks,
> David

David, the problem is usually in the processes holding these connections. If, say, Apache spawns a httpd process which is permanently connected to the database, it will not get killed because the process will not be a dead connection. The best solution that I've come up with is limiting the idle time of the process. The same applies to weblogic and tomcat. If a process acquires more than 1 hour of idle time, it shouldn't be running, period.
Alternatively, the applications should not enter long waits while in transaction. Transactions were initially modeled after the banking business, which means that they are expected to be discrete, independent and, above all other, short. The transaction should begin when the luser pushes the "submit" button and end before the form is refreshed. Waiting for input, while in transaction, is a bad coding practice. As for the platform, I have fairly decent experience with 64 bit Windows7, my impression is that it is much more stable than now fairly old Win2k3. I cannot substantiate it with numbers, it's just my impression.

Received on Wed Jun 22 2011 - 08:30:39 CDT

Original text of this message