如何查找产生阻塞的SQL？

显示全部楼层 · 2009-10-9 08:28:00

如何查找产生阻塞的SQL？
测试：
session1: 执行： select * from test_doindex where id=1 for update;
此时调整之后不做提交也不回退,并打开多个session，并执行相同的SQL，使其产生锁等待，
如下：
session2: 执行： select * from test_doindex where id=1 for update;
session3: 执行： select * from test_doindex where id=1 for update;
session4: 执行： select * from test_doindex where id=1 for update;
session5: 执行： select * from test_doindex where id=1 for update;
session6: 执行： select * from test_doindex where id=1 for update;
此时查询dba_waiters,可以发现
WAITINGHOLDINGLOCK_TYPEMODE_HELDMODE_REQUESTEDLOCK_ID1LOCK_ID2
137138TransactionNoneExclusive196640427
138139TransactionNoneExclusive196640427
137139TransactionNoneExclusive196640427
154140TransactionExclusiveExclusive196640427
141140TransactionExclusiveExclusive196640427
137140TransactionExclusiveExclusive196640427
154141TransactionNoneExclusive196640427
141141TransactionNoneExclusive196640427
此时可以通过HOLDING的SID找到session 140,但此时v$session里SQL_ID已经为空。
无法查到当生锁等待的session究竟是在执行什么SQL语句？那当然对应的open_courr也未能查到，
请问那位高人有好的方法可以指点一二？
持有锁的session未能提交或回退，status已变为inactive，无法查到当前SQL，而上一条SQL明显也不是产生锁等待的SQL。
如何找出，产生锁的SQL呢？

千问 · 2009-10-9 08:28:00

http://www.inthirties.com/thread-216-1-1.html
见上面链接的最后一个sql语句

千问 · 2009-10-9 08:28:00

楼上所指的SQL，就是以下这句SQL，但并不能查出产生锁的语句。
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
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;

千问 · 2009-10-9 08:28:00

是有这个问题。
SQL_ID总是为空，可以查询被阻塞的 SQL，却查不到产生阻塞的SQL。。。。

千问 · 2009-10-9 08:28:00

呵呵
你先找到是那个session
在session1中
select * from t1 where object_id = 2 for update
在session2中
select * from t1 where object_id = 2 for update
在另外一个session3中
select sid,serial#,username,sql_id,prev_sql_id from v$session where username = 'DZ';
SIDSERIAL# USERNAME
SQL_IDPREV_SQL_ID
---------- ---------- ------------------------------ ------------- -------------
138 2979 DZ
bj081vdtrs6q2
152 9317 DZ
bj081vdtrs6q2 dyk4dprp70d74
lz可以判断是138这个session的sql堵塞了别的sql 通过PREV_SQL_ID这个字段就可以判断，造成堵塞的sql。
select sql_text from v$sql where sql_id = 'bj081vdtrs6q2';
SQL_TEXT
--------------------------------------------------------------------------------
select * from t1 where object_id = 2 for update

千问 · 2009-10-9 08:28:00

如果你在session1中执行
select * from t1 where object_id = 2 for update这个后又执行了别的sql就找不到这条语句了

千问 · 2009-10-9 08:28:00

通常在实际应用中，session中常常是执行事务，而这个事务需要处理多种数据，当然事务中的SQL语句也复杂而多。
单非简单的几句SQL.我们所找到产生阻塞的session，其中的 SQL_ID和PREV_SQL_ID未必就是产生锁的SQL。
可能整个事务的第一句SQL就是已使用了锁资源。而ORACLE并没有告诉我们查找的方法。

千问 · 2009-10-9 08:28:00

--block and waiter
set pagesize 40 linesize 150
column blockers format a45
column waiters format a45
select '阻塞者('||sb.sid||':'||sb.serial#||'-'||sb.username||')-'||qb.sql_text blockers,
'等待者('||sw.sid||':'||sw.serial#||'-'||sw.username||')-'||qw.sql_text waiters
from v$lock lb,v$lock lw,v$session sb,v$session sw,v$sql qb,v$sql qw
where lb.sid=sb.sid
and lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and lb.id1=lw.id1
and sb.lockwait is null
and sw.lockwait is not null
and lb.block>0;
类似SQL如果能及时查询的话，也许可以捕捉到。
复杂业务系统执行的语句可以说瞬息万变，想要真正找到很难。
可以借助其他手段，如LOGMINER查找历史，TRIGGER时时抓取等查找问题的根源。

千问 · 2009-10-9 08:28:00

mark,学习。

千问 · 2009-10-9 08:28:00

多谢各位提点。