如何查到堵塞的会话

      现场有可能出现这样的情况，实施同事修改数据后，忘记提交了（特别是新人最容易出现这种情况），从数据库报告的表象如下，一个极其简单的update产生了大量的等待（取当时的执行计划，是走了索引），其实它是被冤枉的：

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	32195	27-May-13 11:00:55	95	24.7
End Snap:	32196	27-May-13 12:00:08	105	8.6
Elapsed:		59.22 (mins)
DB Time:		400.72 (mins)

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
enq: TX - row lock contention	7,526	22,061	2,931	91.8	Application
CPU time		1,376		5.7
db file sequential read	199,193	241	1	1.0	User I/O
SQL*Net break/reset to client	517	188	364	.8	Application
db file scattered read	95,642	75	1	.3	User I/O

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
22,062	0	570	38.70	91.76	3a4rqqahh9n2q		UPDATE GG_RUNLOG_RUN_REC_DET...

模拟当时的情况，通过脚本查出产生堵塞的会话和被堵塞的会话，不过可惜的是，产生堵塞会话的SQL找不到：

session1:

   select * from test where object_id = 20 for update;

session2:

   select * from test  for update;

session3:

SELECT bs.username "Blocking User",
       bs.username "DB User",
       bs.SID "SID",
       bs.serial# "Serial#",
       bs.sql_address "address",
       bs.sql_hash_value "Sql hash",
       bs.program "Blocking App",   
       bs.machine "Blocking Machine", 
       bs.osuser "Blocking OS User", 
       bs.serial# "Serial#",                       
       ws.username "Waiting User",
       ws.SID "WSID",
       ws.program "Waiting App",
       ws.machine "Waiting Machine",
       ws.osuser "Waiting OS User",
       ws.serial# "WSerial#",
       wk.TYPE lock_type,
       hk.lmode mode_held,
       wk.request mode_requested,
       TO_CHAR(hk.id1) lock_id1,
       TO_CHAR(hk.id2) lock_id2,
       hk.BLOCK blocking_others
  FROM v$lock hk, v$session bs, v$lock wk, v$session ws
 WHERE hk.BLOCK = 1
   AND hk.lmode != 0
   AND hk.lmode != 1
   AND wk.request != 0
   AND wk.TYPE(+) = hk.TYPE
   AND wk.id1(+) = hk.id1
   AND wk.id2(+) = hk.id2
   AND hk.SID = bs.SID(+)
   AND wk.SID = ws.SID(+)
   AND (bs.username IS NOT NULL)
   AND (bs.username <> 'SYSTEM')
   AND (bs.username <> 'SYS')
 ORDER BY 1;

Blocking User	TEST
DB User	TEST
SID	13
Serial#	12
address	0
Sql hash	0
Blocking App	PlSqlDev.exe
Blocking Machine	COMTOP\HLPNT2X
Blocking OS User	COMTOP\guogang
Serial#	12
Waiting User	TEST
WSID	18
Waiting App	PlSqlDev.exe
Waiting Machine	COMTOP\HLPNT2X
Waiting OS User	COMTOP\guogang
WSerial#	6
LOCK_TYPE	TX
MODE_HELD	6
MODE_REQUESTED	6
LOCK_ID1	196641
LOCK_ID2	1548
BLOCKING_OTHERS	1