Re: What is the correct steps to solve the hanging session problem?

zilanyang_at_hotmail.com (nancy) wrote in message news:<1c02a922.0303052316.3358e8dd_at_posting.google.com>...
> If a customer calls you about a hanging database session(Oracle
> 8.1.7), what will you do to resolve it?
> I think there must be many possible reasons to cause this problem,
> such as resource contention or others. what is the correct steps to
> solve it?
>
> Thanks

Zilan, Here is how I go about looking into reported hanging problems that do not appear to be system wide:

1- Check the system for lock waited sessions using the lockwait column of v$session

2- If only one or two sessions then I manuall chase down the blocking session using v$lock and v$session. V$session contains rowid columns if the lock is for the same row

3- Use one of the v$sqlx views liek v$sqlarea or v$sqltext I look at the SQL being ran.

4- If the user is locking themselves we ask them to check their other session windows or if they had exited by clicking the X in the upper right handle corner of their MS client. If so, I kill the locking session.

If the lock holder is another user we ask if they work in the same department. They usually do and they are often on the phone or in a meeting. We have the locked user ask them to complete the screen or exit them out and problem solved.

Back to item 2 above. If there are many locked session we run a lock blocking script like the one Oracle provides in $ORACLE_HOME/rdbms/admin/utllockt. Check to see what SQL the holder and waiter are running.

If there are no locked sessions or an unusually large number of locked sessions check v$session_wait to see what Oracle tells you the waiting session is waiting on.

See the Oracle 9i Reference Manaul for documentation on the v$ tables (dynamic performance views).

HTH -- Mark D Powell -- Received on Thu Mar 06 2003 - 18:11:30 CET