Re: Crashed client application leaves Oracle connection behind

On Jul 16, 3:44 am, André Hartmann <andre.hartm..._at_hotmail.de> wrote:
> Hello everyone,
>
> I would like to describe a phenomenon which we get occasionally with one of our applications and see whether people have observed
> similar before or have an idea as to how to approach this problem in terms of analysis, debugging or so...
>
> The application is done in Visual C++ (MS Visual Studio 2003) and it acts as an Oracle client application. We currently use the
> Oracle 9 client and server software on Windows 2000 and XP and we are about to shift to Oracle 10. But the phenomenon that I am
> describing occurs under Oracle 9 and Oracle 10 and all supported OSs. We use the Oracle Call Interface OCI to communicate with the
> database.
>
> Our application will open a database connection and process some transactions there. At some stage the application may crash. That
> is unpleasant but that's not the issue I want to write about here. We are working on the crash also and hope to eliminate it some
> day soon. However what we observe when such a crash occurs (in the middle of a transaction) is, that the application window will go,
> the typical Microsoft report dialogue will show up and the application process is not visible anymore in the task manager (not in
> the applications tab amd also not in the processes tab).
>
> So far so good, that's all as expected. However sometimes the Oracle server will still have the connection to the (already
> vanished) application in its list of sessions. We observe this using the Oracle Enterprise Manager. This does not happen with EVERY
> such crash but only once in a while. Typically when killing our application intentionally via the task manager or a kill tool, no
> Oracle session will be left over. Only when a crash occurs and even then only sometimes we will experience that.
>
> The session will go after a while, like half an hour. Then it wont be visible via the OEM anymore and locks and other resources
> that the transaction claimed are released by Oracle.
>
> Users reported to us that this scenario is especially nasty because when they experience the crash and they restart our
> application, they cannot resume their work because "work" would include imposing locks on resources that the orphaned session still
> holds...
>
> Users also reported that it helps to log off / log on their Windows work stations, which would eradicate the session. We couldnt
> reproduce this behaviour here in our developing environment. We get the crash and the orphaned session but cannot mend it by log off
> / log on. That's a bit strange because so far the problem sounded to me like a server-side issue... but I am not sure anymore.
>
> Thanks very much in advance for your input,
>
> André
> Berlin, Germany
> :)

These are known as runaway or dead connections. Many shops write scritps to detect and eliminate these connections along with ones that have been idle too long.

There are numerous ways to deal with this issue:

Many shops just write a script that look at the v$session.status column for sessions that are INACTIVE and that have a large last_call_et that exceeds some value and then kills these sessions. You could also look at v$transaction and use a shorter or different time period for inactive sessions holding locks. Search the archives for more information on this approach (perhaps using v$session runaway or v$session dead connections)

Oranet provides a dead connection detection feature you can configure providing your database does not need to support any applications that go idle for hours on end.

See metalink for the article: Dead Connection Detection (DCD) Explained #151972.1

There is an idle time parameter on the user profile (create profile) feature that might be of use. See the DBA Administration manual and the SQL manual.

HTH -- Mark D Powell -- Received on Mon Jul 16 2007 - 09:00:42 CDT