Re: Enqueue Diagnosis

Query V$LOCK. You will see one SID that has a TX lock and the REQUEST is mode 6. For that session, note the two values in ID1 and ID2. Now find another session that has the same values and LMODE is non-zero. That is the session that is holding the lock. Want to know which object? For those same sessions, you should see a TM lock. The ID1 column of the TM lock is the object id. With the object id, you can query DBA_OBJECTS to find out which table it is. Further more, once you know who has the lock, you can join V$SESSION and V$SQL for that SID to see what SQL statements they are running.

HTH,
Brian

Matt wrote:
>
> I have an 8.1.7 database which is starting to bottleneck on Enqueue
> waits.
>
> Every couple of days we will experience a long 'TX Mode 6' wait for
> anything from 5 mintutes to 40 minutes.
>
> I am almost 100% sure that the waits are due to a long running batch
> job which updates/deletes many rows before commiting. The application
> does not issue a 'select for update' when querying records so I have
> ruled out the problem of users opening a record for update and leaving
> their desktop without commiting the changes.
>
> I am having real problems diagnosing these waits because lock waits
> are something that is very cryptic to interpret in Oracle.
>
> I have implemented a plsql script which enables a10046 trace (level 8)
> on any session waiting for an enqueue.
>
> This will give me some feedback about what the waiter was querying at
> the time of the long enqueue wait, but doesnt tell me who is blocking
> the row.
>
> I can get this information from another script which lists holders and
> waiters of locks.
>
> What I need is a way of calling my holders/waiters sql script from my
> plsql script which is called whenever a long enqueue wait is
> occurring. I need get the output of the SQL statement only when my
> plsql procedure determines that there is an active enqueue wait.
>
> Any ideas on this one....??
>
> Matt

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"