Re: enqueue wait objects?

From: Paul Baumgartel <treegarden_at_yahoo.com>
Date: Fri, 20 Feb 2004 14:59:35 -0800 (PST)
Message-ID: <20040220225935.5111.qmail@web13423.mail.yahoo.com>

It depends on the type of enqueue, of course, but for a row wait (i.e., waiting on a locked row):

SELECT sid, taddr, lockwait, status, sql_address, row_wait_obj# RW_OBJ#, row_wait_file# RW_FILE#, row_wait_block# RW_BLOCK#, row_wait_row# RW_ROW#
FROM v$session
WHERE sid = &sid
ORDER BY sid;

The status for a waiting session is ACTIVE. SQL_ADDR is the address for the last SQL statement that the session executed; you can get the SQL text by querying V$SQL. For the waiter, this is the SQL that caused the wait. For the blocker this could be the SQL that caused the block, or any SQL executed since.

If ROW_WAIT_OBJ# has a value other than -1, then it means that the session is waiting on a lock for this object. The object information can be obtained by joining with DBA_OBJECTS.OBJECT_ID or DBA_OBJECTS.DATA_OBJECT_ID. The other RW* columns identify the row (by file id, block id, row number) that the waiting session wants to lock.

DBA_OBJECTS can tell you the table owner and the table name.

• solbeach_at_cox.net wrote:
  > The SQL below allows me to identify users involved with enqueue
  > waits.
  > =======================================
  > SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
  > id1, id2, lmode, request, type
  > FROM V$LOCK
  > WHERE (id1, id2, type) IN
  > (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
  > ORDER BY id1, request
  > =========================================
  > How do I identify which database objects are involved with an enqueue
  > wait?
  >
  >
  > ----------------------------------------------------------------
  > Please see the official ORACLE-L FAQ: http://www.orafaq.com
  > ----------------------------------------------------------------
  > To unsubscribe send email to: oracle-l-request_at_freelists.org
  > put 'unsubscribe' in the subject line.
  > --
  > Archives are at http://www.freelists.org/archives/oracle-l/
  > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  > -----------------------------------------------------------------
  

  ---

Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 20 2004 - 17:20:15 CST