Re: Hung connections

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 1998/04/16
Message-ID: <6h4bni$nf6$1_at_hermes.is.co.za>#1/1


_vivek_at_yahoo.com <_vivek_at_yahoo.com> wrote in message <6gsqi2$d3o$1_at_nnrp1.dejanews.com>...

>How do I identify whether a database connection (remote/local) is active,
>inactive or has hung ?
>I've done a lot of research on the v$ views as well as tinkering with the
>sql*net parameters. Nothing seems give me the information reqd.

What do you mean with "hung"? Simplistically speaking, either one of two things can hang. The client application is not responding (not really Oracle's problem), or due to some bug the actual Oracle process on the server got itself into a knot.

If the client process hang or terminate unexpectedly, you ideally want that client's processes in Oracle to be terminated to. In this case you can set the DCD (dead connection detection) parameter in the listener's config file to clean up after a dead client.

If an Oracle process is hanging - well, that is extremely difficult to detect if no obvious error message is generated. And a process that looks like its hanging may also just be a SQL statement that is either very inefficient, or got parsed into some weird execution plan.

The question is why do you want to detect a hung session? If you often get processes that seems to be hanging, you can look at that process' session in v$session and the session's data in v$session_wait, v$session_event and v$sesstat. You can even use Unix's truss command to debug the system calls that are made by that process. However, this may be an excercise in futility as the problem may be an obscure Oracle or operating system kernel bug.

You may also want to contact Oracle support, find out about patches and bugfixes that may be applicable to the Oracle version and platform you're using.

regards,
Billy Received on Thu Apr 16 1998 - 00:00:00 CEST

Original text of this message