联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
Library cache lock介绍
Oracle利用Library cache lock和Library cache pin来实现并发控制，Library cache lock是在handle上获取的，而Library cache pin则是在data heap上获取。访问对象时，首先必须获取handle上的lock，然后将访问的数据pin在内存中。lock的作用是控制进程间的并发访问，而pin的作用是保证数据一致性，防止数据在访问时被交换出去。
lock和pin的实现类似于enqueue，在每个handle上都有lock和pin的holder list和waiter list，用来保存持有该资源和等待该资源的队列。
Library cache lock相关sql语句
--找出library cache lock等待sid,saddr信息
select sid,saddr from v$session where event= 'library cache lock';
SID SADDR
---------- --------
16 572ed244
--找出blocked信息
select kgllkhdl Handle,kgllkreq Request, kglnaobj Object
from x$kgllk where kgllkses = '572ed244'
and kgllkreq > 0;
HANDLE REQUEST OBJECT
-------- ---------- ------------------
62d064dc 2 EMPLOYEES
--找出blocking信息
select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
from x$kgllk lock_a
where kgllkmod > 0
and exists (select lock_b.kgllkhdl from x$kgllk lock_b
where kgllkses = '572ed244' /* blocked session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0);
SADDR HANDLE MOD OBJECT
-------- -------- ---------- ------------
572eac94 62d064dc 3 EMPLOYEES
--blocking 会话信息
select sid,username,terminal,program from v$session where saddr = '572eac94'
SID USERNAME TERMINAL PROGRAM
---------- ----------- --------- --------------------------------------------
12 SCOTT pts/20 sqlplus@goblin.forgotten.realms (TNS V1-V3)
--所有blocked 会话
select sid,username,terminal,program from v$session
where saddr in
(select kgllkses from x$kgllk lock_a
where kgllkreq > 0
and exists (select lock_b.kgllkhdl from x$kgllk lock_b
where kgllkses = '572eac94' /* blocking session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq = 0)
);
SID USERNAME TERMINAL PROGRAM
---------- --------- --------- -------------------------------------------
13 SCOTT pts/22 sqlplus@goblin.forgotten.realms (TNS V1-V3)
16 SCOTT pts/7 sqlplus@goblin.forgotten.realms (TNS V1-V3)
加我QQ
Systemstate 分析library cache lock持有者和阻塞者