--创建存储监控锁等待数据的表
create table lixia_lock as
select t1.sid block_sid,t1.SERIAL# block_SERIAL#,t1.sql_fulltext block_sql,t1.object_id block_object_id,t1.xid,t1.lock_TYPE block_lock_TYPE,t1.lock_LMODE block_lock_LMODE, --造成阻塞的SQL
t2.sid,t2.SERIAL#,t2.sql_fulltext sql,t2.object_id,t1.lock_TYPE,t1.lock_LMODE,sysdate time --被阻塞的SQL
from (select b.sid, b.SERIAL#, a.sql_fulltext, lo.object_id, t.xid,d.TYPE lock_TYPE,d.LMODE lock_LMODE
from v$sql a,
v$session b,
v$transaction c,
v$lock d,
v$locked_object lo,
V$TRANSACTION t
where a.sql_id = b.prev_sql_id
and b.taddr = c.addr
and c.addr = d.addr
and d.lmode = 6
and lo.SESSION_ID = b.sid
and b.taddr = t.addr
and 1=0) t1,
(select b.sid,
b.SERIAL#,
b.BLOCKING_SESSION,
a.sql_fulltext,
lo.object_id,c.TYPE lock_TYPE,c.LMODE lock_LMODE
from v$sql a, v$session b, v$lock c, v$locked_object lo
where a.sql_id = b.sql_id
and b.LOCKWAIT = c.kaddr
and c.REQUEST = 6
and lo.SESSION_ID = b.sid
and 1=0) t2
where t1.sid = t2.BLOCKING_SESSION;
--1秒中监控一次锁等待
declare
i number;
begin
for i in 1 .. 1000 loop
insert into lixia_lock
select t1.sid,t1.SERIAL#,t1.sql_fulltext,t1.object_id,t1.xid,t1.lock_TYPE,t1.lock_LMODE, --造成阻塞的SQL
t2.sid,t2.SERIAL#,t2.sql_fulltext sql_fulltext_lock,t2.object_id,t1.lock_TYPE,t1.lock_LMODE,sysdate time --被阻塞的SQL
from (select b.sid, b.SERIAL#, a.sql_fulltext, lo.object_id, t.xid,d.TYPE lock_TYPE,d.LMODE lock_LMODE
from v$sql a,
v$session b,
v$transaction c,
v$lock d,
v$locked_object lo,
V$TRANSACTION t
where a.sql_id = b.prev_sql_id
and b.taddr = c.addr
and c.addr = d.addr
and d.lmode = 6
and lo.SESSION_ID = b.sid
and b.taddr = t.addr) t1,
(select b.sid,
b.SERIAL#,
b.BLOCKING_SESSION,
a.sql_fulltext,
lo.object_id,c.TYPE lock_TYPE,c.LMODE lock_LMODE
from v$sql a, v$session b, v$lock c, v$locked_object lo
where a.sql_id = b.sql_id
and b.LOCKWAIT = c.kaddr
and c.REQUEST = 6
and lo.SESSION_ID = b.sid) t2
where t1.sid = t2.BLOCKING_SESSION;
DBMS_LOCK.sleep(1);
commit;
end loop;
end;
/
--举例：
会话一执行update 后不提交事务然后再执行其他的SQL
会话二和会话三更新会话一锁定的行被阻塞，通过该SQL监控脚本定位出造成阻塞的SQL
--会话一的SID
SQL> select sid from v$mystat where rownum=1;
SID
----------
144
--会话二的SID
SQL> select sid from v$mystat where rownum=1;
SID
----------
145
--会话三的SID
SQL> select sid from v$mystat where rownum=1;
SID
----------
22
--在监控的会话中执行脚本
declare
i number;
begin
for i in 1 .. 1000 loop
insert into lixia_lock
select b.sid, b.SERIAL#, a.sql_fulltext, lo.object_id, c.xid,d.TYPE lock_TYPE,d.LMODE lock_LMODE,
'','','','','','',sysdate time
from v$sql a,
v$session b,
v$transaction c,
v$lock d,
v$locked_object lo
where a.sql_id =b.prev_sql_id
and b.taddr = c.addr
and c.addr = d.addr
and d.lmode = 6
and lo.SESSION_ID = b.sid;
insert into lixia_lock
select t1.sid,t1.SERIAL#,t1.sql_fulltext,t1.object_id,t1.xid,t1.lock_TYPE,t1.lock_LMODE, --造成阻塞的SQL
t2.sid,t2.SERIAL#,t2.sql_fulltext sql_fulltext_lock,t2.object_id,t1.lock_TYPE,t1.lock_LMODE,sysdate time --被阻塞的SQL
from (select b.sid, b.SERIAL#, a.sql_fulltext, lo.object_id, c.xid,d.TYPE lock_TYPE,d.LMODE lock_LMODE
from v$sql a,
v$session b,
v$transaction c,
v$lock d,
v$locked_object lo
where a.sql_id = b.prev_sql_id
and b.taddr = c.addr
and c.addr = d.addr
and d.lmode = 6
and lo.SESSION_ID = b.sid) t1,
(select b.sid,
b.SERIAL#,
b.BLOCKING_SESSION,
a.sql_fulltext,
lo.object_id,c.TYPE lock_TYPE,c.LMODE lock_LMODE
from v$sql a, v$session b, v$lock c, v$locked_object lo
where a.sql_id = b.sql_id
and b.LOCKWAIT = c.kaddr
and c.REQUEST = 6
and lo.SESSION_ID = b.sid) t2
where t1.sid = t2.BLOCKING_SESSION;
DBMS_LOCK.sleep(1);
commit;
end loop;
end;
/
--会话一执行UPDATE 不提交会话
update lixia.t1 set object_name='test1' where object_id=20;
--会话一执行 select
select 1 from dual ;
--会话二执行 UPDATE被阻塞
update lixia.t1 set object_name='test2' where object_id=20;
--会话三执行UPDATE 被阻塞
update lixia.t1 set object_name='test3' where object_id=20;
--查看监控的到信息
select BLOCK_SID ,BLOCK_SERIAL#, to_char(block_sql) block_sql,BLOCK_OBJECT_ID,
XID,SID,SERIAL#,OBJECT_ID,to_char(sql) sql ,time
from sys.lixia_lock
order by time;
