Re: Killing disconnected Oracle sessions

In article <3ki6tm$hoe_at_ixnews4.ix.netcom.com>, rsingh_at_ix.netcom.com (Rashpal Singh) says:
>
>In <rblizard.4.0219855C_at_bb.ultranet.com> rblizard_at_bb.ultranet.com (Bob
>Blizard) writes:
>
>>
>>Does anyone know how to detect Oracle sessions which are disconnected
 from
>>their client machines, but which linger on as Oracle sessions?
>>
>>I have a client who is experiencing problems with these lost sessions
 locking
>>up other users. Specifically, they have Oracle 7.0.16 on hp9000,
 orasrv, and
>>clients running a PowerBuilder app (I didn't write and can't see under
 the
>>hood). The application has everyone logging into Oracle under 1
 account, and
>>inserting/updating 15-20 tables. There are approx 20 users of the
 database.
>>
>>What seems to happen is that every several days, the users begin to
 experience
>>'hanging' problems, which turn out to be waits for locks. PB is not
 returning
>>any kind of lock delay error. One user will hold a 'TX' lock on an
 object in
>>mode 'X', with others waiting for the same thing. Turns out that the
 session
>>is inactive, and orasrv log shows that the same ip address logged into
 the
>>database a second time an hour later than the unix process that is
 holding the
>>lock.
>>
>>So, ... there are two issues here.
>>1. Does oracle issue a warning if the lock is being held too long, or
 too many
>>lock waiters?
>>2. Is there a way to make Oracle or unix detect (and kill or report)
 these
>>disconnected sessions?
>>
>>I was considering limiting connect time via a profile, but the users
 can
>>legitimately stay on all day, so that doesn't seem to make much sense.
>>
>>All ideas accepted.
>>
>>Bob Blizard
>>
>
>We have a similar problem on IBM AIX box and have been told by
>Oracle tech support that going to SQL*Net 2.1 will resolve some
>of the problems with "dead connections" as SQL*Net 2.1 does
>a better job of "cleaning up" after itself. I would be interested
>to hear from you if
>you are running SQL*Net 2.1 and have these problems.

Use the SQL*NET 2.1 dead connection feature. SQL*NET 2.1 ships with Oracle7.1. I believe the minimum SQL*NET on the client side required for dead connection detection is 2.0.15.

FYI the problem arises because most windows TCP stacks to not enable the "keepalive" handshake properly. When the client goes away because of a GPF or whatever the TCP stack on the server side never knows the client is gone. Of course Oracle is never informed and the thread stays open within Oracle.

Generally
you won't find the problem of "hung threads" with UNIX clients as the UNIX TCP stacks do the "keepalive" handshake properly. Oracle has implemented dead connection detection by sending out an empty SQL*NET packet every "n" minutes .... you configure it via an INIT.ORA parm. You will find most client server DBs ( Sybase, Oracle etc ) all have this problem. It's not really the DB vendors problem but it affects them directly... Oracle has implemented a SQL*NET fix.

Regards
GAG Received on Mon Mar 20 1995 - 01:38:48 CET