Re: Enqueue Diagnosis

Brian Peasland <dba_at_remove_spam.peasland.com> wrote in message news:<3F3A3B4F.A37413BA_at_remove_spam.peasland.com>...
> 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

Good input... This info will give what I want to diagnose which rows are being contended for, but I only want to query v$lock, v$sessiom and v$sql when a large enqueue wait occurs. Therefore I need to be able to trigger the select on these table from my plsql script which checks for enqueue waits.

How can I call the select statement from plsql. I need to be able to spool the output to a file because I cant monitor enqueue waits interactively.

Matt Received on Thu Aug 14 2003 - 02:50:51 CDT