如何查到堵塞的会话

来源：互联网发布：台湾国民党知乎编辑：程序博客网时间：2024/04/29 01:23

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

Snap IdSnap TimeSessionsCursors/SessionBegin Snap:3219527-May-13 11:00:559524.7End Snap:3219627-May-13 12:00:081058.6Elapsed: 59.22 (mins) DB Time: 400.72 (mins)

EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Classenq: TX - row lock contention7,52622,0612,93191.8ApplicationCPU time 1,376 5.7 db file sequential read199,19324111.0User I/OSQL*Net break/reset to client517188364.8Applicationdb file scattered read95,642751.3User I/O

Elapsed Time (s)CPU Time (s)ExecutionsElap per Exec (s)% Total DB TimeSQL IdSQL ModuleSQL Text22,062057038.7091.763a4rqqahh9n2q 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 UserTESTDB UserTESTSID13Serial#12address0Sql hash0Blocking AppPlSqlDev.exeBlocking MachineCOMTOP\HLPNT2XBlocking OS UserCOMTOP\guogangSerial#12Waiting UserTESTWSID18Waiting AppPlSqlDev.exeWaiting MachineCOMTOP\HLPNT2XWaiting OS UserCOMTOP\guogangWSerial#6LOCK_TYPETXMODE_HELD6MODE_REQUESTED6LOCK_ID1196641LOCK_ID21548BLOCKING_OTHERS1