oracle 对于锁等待递归次数比较多不太好排查时候使用

SQL> select a.inst_id,
a.process,
a.sid,
a.serial#,
a.sql_id,
a.event,
a.status,
a.program,
a.machine,
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);
<!--省略部分列-->
INST_ID PROCESS SID SERIAL# EVENT STATUS ISLEAF TREE TREE_LEVEL
------- ------- ---- ------- ----------------------------- ------- ------ --------------- ---------
1 7663 17 6749 enq: TX - row lock contention ACTIVE 0 <- 17@1 1
1 6198 25 9989 SQL*Net message from client INACTIVE 1 <- 17@1 <- 25@1 2
1 6310 28 23199 enq: TX - row lock contention ACTIVE 0 <- 28@1 1
1 6198 25 9989 SQL*Net message from client INACTIVE 1 <- 28@1 <- 25@1 2
下面对代码段中的部分参数进行说明。
·INST_ID：会话所在的节点号。
·PROCESS：客户端进程号，与v$process中的spid不是同一个。
·SID、SERIAL#、SQL_ID、STATUS、PROGRAM、MACHINE：会话信息。
·ISLEAF：是否为源头，0代表否，1代表是。
·TREE：树形结构，锁的层次，例如，<- 152@2 <- 153@2 <- 161@1，从左到右依次表示为节点2的会话152被节点2的
会话153堵塞，而节点2的会话153又被节点1的会话161堵塞。所以节点1的会话161是锁的源头。
·TREE_LEVEL：树形层次。
锁源头的查杀方法有两种，说明如下。
1）通过ISLEAF进行筛选，直接查杀锁源头，语句如下：
SQL> select 'alter system kill session ''' || sid || '' || ',' || serial# || ',@' ||
inst_id || ''' immediate;' db_kill_session
from (select a.inst_id,
a.process,
a.sid,
a.serial#,
a.sql_id,
a.event,
a.status,
a.program,
a.machine,
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;
KILL_SESSION
---------------------------------------------------
alter system kill session '161,5579,@1' immediate;
alter system kill session '161,5579,@1' immediate;
SQL> select inst_id, 'kill -9 ' || spid os_kill_session
from (select p.inst_id,
p.spid,
a.sid,
a.serial#,
a.sql_id,
a.event,
a.status,
a.program,
a.machine,
connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
level as tree_level
from gv$session a, gv$process p
where a.inst_id = p.inst_id
and a.paddr = p.addr
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;
INST_ID OS_KILL_SESSION
---------- --------------------------------
1 kill -9 30049

 

2）借助v$session中的final_blocking_instance和final_blocking_session定位锁源头，语句如下：
SQL> select 'alter system kill session ''' || ss.sid || '' || ',' || ss.serial# || ',@' ||
ss.inst_id || ''' immediate;' db_kill_session
from gv$session s, gv$session ss
where s.final_blocking_session is not null
and s.final_blocking_instance = ss.inst_id
and s.final_blocking_session = ss.sid
and s.FINAL_BLOCKING_SESSION_STATUS = 'VALID'
and s.sid <> ss.sid
DB_KILL_SESSION
--------------------------------------------------
alter system kill session '161,5579,@1' immediate;
alter system kill session '161,5579,@1' immediate;
SQL> select p.inst_id, 'kill -9 ' || p.spid os_kill_session
from gv$session s, gv$session ss, gv$process p
where s.final_blocking_session is not null
and s.final_blocking_instance = ss.inst_id
and s.final_blocking_session = ss.sid
and ss.paddr = p.addr
and ss.inst_id = p.inst_id
and s.sid <> ss.sid
INST_ID OS_KILL_SESSION
---------- --------------------------------
1 kill -9 30049
执行拼接生成的语句，即可杀掉锁的源头。
想必大家都遇到过在数据库层面发起“ alter system kill session”（数据库层杀掉会话，不加immediate关键字）时，经常
会出现资源无法及时释放、会话一直处于killed状态的情况。如果这个会话是锁的源头，那么除了等待PMON（进程监视
器）来清理之外，再没有更好的办法了，而在操作系统层面杀掉进程的方式，基本上是百试百灵。使用系统命令“ kill -9”
杀死进程，系统向该process进程发出sigkill，sigkill信号直接发送给init进程，终止process进程。这种方式直接终止了
Oracle会话中对应的操作进程，资源也可以直接释放。