enqueue(ロック) の 待機させている方(ホルダー) と 待機している方(ウェイター) を出力する SQL

Oracle Database の enqueue(ロック) の 待機させている方(ホルダー) と 待機している方(ウェイター)を出力するには、V$LOCKビューを参照します。以下の SQL を実行します。

SET LINESIZE 300;
COLUMN SESS FORMAT A20;
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;

出力例について、まずは仕込みです。TBL_X表の同一レコードを
複数セッションから更新して、行ロック待ちを発生させます。

★セッション１
SQL> SELECT * FROM TBL_X;
        C1 C2
---------- ----------
         2 BBB
         3 CCC
SQL> UPDATE TBL_X SET C2 = 'bbb' WHERE C1 = 2;
1 row updated.

★セッション２
SQL> DELETE FROM TBL_X WHERE C1 = 2;
※セッション１と同じレコードを更新しているので、待たされる。

以下のような感じで出てきます。今回は行ロック待ちなので TXエンキュー です。

★セッション３
SQL> L
  1  SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
  2            sid sess, id1, id2, lmode, request, type
  3     FROM V$LOCK
  4   WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
  5*    ORDER BY id1, request
SQL> /

SESS                        ID1        ID2      LMODE    REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 191              655370       3038          6          0 TX ← ★ 待たしている方(ホルダー)    ★
Waiter: 196              655370       3038          0          6 TX ← ★ 待たされている方(ウェイター)★

V$SESSION の BLOCKING_SESSION列 でも参照可能です。こっちの方が簡単ですかね(ﾟεﾟ )
以下のような SQL を実行します。単に ACTIVE なセッションを出力してるだけですが。

COLUMN SID FORMAT 99999;
COLUMN EVENT FORMAT A40;
COLUMN BINS FORMAT 999;
COLUMN BSID FORMAT 99999;
SELECT SID, SERIAL#, SQL_ID, EVENT, BLOCKING_INSTANCE AS BINS, BLOCKING_SESSION AS BSID
FROM V$SESSION WHERE STATUS = 'ACTIVE' AND USERNAME IS NOT NULL;

 SID  SERIAL# SQL_ID        EVENT                          BINS   BSID
---- -------- ------------- ------------------------------ ---- ------
 196     4915 97kq8c153s474 enq: TX - row lock contention     1    191
 ^^^★ウェイターの　　　　　　　　　　　　　　　　　　　　　　　　 ^^^★ホルダーの
　　　セッション　　　　　　　　　　　　　　　　　　　　　　　　　　　　セッション

行ロック待ちなのでウェイターが「enq: TX - row lock contention」の待機イベントで
待っていて、BLOCKING_SESSION列にホルダーのセッションが出力されます。

参考マニュアルは下記となります。

Oracle Databaseパフォーマンス・チューニング・ガイド
11gリリース2 (11.2)
B56312-06
10 パフォーマンス・ビューを使用したインスタンスのチューニング
10.3.6.1 ロックおよびロック・ホルダーの検索
http://docs.oracle.com/cd/E16338_01/server.112/b56312/instance_tune.htm#sthref751