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


PinLock

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

Library Cache lock模式

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

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

       3Null(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

比如用户AN模式持有某个handle lock,并且把该handle中的objectS模式pin到了内存中,此刻用户B发出一个请求,想要编辑该object,首先B会在handle上获得X模式的lock,然后再获取该objectX模式的pin,此时如果用户A还持有Spin,用户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=Npin=S

sessionA

SQL> exec calling;

sessionB

SQL> grant execute on pining to scott;

Grant succeeded.

/*奇怪的是grant并没有触发pin等待,可能是oracle10g的增强,Oracle10gR2中,library cache pinlibrary 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,下面的显示sid133

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;

查询结果如下:

 

 

如何避免pinlock事件

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大小。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-1245330/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/29320885/viewspace-1245330/

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值