dedecms网站后台,厦门建设局人员名单,文学网站做编辑,建设体育课程基地网站背景#xff1a;
随着数据库版本不断迭代更新#xff0c; v$session
视图的内容越来越丰富#xff0c;可以直接使用blocking_session、blocking_instance、final_blocking_instance和final_blocking_session字段进行定位。对于锁层次的排查可以重复查询v$session来确定
随着数据库版本不断迭代更新 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);参数说明 INST_ID会话所在的节点号 PROCESS客户端进程号v$process中的spid不是同一个 ISLEAF是否为源头0代表否1代表是 TREE树形结构锁的层次例如-1522-1532-1611从左到右表示节点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;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;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.sidselect 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
