该使用到的sql脚本是参考别人技术文档转接过来用于日常工作中使用到的记录下。
1，树形结构分级别显示会话之间的阻塞关系
set lines 200 pages 100
col tree for a30
col event for a40
select *
from (select a.inst_id, a.sid, a.serial#,
a.sql_id,
a.event,
a.status,
connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
level as tree_level
from gv$session a
start with a.blocking_session is not null
connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
where isleaf = 1
order by tree_level asc;
效果如下：
2，直接显示会话阻塞关系
SELECT DISTINCT
s1.username
|| '@'
|| s1.machine
|| ' ( INST='
|| s1.inst_id
|| ' SID='
|| s1.sid
|| ' Serail#='
|| s1.serial#
|| ' ) IS BLOCKING '
|| s2.username
|| '@'
|| s2.machine
|| ' ( INST='
|| s2.inst_id
|| ' SID='
|| s2.sid
|| ' Serial#='
|| s2.serial#
|| ' ) '
AS blocking_status
FROM gv$lock l1,
gv$session s1,
gv$lock l2,
gv$session s2
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND s1.inst_id = l1.inst_id
AND s2.inst_id = l2.inst_id
AND l1.block > 0
AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2;
效果如下：
3，显示阻塞实例的sql语句（需要在RAC的每个实例上面去执行）
select b.sid,
a.sql_id,
a.sql_text,
a.hash_value,
b.username,
b.machine,
a.module,
decode(c.block, 1, 'blocking') blocking,
decode(c.request, 0, 'null', 'blocked') blocked,
to_char(b.logon_time, 'yyyy-mm-dd hh24:mi:ss')
from v$sql a, v$session b, v$lock c
where c.type = 'TX'
and a.sql_id = b.sql_id
and b.sid = c.sid
union all
select b.sid,
a.sql_id,
a.sql_text,
a.hash_value,
b.username,
b.machine,
a.module,
decode(c.block, 1, 'blocking') blocking,
decode(c.request, 0, 'null', 'blocked') blocked,
to_char(b.logon_time, 'yyyy-mm-dd hh24:mi:ss')
from v$sql a, v$session b, v$lock c
where c.type = 'TX'
and a.sql_id = b.prev_sql_id
and b.sid = c.sid
and c.block = 1；
效果如下：
