Oracle RAC阻塞排查SQL脚本
该使用到的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
l
o
c
k
l
1
,
g
v
lock l1, gv
lockl1,gvsession s1,
gv
l
o
c
k
l
2
,
g
v
lock l2, gv
lockl2,gvsession 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
s
q
l
a
,
v
sql a, v
sqla,vsession b, v
l
o
c
k
c
w
h
e
r
e
c
.
t
y
p
e
=
′
T
X
′
a
n
d
a
.
s
q
l
i
d
=
b
.
s
q
l
i
d
a
n
d
b
.
s
i
d
=
c
.
s
i
d
u
n
i
o
n
a
l
l
s
e
l
e
c
t
b
.
s
i
d
,
a
.
s
q
l
i
d
,
a
.
s
q
l
t
e
x
t
,
a
.
h
a
s
h
v
a
l
u
e
,
b
.
u
s
e
r
n
a
m
e
,
b
.
m
a
c
h
i
n
e
,
a
.
m
o
d
u
l
e
,
d
e
c
o
d
e
(
c
.
b
l
o
c
k
,
1
,
′
b
l
o
c
k
i
n
g
′
)
b
l
o
c
k
i
n
g
,
d
e
c
o
d
e
(
c
.
r
e
q
u
e
s
t
,
0
,
′
n
u
l
l
′
,
′
b
l
o
c
k
e
d
′
)
b
l
o
c
k
e
d
,
t
o
c
h
a
r
(
b
.
l
o
g
o
n
t
i
m
e
,
′
y
y
y
y
−
m
m
−
d
d
h
h
24
:
m
i
:
s
s
′
)
f
r
o
m
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
lockcwherec.type=′TX′anda.sqlid=b.sqlidandb.sid=c.sidunionallselectb.sid,a.sqlid,a.sqltext,a.hashvalue,b.username,b.machine,a.module,decode(c.block,1,′blocking′)blocking,decode(c.request,0,′null′,′blocked′)blocked,tochar(b.logontime,′yyyy−mm−ddhh24:mi:ss′)fromvsql a, v
s
e
s
s
i
o
n
b
,
v
session b, v
sessionb,vlock c
where c.type = ‘TX’
and a.sql_id = b.prev_sql_id
and b.sid = c.sid
and c.block = 1；
效果如下：
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
