Oracle锁处理

背景：

随着数据库版本不断迭代更新， v$session

视图的内容越来越丰富，可以直接使用blocking_session、blocking_instance、final_blocking_instance和final_blocking_session字段进行定位。对于锁层次的排查可以重复查询v$session来确定，但如果锁层次有100层，那么通过人工遍历100次的方式，显然不合适。
自Oracle 9i开始，DBA就可以使用SYS_CONNECT_BY_PATH函数将父节点到当前行的内容以路径或层次的形式显示出来。

可以使用如下语句查询锁信息：

select a.inst_id,
       a.PROCESS,
       a.SID,
       a.serial#,
       a.SQL_ID,
       a.EVENT,
       a.status,
       a.program,
       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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

参数说明：
INST_ID：会话所在的节点号
PROCESS：客户端进程号，v$process中的spid不是同一个
ISLEAF：是否为源头，0代表否，1代表是
TREE：树形结构，锁的层次，例如，<-152@2<-153@2<-161@1，从左到右表示节点2的会话152被节点2的会话153堵塞，而节点2的会话153又被节点1的会话161堵塞。所以节点1的会话161是锁的源头。
TREE_LEVEL：树形层次

源头的查杀方式有两种

1.通过ISLEAF进行筛选，直接查杀锁源头

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,
       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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2.借助v$session中的final_blocking_instance和final_blocking_session定位锁源头

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.sid <> ss.sid
1
2
3
4
5
6
7

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
1
2
3
4
5
6
7
8