Re: Orphan sessions
Date: Wed, 22 Jun 2011 13:30:39 +0000 (UTC)
Message-ID: <pan.2011.06.22.13.30.38_at_gmail.com>
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 10.2.0.4 (on 64bit Win2k3 machines), the
> clients are your typical IIS's using ODP.NET 11.2.0.2, 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.
-- http://mgogala.byethost5.comReceived on Wed Jun 22 2011 - 08:30:39 CDT