学步园

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

模拟当时的情况，通过脚本查出产生堵塞的会话和被堵塞的会话，不过可惜的是，产生堵塞会话的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;