library cache pin

最新推荐文章于 2021-04-16 05:01:35 发布

Ellison

最新推荐文章于 2021-04-16 05:01:35 发布

阅读量769

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

转自：http://www.itpub.net/thread-1703738-1-1.html

最近运行一个分批删除数据的procedure，感觉运行比较慢，临时取消了该job，然后再次重建procedure时，sessionhang住了，应该是library cache pin的等待事件了，oracle用两种结构pin和lock来控制shared pool的并发访问控制。

访问一个对象将其pin到内存中，在pin之前需要先获取该对象的handle的锁定，lock的索引主要有nullshare exclusive，当然pin该对象也有上述三种状态。

Session a ：execute delete_data

临时取消了该procedure的运行，实际进程并没有马上释放该对象的pin

Session b：

SQL> create orreplace procedure delete_data

2 as

3 begin

4 loop

Delete fromTEXTAUTO_FOLDERARTICLE where LASTPOSTDATE<sysdate-10 and rownum<10000;

Exit whensql%notfound;--dml语句中隐式游标属性进行控制

Commit;

End loop;

9 Commit;

10 end;

11 /

出现了library cache pin等待事件：

SQL> selectevent,count(*) from v$session group by event;

EVENT COUNT(*)

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

SQL*Net messagefrom client 92

library cachepin 1

jobq slavewait 1

rdbms ipcmessage 10

smon timer 1

pmon timer 1

db file scatteredread 1

Streams AQ: qmnslave idle wait 1

SQL*Net message toclient 1

Streams AQ: qmncoordinator idle wait 1

Streams AQ: waitingfor time management or cleanup tasks 1

11 rows selected.

分析一下上述的library cache pin是如何造成的：首先系统运行了该procedure，此时该对象已经被pin到内存，占用了一个share pin和一个null lock，而此时在进程还没有释放此pin之前再次create procedure，此时要获取一个exclusive pin和exclusivelock，由于之前的share pin和exclusivepin不共存，此时就会产生一个library cache pin等待。

这里注意一下两个基表x$kglpn和x$kgllk，对于解决library cache pin和lock太轻松了。

SQL> descx$kglpn;

Name Null? Type

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

ADDR RAW(8)

INDX NUMBER

INST_ID NUMBER

KGLPNADR RAW(8)

KGLPNUSE RAW(8)

KGLPNSES RAW(8)

KGLPNHDL RAW(8)

KGLPNLCK RAW(8)

KGLPNCNT NUMBER

KGLPNMOD NUMBER

KGLPNREQ NUMBER

KGLPNDMK NUMBER

KGLPNSPN NUMBER

通过等待事件的p1raw参数联合kglpnhdl来获取kglpnuse，这里的kglpnmod和kglpnreq和v$lock的lmode和request基本相同，kglpnmod=2表示此时这个session占有了library cache pin而造成了kglpnreq=3这个session的等待。

SQL> selectkglpnuse,kglpnhdl,kglpnmod,kglpnreq from x$kglpn where kglpnhdl in (selectp1raw from v$session where event='library cache pin');

KGLPNUSE KGLPNHDL KGLPNMOD KGLPNREQ

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

00000000D822759000000000DD876D98 0 3

00000000DA22534000000000DD876D98 2 0

联合v$session的saddr来获取sid然后可以通过v$process来kill掉没有释放的进程。

SQL> selectb.spid,a.sid from v$session a,v$process b where a.saddr in ('00000000DA225340') and a.paddr=b.addr;

SPID SID

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

11232 195

而如果此时再重新编译一下该procedure，同样需要获取一个该对象的lock锁定，由于上述重建procedure还在视图获取该对象的librarycache lock的exclusive lock，那么当然此时出现librarycache lock是当然的。

SQL> alterprocedure delete_data compile;

SQL> selectevent,count(*) from v$session group by event;

EVENT COUNT(*)

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

SQL*Net messagefrom client 91

library cachepin 1

jobq slavewait 1

rdbms ipcmessage 10

smon timer 1

pmon timer 1

db file scatteredread 1

library cachelock 1

Streams AQ: qmnslave idle wait 1

SQL*Net message toclient 1

Streams AQ: qmncoordinator idle wait 1

SQL> descx$kgllk;

Name Null? Type

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

ADDR RAW(8)

INDX NUMBER

INST_ID NUMBER

KGLLKADR RAW(8)

KGLLKUSE RAW(8)

KGLLKSES RAW(8)

KGLLKSNM NUMBER

KGLLKHDL RAW(8)

KGLLKPNC RAW(8)

KGLLKPNS RAW(8)

KGLLKCNT NUMBER

KGLLKMOD NUMBER

KGLLKREQ NUMBER

KGLLKFLG NUMBER

KGLLKSPN NUMBER

KGLLKHTB RAW(8)

KGLNAHSH NUMBER

KGLLKSQLID VARCHAR2(13)

KGLHDPAR RAW(8)

KGLHDNSP NUMBER

USER_NAME VARCHAR2(30)

KGLNAOBJ VARCHAR2(60)

获取blocking session的方法基本相同，也是通过p1raw参数联合kgllkhdl来获取kgllkuse，最后联合v$session来获取sid，进而通过v$process获取spid，杀掉进程后即可

SQL> selectkgllkhdl,kgllkuse,kgllkreq,kgllkmod from x$kgllk where kgllkhdl in (Selectp1raw from v$session where event='library cache lock');

KGLLKHDL KGLLKUSE KGLLKREQ KGLLKMOD

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

00000000DD876D9800000000DA225340 0 1

00000000DD876D9800000000D8227590 0 3

00000000DD876D9800000000D821FC40 3 0

通过上述的library cache pin和library cache lock等待事件的分析和诊断，可以看出数据字典基表可以提供我们非常的信息，而帮助解决一些比较棘手的案例，当然上述我们也可以利用oradebug然后通过trace文件来诊断，不过相对trace文件较难以阅读，需要一定的功底，后续如果有对该等待事件trace的解决案例再拿出来与大家分享。

转自：http://www.xifenfei.com/3172.html

library cache pin说明
library cache pin 事件是用来管理library cache的并发访问的, pin一个object会引起相应的heap被载入内存中,如果客户端需要修改或检测这个object它就必须在锁住后取得一个pin.library cache pin的等待时间为3秒钟,其中有1秒钟用于PMON后台进程,即在取得pin之前最多等待3秒钟,否则就超时.library cache pin通常是发生在编译或重新编译PL/SQL,VIEW,TYPES等object时.编译通常都是显性的,如安装应用程序,升级,安装补丁程序等,但object的重新编译也可能发生在object变得无效时.library cache pin的参数如下,有用的主要是P1和P2:
P1 – KGL Handle address.
P2 – Pin address
P3 – 10*Mode + Namespace
其中,P1,P2可与x$kglpn和x$kglob表相关.x$kglpn和x$kglob是ORACLE数据库的内部数据字典.
x$kglpn library cache pin信息
x$kglob library cache object信息

查询方法一

 
        --通过查询V$SESSION_WAIT找出正在等待”library cache pin”的session 
       
        SELECT 
         sid, 
       
        SUBSTR (event, 1, 30), 
       
        TO_CHAR(p1,  
        'xxxxxxxx' 
        ) p1_16, 
       
        --P1RAW P1_16, 
       
        p2, 
       
        p3 
       
        FROM 
        v$session_wait 
       
        WHERE 
        wait_time = 0  
        AND 
         event  
        LIKE 
        'library cache pin%' 
        ; 
       
        --P1 列是Library Cache Handle Address 
       
        --P2 列是Library Cache Pin Address.  
       
        --找到相关session pin状态 
       
        SELECT 
         ADDR, 
       
        INDX, 
       
        KGLPNADR, 
        -- Library Cache Pin Address 
       
        KGLPNUSE, 
       
        KGLPNSES, 
        --识别锁住此pin 的session 
       
        KGLPNHDL, 
        --Library Cache Handle Address 
       
        kGLPNLCK, 
       
        KGLPNMOD, 
        -- Pin 锁  
       
        KGLPNREQ 
        -- Pin 请求 
       
        FROM 
        x$kglpn 
       
        WHERE 
        KGLPNHDL  
        LIKE 
         '%EB3EB8%' 
        ; 
        --p1_16 
       
        --询X$KGLOB (Library Cache Object),可找到相关的object 
       
        SELECT 
         KGLNAOBJ 
        -- 相关object的名字(取前面80个字符) 
       
        FROM 
        X$KGLOB 
       
        WHERE 
        KGLHDADR  
        LIKE 
         '%EB3EB8%' 
        ; 
        --p1_16 
       
        --查出占着pin锁的session目前正在做什么 
       
        SELECT 
         a.sid, a.username, a.program 
       
        FROM 
        v$session a, x$kglpn b 
       
        WHERE 
        a.saddr = b.kglpnuse  
        AND 
        b.kglpnhdl  
        LIKE 
         '%EB3EB8%' 
        --p1_16 
       
        AND 
        b.kgnmod <> 0; 
       
        --查出阻塞者正执行的SQL语句 
       
        SELECT 
        sid, sql_text 
       
        FROM 
        v$session, v$sqlarea 
       
        WHERE 
        v$session.sql_address = v$sqlarea.address  
        AND 
        sid =&sid;

查询方法二

 
        --通过查询DBA_LOCK_INTERNAL和V$SESSION_WAIT,可得到与”library cache pin” 等待相关的object的名字 
       
        SELECT 
         TO_CHAR (SESSION_ID,  
        '999' 
        ) sid, 
       
        SUBSTR (LOCK_TYPE, 1, 30) TYPE, 
       
        SUBSTR (lock_id1, 1, 23) Object_Name, 
       
        SUBSTR (mode_held, 1, 4) HELD, 
       
        SUBSTR (mode_requested, 1, 4) REQ, 
       
        lock_id2 Lock_addr 
       
        FROM 
        dba_lock_internal 
       
        WHERE 
        mode_requested <>  
        'None' 
        AND 
        mode_requested <> mode_held 
       
        AND 
        session_id  
        IN 
       
        ( 
        SELECT 
        sid 
       
        FROM 
        v$session_wait 
       
        WHERE 
        wait_time = 0  
        AND 
         event  
        LIKE 
        'library cache pin%' 
        ); 
       
        --查出”library cache pin”占有者(即阻塞者)的session id 
       
        SELECT 
        sid Holder, 
       
        KGLPNUSE Sesion, 
       
        KGLPNMOD Held, 
       
        KGLPNREQ Req 
       
        FROM 
        sys.x$kglpn, v$session 
       
        WHERE 
        KGLPNHDL  
        IN 
         ( 
        SELECT 
        p1raw 
       
        FROM 
        v$session_wait 
       
        WHERE 
        wait_time = 0  
        AND 
         event  
        LIKE 
        'library cache pin%' 
        ) 
       
        AND 
        KGLPNMOD <> 0 
       
        AND 
        v$session.saddr = x$kglpn.kglpnuse; 
       
        --查出”library cache pin”占有者(阻塞者)正在等什么 
       
        SELECT 
        sid, SUBSTR (event, 1, 30), wait_time 
       
        FROM 
        v$session_wait 
       
        WHERE 
        sid  
        IN 
       
        ( 
        SELECT 
        sid 
       
        FROM 
        x$kglpn, v$session 
       
        WHERE 
        KGLPNHDL  
        IN 
       
        ( 
        SELECT 
        p1raw 
       
        FROM 
        v$session_wait 
       
        WHERE 
        wait_time = 0 
       
        AND 
        event  
        LIKE 
         'library cache pin%' 
        ) 
       
        AND 
        KGLPNMOD <> 0 
       
        AND 
        v$session.saddr = x$kglpn.kglpnuse); 
       
        --查出阻塞者正执行的SQL语句 
       
        SELECT 
        sid, sql_text 
       
        FROM 
        v$session, v$sqlarea 
       
        WHERE 
        v$session.sql_address = v$sqlarea.address  
        AND 
        sid =&sid;

Ellison

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
library cache pin

转自：http://www.itpub.net/thread-1703738-1-1.html最近运行一个分批删除数据的procedure，感觉运行比较慢，临时取消了该job，然后再次重建procedure时，sessionhang住了，应该是library cache pin的等待事件了，oracle用两种结构pin和lock来控制shared pool的并发访问控制。访
复制链接

扫一扫