【性能调整】等待事件(八) library cache locks and pins

最新推荐文章于 2021-04-14 13:02:27 发布

cixlv48826

最新推荐文章于 2021-04-14 13:02:27 发布

阅读量144

点赞数

文章标签：数据库

Library cache locks and library cache pin waits:

Library cache lock和pin等待能够hang住实例，并且在少数情况下整个rac集群的实例都能被其hang住。

为什么需要library cache locks?

Library cache locks(库缓存锁)又被称作parse locks(解析锁)，用来维护对象以及对象的依赖对象(如sql)之间的依赖机制。例如，如果一个对象定义需要修改或者解析锁将被终止，那么依赖对象必须失效。这种依赖由library cache locks维护。举例：如果从一个表中删除一个字段，那么所有依赖于这个表的sql都要失效，并且在下次访问这个对象的时候需要重新解析,Library cache locks就是为实现这种追踪机制而设计的。

为什么需要library cache pins?

Library cache pins处理当前依赖对象的执行。例如：当一个会话正在执行或者访问一个依赖对象(sql)的时候，其基础对象不能被修改。所以，在解析锁释放一个library cache object(库缓存对象)之前，必须获得独占模式的library cache pins（通俗的说先library cache lock到object，如果需要修改数据字典，那么就要pin住）。如果一个session正在执行一个sql，那么library cache pins将不可用，将会在library cache pins等待。一般来说，这种情况发生在长时间运行的sql语句。

比较绕，通俗的讲：

哪种情况下使用library cache lock?

这个事件控制客户之间library cache的一致性。在一个object句柄请求一个lock，是为了不让其他的客户访问相同的对象。这个客户可以在相对长的一段时间内维护依赖（例如，其他的客户不能修改object），这个锁同时也获得了object的位置。

数据库对象相关的解析或者编译sql或者pl/sql语句(table,view,procedure,function,package,package body,trigger,index,cluster,synonym)时使用，当解析或者编译完成时释放，cursor(sql and plsql区域)，pipes和其他transient对象不使用这种lock。

这种lock是死锁敏感的，并且操作是同步的。

哪些地方使用library cache pin?

这个事件管理library cache 的一致性，pin住一个object将导致heaps被load进内存，如果一个客户想要修改或者检查某个object，这个客户必须在lock之后请求一个pin。

Pin不是死锁敏感的，因为是抢占式的获得的，同样操作也是同步的。

为什么需要两种类型的锁？

Lock和pin都是为了保护library cache的对象访问的，locks管理进程间的一致性，而pin管理cache的一致性。为了访问一个对象，一个进程必须先lock对象句柄，然后再pin住对象heap。

请求lock和pin都会产生等待，这是一个肯能的竞争源头，但是并没有NOWAIT的请求模式。

如何降低library cache lock和pin?

降低library cache lock：

通过增大shared pool size，或者设置cursor_sharing为similar，或者将一些特殊操作，例如批量收集统计信息的job与其他的维护job从oltp中分离，尽量在系统不繁忙的时候进行。

降低library cache pin：

减少对象定义的修改，比如alter truncate drop gather statistics等操作尽量在系统不繁忙时进行。

X$kgllk,x$kglpn,x$kglob

X$kgllk是所有object上的锁结构的表，x$kglob则是资源结构表,x$kglpn是所有的library cahe pin的表。

X$kglob.kglhdadr为资源结构(resource structure)的指针，可以推测，kglhdadr就是KGL handle address，即kgl的句柄地址。X$kgllk作为一个锁的结构，x$kgllk.kgllkhdl这个字段可以认为是kgl lock handle，指向x$kglob.kglhdadr来lock object。同样的，x$kglpn作为一个pin的结构，x$kglpn.kglpnhdl这个字段可以认为是kgl pin handle，指向x$kglob.kglhdadr来pin一个resource。给出一个类推：x$kglob作为一个资源结构，而x$kgllk作为library cache locks的锁结构。对于library cache pin，x$kglpn作为一个pin结构，x$kglpn使用kglpnhdl来pin住资源。结合下面一个例子看看就很清楚了。

创建一个简单的例子来create library cache locks和pin waits

Create or replace procedure test_kgllk(l_sleep in Boolean,

l_conpile in boolean) as

Begin

If (l_sleep) then

sys.dbms_lock.sleep(60);

elsif (l_compile) then

execute immediate 'alter procedure test_kgllk compile';

end if;

End;

上面的例子中，创建了一个过程，这个过程接受两个boolean参数:sleep和compile。

第一个参数为true则允许过程sleep一分钟，第二个参数为true则允许过程重新编译自己。

然后在两个不同的session中分别执行：

Session 1:exec test_kgllk(true,false);

Session 2:exec test_kgllk(false,true);

这时候两个session将都会等待。

下面来做一些测试：

在session 145:

SQL> select sid from v$mystat where rownum = 1;

SID

----------

145

SQL> exec test_kgllk(true,false);

在session 149:

SQL> select sid from v$mystat where rownum = 1;

SID

----------

149

SQL> exec test_kgllk(false,true);

在session 138:

SQL> select sid from v$mystat where rownum = 1;

SID

----------

138

SQL>

SQL> select distinct ses.ksusenum sid,

2 ses.ksuseser serial#,

3 --ses.ksuudlna username,

4 --ses.ksuseunm machine,

5 --ob.kglnaown obj_owner,

6 ob.kglnaobj obj_name,

7 pn.kglpncnt pin_cnt,

8 pn.kglpnmod pin_mode,

9 pn.kglpnreq pin_req,

10 w.state,

11 w.event,

12 w.wait_Time,

13 w.seconds_in_Wait

14 -- lk.kglnaobj, lk.user_name, lk.kgllksnm,

15 --,lk.kgllkhdl,lk.kglhdpar

16 --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,

17 --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl

18 from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w

19 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)

20 and ob.kglhdadr = pn.kglpnhdl

21 and pn.kglpnuse = ses.addr

22 and w.sid = ses.indx

23 order by seconds_in_wait desc;

SID SERIAL# OBJ_NAME PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT

145 19 TEST_KGLLK 3 2 0 WAITING PL/SQL lock timer 0 18

149 24 TEST_KGLLK 0 0 3 WAITING library cache pin 0 15

149 24 TEST_KGLLK 3 2 0 WAITING library cache pin 0 15

SQL>

一分钟后session145运行完成，查看：

SQL> select distinct ses.ksusenum sid,

2 ses.ksuseser serial#,

3 --ses.ksuudlna username,

4 --ses.ksuseunm machine,

5 --ob.kglnaown obj_owner,

6 ob.kglnaobj obj_name,

7 pn.kglpncnt pin_cnt,

8 pn.kglpnmod pin_mode,

9 pn.kglpnreq pin_req,

10 w.state,

11 w.event,

12 w.wait_Time,

13 w.seconds_in_Wait

14 -- lk.kglnaobj, lk.user_name, lk.kgllksnm,

15 --,lk.kgllkhdl,lk.kglhdpar

16 --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,

17 --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl

18 from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w

19 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)

20 and ob.kglhdadr = pn.kglpnhdl

21 and pn.kglpnuse = ses.addr

22 and w.sid = ses.indx

23 order by seconds_in_wait desc;

SID SERIAL# OBJ_NAME PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT

149 24 TEST_KGLLK 0 0 3 WAITING library cache pin 0 72

149 24 TEST_KGLLK 3 2 0 WAITING library cache pin 0 72

再开一个session，在session 137：

SQL> select sid from v$mystat where rownum = 1;

SID

----------

137

SQL> exec test_kgllk (false, true);

可以看到：

SQL> select distinct ses.ksusenum sid,

2 ses.ksuseser serial#,

3 --ses.ksuudlna username,

4 --KSUSEMNM module,

5 --ob.kglnaown obj_owner,

6 ob.kglnaobj obj_name,

7 lk.kgllkcnt lck_cnt,

8 lk.kgllkmod lock_mode,

9 lk.kgllkreq lock_req,

10 w.state,

11 w.event,

12 w.wait_Time,

13 w.seconds_in_Wait

14 from x$kgllk lk, x$kglob ob, x$ksuse ses, v$session_wait w

15 where lk.kgllkhdl in (select kgllkhdl from x$kgllk where kgllkreq > 0)

16 and ob.kglhdadr = lk.kgllkhdl

17 and lk.kgllkuse = ses.addr

18 and w.sid = ses.indx

19 order by seconds_in_wait desc ;

SID SERIAL# OBJ_NAME LCK_CNT LOCK_MODE LOCK_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT

149 24 TEST_KGLLK 1 1 0 WAITING library cache pin 0 115

149 24 TEST_KGLLK 1 3 0 WAITING library cache pin 0 115

145 19 TEST_KGLLK 1 1 0 WAITING SQL*Net message from client 0 58

137 7 TEST_KGLLK 0 0 2 WAITING library cache lock 0 21

137 7 TEST_KGLLK 1 1 0 WAITING library cache lock 0 21

SQL>

而cancel掉137的执行，则有：

SQL> select distinct ses.ksusenum sid,

2 ses.ksuseser serial#,

3 --ses.ksuudlna username,

4 --KSUSEMNM module,

5 --ob.kglnaown obj_owner,

6 ob.kglnaobj obj_name,

7 lk.kgllkcnt lck_cnt,

8 lk.kgllkmod lock_mode,

9 lk.kgllkreq lock_req,

10 w.state,

11 w.event,

12 w.wait_Time,

13 w.seconds_in_Wait

14 from x$kgllk lk, x$kglob ob, x$ksuse ses, v$session_wait w

15 where lk.kgllkhdl in (select kgllkhdl from x$kgllk where kgllkreq > 0)

16 and ob.kglhdadr = lk.kgllkhdl

17 and lk.kgllkuse = ses.addr

18 and w.sid = ses.indx

19 order by seconds_in_wait desc ;

SID SERIAL# OBJ_NAME LCK_CNT LOCK_MODE LOCK_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT

SQL>

现在只剩下149的session还在执行中，查询pin的信息：

SQL> select distinct ses.ksusenum sid,

2 ses.ksuseser serial#,

3 --ses.ksuudlna username,

4 --ses.ksuseunm machine,

5 --ob.kglnaown obj_owner,

6 ob.kglnaobj obj_name,

7 pn.kglpncnt pin_cnt,

8 pn.kglpnmod pin_mode,

9 pn.kglpnreq pin_req,

10 w.state,

11 w.event,

12 w.wait_Time,

13 w.seconds_in_Wait

14 -- lk.kglnaobj, lk.user_name, lk.kgllksnm,

15 --,lk.kgllkhdl,lk.kglhdpar

16 --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,

17 --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl

18 from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w

19 where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)

20 and ob.kglhdadr = pn.kglpnhdl

21 and pn.kglpnuse = ses.addr

22 and w.sid = ses.indx

23 order by seconds_in_wait desc;

SID SERIAL# OBJ_NAME PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT

149 24 TEST_KGLLK 0 0 3 WAITING library cache pin 0 358

149 24 TEST_KGLLK 3 2 0 WAITING library cache pin 0 358

SQL>

上面的一些测试可以这样概述，一个过程正在编译本身，一个过程正在编译一个正在执行的过程

对于一个过程正在编译一个正在执行的过程，测试一下：

Create or replace procedure sys.test_kgllk1(l_sleep in Boolean,

l_compile in boolean) as

Begin

If (l_sleep) then

sys.dbms_lock.sleep(60);

elsif (l_compile) then

execute immediate 'alter procedure test_kgllk compile';

end if;

End;

在session 145：

SQL> exec test_kgllk(true,false);

在session149：

SQL> exec test_kgllk1(false,true);

在session138：

SQL> select a.SID,

2 a.SEQ#,

3 a.EVENT,

4 a.P1TEXT,

5 a.P2TEXT,

6 a.P3TEXT,

7 a.WAIT_CLASS,

8 a.STATE

9 from v$session_wait a

10 where a.sid in (145, 149);

SID SEQ# EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS STATE

---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------

145 200 PL/SQL lock timer duration Idle WAITING

149 172 library cache pin handle address pin address 100*mode+namespace Concurrency WAITING

可以看到，145正在等待pl/sql lock timer，而149在等待library cache pin，其中library cache pin的参数如下：

P1: handle address

P2: pin address

P3: 100*mode+namespace

这样就很好理解，当过程编译本身的时候，出现的:

PIN_CNT PIN_MODE PIN_REQ

---------- ---------- ----------

3 2 0

0 0 3

第一行是在等待lock timer，此时pin count为3，pin mode 为2，显然正在运行的过程已经拥有了pin，而第二行的pin count为0，pin mode为0，pin mode为3，显然是其本身又在请求pin，这个执行过程将挂起，因为执行过程中的过程永远也得不到对其本身的pin。