oracle的pin锁,Oracle 之pin和lock

概念扫盲

首先理解一下有关shared pool中的这两个概念：

1、Hash bucket

当一条SQL语句进入library cache的时候，先将SQL文本转化为对应ASCII数值，然后对该这些ASCII数值进行hash函数的运算，传入函数的是SQL语句的名称(name，对于SQL语句来说其name就是SQL语句的文本)以及命名空间(namespace，对于SQL语句来说是“SQL AREA”，表示共享游标。可以从视图v$librarycache里找到所有的namespace)。运用hash函数后得到一个值，该值就是hash bucket的号码，从而该SQL语句被分配到该号的hash bucket里去。实际上，hash bucket就是通过串连起来的对象句柄才体现出来的，它本身是一个逻辑上的概念，是一个逻辑组，而不像对象是一个具体的实体。SQL语句相关的对象有很多(最直观的就是SQL语句的文本)，这些对象都存放在library cache里，它们都通过句柄来访问。可以把library cache理解为一本书，而SQL语句的对象就是书中的页，而句柄就是目录，通过目录可以快速定位到指定内容的页。

2、library cache object handle

如上面所说，library cache使用多个hash bucket来管理，每个hash bucket后面都串连着多个句柄(library cache object handle)，这些对象句柄存放了对象的名称(name)、对象所属的命名空间(namespace)、有关对象的一些标记(比如对象是否为只读、为本地对象还是远程对象、是否被pin在内存中等等)以及有关对象的一些统计信息等。而且，对象句柄中还存放了当前正在lock住和pin住该对象的用户列表、以及当前正在等待lock和pin该对象的用户列表。

Library Cache Lock/Pin

Library cache latch

Library cache latch用来控制对library cache object的并发访问。

由于在对object操作过程中，在handle持有的 lock很容易被打破(修改或删除等dml操作)， Oracle为了保护这个lock，引入了library cache latch机制，也就是说在获得library cachelock之前，需要先获得library cache latch，当获得library cache lock之后就释放librarycache latch。如果某个librarycache object没有在内存中，那么这个lock就不能被获取，这个时候需要获得一个library cache load lock latch，然后再获取一个librarycache load lock,当load lock获得之后就释放library cache load lock latch。

Pin和Lock

Library cache Handle里保存了lock和pin的信息。而且在Library cache handle和child cursor上都有lock和pin。它们称为library cache lock和library cache pin。

Library Cache lock模式

(1)Share(S)：当读取一个library cache object的时候获得

(2)Exclusive(X)：当创建/修改一个library cache object的时候获得

(3)Null(N)：用来确保对象依赖性

NULL锁主要的目的就是标记某个对象是否有效。比如一个SQL语句在解析的时候获得了NULL锁，如果这个SQL的对象一直在共享池中，那么这个NULL锁就会一直存在下去，当这个SQL语句所引用的表被修改之后，这个NULL锁就被打破了，因为修改这个SQL语句的时候会获得Exclusive锁，由于NULL锁被打破了，下次执行这个SQL的时候就需要从新编译。

Library Cache pin模式

0 no lock/pin held(N)

2 share mode(S)

3 exclusive mode(X)

Library cachelock/pin是用来控制对librarycache object的并发访问的。Lock管理并发，pin管理一致性，lock是针对于librarycache handle,而pin是针对于heap。

比如用户A以N模式持有某个handle lock，并且把该handle中的object以S模式pin到了内存中，此刻用户B发出一个请求，想要编辑该object，首先B会在handle上获得X模式的lock，然后再获取该object上X模式的pin，此时如果用户A还持有S的pin，用户B将等待A释放该pin后再去以X模式的pin去编译该object。

示例

借用eygle的方法借用sleep函数测试一下Library cache pin等待发生过程(前人栽树，后人乘凉，嘻嘻)

SQL> create or replace procedure pining

2  is

3  begin

4  null;

5  end;

6  /

Procedure created.

SQL> create or replace procedure calling

2  is

3  begin

4  pining;

5  dbms_lock.sleep(5000);

6  end;

7  /

calling过程调用pining过程时，这样在Libiary中解析pining过程时lock=N，pin=S

sessionA：

SQL> exec calling;

sessionB：

SQL> grant execute on pining to scott;

Grant succeeded.

/*奇怪的是grant并没有触发pin等待，可能是oracle10g的增强，在Oracle10gR2中，library cache pin被library cache mutex所取代。具体原因有待验证，总之，不影响测试*/

SQL> revoke execute on pinling from scott;

回收权限会使回话挂起

再新开一个会话研究一下：

查看一下library cache pin等待信息

SQL> col state for a12

SQL> col event for a20

SQL> select sid,seq#,event,p1,p1raw,state from v$session_wait where event like 'library%';

SID       SEQ# EVENT                      P1 P1RAW    STATE

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

130         24 library cache pin   645719616 267CE640 WAITING

输出结果中P1 为Library Cache Handle Address，pnraw记录的是十六进制的表示，通过这个地址结合X$KGLOB视图，查看一下对象信息

SQL> col KGLNAOWN for a12

SQL> col KGLNAOBJ for a15

SQL> select addr,kglhdadr,kglnaown,kglnaobj,kglnahsh from x$kglob where kglhdadr like '267CE640';

ADDR     KGLHDADR KGLNAOWN     KGLNAOBJ          KGLNAHSH

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

B7ECBC18 267CE640 SYS          PINING          2030642293

kglnahsh代表的就是hash_value

x$kglpn视图中包含着Libiary Cache pin等待的记录，可以通过该视图与v$session结合找出哪些会话持有该handle，下面的显示sid为133

SQL> select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ

2  from v$session a,x$kglpn b

3  where a.saddr=b.kglpnuse and b.kglpnhdl = '267CE640' and b.KGLPNMOD<>0

4  /

SID USERNAME   PROGRAM       ADDR     KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL

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

KGLPNLCK   KGLPNMOD   KGLPNREQ

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

133 SYS        sqlplus@node1 B7ECAFC0 27BF3F6C 29EECDC0 29EECDC0 267CE640

(TNS V1-V3)

27BB3D1C          2          0

接下来的查询可以看出该会话等待PL/SQL lock timer

SQL> col p1text for a10

SQL> select SID,SEQ#,EVENT,P1TEXT  from v$session_wait where sid=133;

SID       SEQ# EVENT              P1TEXT

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

133         21 PL/SQL lock timer  duratio

通过v$sqltext，或者v$sqlarea就可以查出具体的sql

SQL> select a.sid,a.serial#,c.spid,b.address,b.hash_value,b.sql_text

2  from v$session a,v$sqltext b,v$process c

3  where a.sid='133' and a.sql_hash_value=b.hash_value and a.paddr=c.addr

4  /

SID    SERIAL# SPID         ADDRESS  HASH_VALUE

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

SQL_TEXT

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

133          7 4544         267CEA34 3918317640

BEGIN calling; END;

[oracle@node1 ~]$ ps -ef | grep 4544 | grep -v 'grep'

oracle    4544  4543  0 16:16 ?        00:00:00 oracleorcl_dup (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

[oracle@node1 ~]$ kill -9 4544

/*由于用到的是sleep函数，此时该会话被休眠，如果数据库中kill session会话也将处于挂起状态*/

此时会话被终止，revoke会话将结束等待

SQL> exec calling;

BEGIN calling; END;

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

另一个会话：

SQL> revoke execute on pining from scott;

Revoke succeeded.

此处分享一个SQL，网上找到的，用来查找当前的堵塞进程，谢谢无私奉献的不知名网友~

select Distinct /*+ ordered */ w1.sid waiting_session,

h1.sid holding_session,

w.kgllktype lock_or_pin,

od.to_owner object_owner,

od.to_name object_name,

oc.Type,

decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_held,

decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_requested,

xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql

from dba_kgllock w, dba_kgllock h, v$session w1,

v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllk xw,x$kgllk xh

where

(((h.kgllkmod != 0) and (h.kgllkmod != 1)

and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

and

(((w.kgllkmod = 0) or (w.kgllkmod= 1))

and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and w.kgllktype = h.kgllktype

and w.kgllkhdl = h.kgllkhdl

and w.kgllkuse = w1.saddr

and h.kgllkuse = h1.saddr

and od.to_address = w.kgllkhdl

and od.to_name=oc.Name

and od.to_owner=oc.owner

and w1.sid=xw.KGLLKSNM

and h1.sid=xh.KGLLKSNM

and (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)

and (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH) order by h1.sid;

查询结果如下：

如何避免pin和lock事件

1、当对用户的权限进行管理即进行”grant” “revoke”时,可能产生”library cache pin”，可以通过角色来对最终用户进行授权或收回授权,而不要用显性的方式即直接对最终用户授权或收回授权,从而避免产生”library cache pin”。

2、高峰时的object管理在系统运行高峰时对数据库object的管理可能产生”library cache pin”，尽量把对数据库object的管理安排到负载相对较小的时侯。

3、如果Package或过程中存在复杂的、交互的依赖以来关系极易导致library cache pin的出现，在PL/SQL包中存在大量的互跨的依赖性(dependency)，尽可能按等级来排列它们的结构。

4、合理控制shared pool大小。