oracle查看阻塞的sql,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, '

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；

效果如下：