Library Cache Lock/Pin研究

最新推荐文章于 2022-05-17 11:26:21 发布

congyun1447

最新推荐文章于 2022-05-17 11:26:21 发布

阅读量105

点赞数 1

Oracle使用lock和pin来进行shared pool的并发访问。Lock比pin具有更高的级别。
Lock在handle上获得，在pin一个对象之前，必须首先获得该handle的锁定。
Lock有三种模式：Null、Share、Exclusive。
在读取对象时，通常需要获取Null以及Share模式的锁定。在修改对象时需要获取Exclusive锁定。
一个进程在访问之前必须pin住对象，pin之前必须先获取到Library Lach Lock。同样pin有三种模式：Null、Share、Exclusive。只读模式时获取share pin，修改模式时获取exclusive pin。
访问、执行过程和package时，获取的都是share lock和share pin。修改、编译时需要获取Excusive lock 和 Exclusive pin。
固定表x$kglob (Kernle Generic Library Cache Manager Object)
X$kglpn(Kernle Generic Library Cache Manager Object Pin)
X$KGLLK(Kernle Generic Library Cache Manager Object Lock)（该视图数据更清楚,能够同时看到lock和pin的信息）

x$kglob.kglhdpar = x$kglpn.kglpnhdl
X$KGLLK.kglhdpar = x$kglob.kglhdpar
X$KGLLK .Kgllkpnc = x$kglpn.kglpnadr
X$KGLLK.kgllkuse、X$KGLLK.kgllkses、X$KGLPN.kglpnuse、X$KGLPN.kglpnuse都为session.saddr

SQL> col SID format a3
SQL> col event format a20
SQL> col P1TEXT format a10
SQL> col P2TEXT format a10
SQL> col P3TEXT format a10
SQL> select sid,
2         seq#,
3         event,
4         p1text,
5         p1,
6         p1raw,
7         p2text,
8         p2,
9         p2raw
10    from v$session_wait
11   where event = 'library cache pin';

SID       SEQ# EVENT                P1TEXT             P1 P1RAW    P2TEXT             P2 P2RAW
--- ---------- -------------------- ---------- ---------- -------- ---------- ---------- --------
32        870 library cache pin    handle add 622063236 2513EE84 pin addres 700781064 29C51208
                                    ress                           s

SQL> col sid format a3
SQL> col INST_ID format 8
SQL> col KGLNAOWN format a6
SQL> col KGLNAOBJ format a10
SQL> select inst_id, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
2    from x$kglob
3   where kglhdadr = '2513EE84';

   INST_ID KGLHDADR KGLHDPAR KGLNAO KGLNAOBJ     KGLNAHSH KGLHDOBJ
---------- -------- -------- ------ ---------- ---------- --------
         1 2513EE84 2513EE84 RMGZ   PINING     2175063080 2513EC6C


SQL> select inst_id, kglpnadr, kglpnuse, kglpnuse, kglpnhdl, kglpnlck, kglpnmod, kglpnreq
2    from x$kglpn
3   where kglpnhdl = '2513EE84'
4 ;

   INST_ID KGLPNADR KGLPNUSE KGLPNUSE KGLPNHDL KGLPNLCK   KGLPNMOD   KGLPNREQ
---------- -------- -------- -------- -------- -------- ---------- ----------
         1 29C51208 2B664C5C 2B664C5C 2513EE84 00                0          3
         1 29C50BD8 2B660A4C 2B660A4C 2513EE84 29C4FEA0          2          0
         1 29C50A70 2B65A27C 2B65A27C 2513EE84 29C4FA20          2          0

Kglpnlck<>’00’获取到Library Latch Lock的纪录
KGLPNMOD为pin的锁定模式
Kglpnreq为等待3模式的锁定（3为 Exclusive Pin）

SQL> select kgllkadr,
2         kgllkuse,
3         kgllkses,
4         kgllkhdl,
5         kgllkmod,
6         kgllkreq,
7         kgllkpnc,
8         kgllkhdl,
9         kglhdpar,
10         user_name,
11         kglnaobj
12    from X$KGLLK
13   where kglhdpar = '2513EE84'
14 ;

KGLLKADR KGLLKUSE KGLLKSES KGLLKHDL   KGLLKMOD   KGLLKREQ KGLLKPNC KGLLKHDL KGLHDPAR USER_NAME KGLNAOBJ
-------- -------- -------- -------- ---------- ---------- -------- -------- -------- --------- ----------
29C4FA20 2B65A27C 2B65A27C 2513EE84          1          0 29C50A70 2513EE84 2513EE84 SYS       PINING
29C4FEA0 2B660A4C 2B660A4C 2513EE84          1          0 29C50BD8 2513EE84 2513EE84 SYS       PINING
29C500E0 2B664C5C 2B664C5C 2513EE84          3          0 00       2513EE84 2513EE84 SYS       PINING

从数据可知：kgllkpnc＝’00’说明没有取得pin上的锁定,正在等待
Kgllkpnc = x$kglpn.kglpnadr查询在pin上的锁定信息。
Kgllkmod代表library latch lock上的锁定模式：1：null，2:share，3:Exclusive

在上面的基础上再增加一个library latch lock 的等待

SQL> select sid,seq#,event,p1text,p1,p1raw,p2text,p2,p2raw,p3text,p3,p3raw from v$session_wait where event like'library cache %';

SID       SEQ# EVENT                P1TEXT             P1 P1RAW    P2TEXT             P2 P2RAW    P3TEXT             P3 P3RAW
--- ---------- -------------------- ---------- ---------- -------- ---------- ---------- -------- ---------- ---------- --------
32        961 library cache pin    handle add 622063236 2513EE84 pin addres 700781064 29C51208 100*mode+n        301 0000012D
                                    ress                           s                              amespace

23        249 library cache lock   handle add 622063236 2513EE84 lock addre 700776960 29C50200 100*mode+n        301 0000012D
                                    ress                           ss                             amespace

SQL>
SQL> select kgllkadr,
2         kgllkuse,
3         kgllkses,
4         kgllkhdl,
5         kgllkmod,
6         kgllkreq,
7         kgllkpnc,
8         kgllkhdl,
9         kglhdpar,
10         user_name,
11         kglnaobj
12    from X$KGLLK
13   where kglhdpar = '2513EE84'
14 ;

KGLLKADR KGLLKUSE KGLLKSES KGLLKHDL   KGLLKMOD   KGLLKREQ KGLLKPNC KGLLKHDL KGLHDPAR USER_NAME                      KGLNAOBJ
-------- -------- -------- -------- ---------- ---------- -------- -------- -------- ------------------------------ ----------
29C50200 2B65F76C 2B65F76C 2513EE84          0          3 00       2513EE84 2513EE84 SYS                            PINING
29C4FA20 2B65A27C 2B65A27C 2513EE84          1          0 29C50A70 2513EE84 2513EE84 SYS                            PINING
29C4FEA0 2B660A4C 2B660A4C 2513EE84          1          0 29C50BD8 2513EE84 2513EE84 SYS                            PINING
29C500E0 2B664C5C 2B664C5C 2513EE84          3          0 00       2513EE84 2513EE84 SYS                            PINING