Re: Orphan sessions

From: David Budac <davidbudac_at_gmail.com>
Date: Wed, 22 Jun 2011 07:00:48 -0700 (PDT)
Message-ID: <41459403-5ff3-40f4-be92-b254edf15fce_at_j25g2000vbr.googlegroups.com>



On Jun 22, 3:30 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> 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.com

Mladen,
the thing is, there are no user processes (httpd's in your comparison) present at the time => you can completely reboot the box on which this IIS is located and it has no effect on the database sessions whatsoever. They are still there, orphaned, holding their resources. Which is perfectly fine, in my understanding of how Oracle handles it - the client is responsible for closing them. I'm just looking for a way of getting rid of them or at least identifying them automatically. It's something that very very rarely happens and I'm trying to find a clean solution on the side of the database.

Limiting the idle time of the process is, I believe, for a completely different discussion. I'm looking for something different and it's hopefully clearer now.

Thank you for the input, I appreciate it. David Received on Wed Jun 22 2011 - 09:00:48 CDT

Original text of this message