ITPub博客

首页 > Linux操作系统 > Linux操作系统 > library cache lock/pin

library cache lock/pin

原创 Linux操作系统 作者:gaopengtttt 时间:2012-05-04 16:14:09 0 删除 编辑

原创:转载请说明

X$KSMLRU that tracks allocations in the shared pool that cause other objects in th e shared pool to be aged out
诊断 library cache lock/pin类型:
不管是访问还是修改library中的heap的信息,都需要先获得library cache lock这个锁实际是对handle进行锁定,修改需要加独占模式,访问需要共享模式,然后访问heap0的信息
访问heap的信息通过library cache pin进行锁定,访问加共享,修改加独占。下面是一些诊断时需要的x$视图说明,另外还说明一年v$session_wait中p3值是模式和对象类型的和,比如
301=3(mode)*100+1(namespace) 3是独占,1是table/procedure
mode 1=null 2=shared 3=exclusive
namespace
0=cursor(sql area)
1=table,procedure and others
2=package body
3=trigger
4=index
5=cluster
6=object
7=pipe
13=java source
14=java resource
32=java data
x$kgllk 找到引起library cache lock 的信息,其中KGLLKSES对应了session saddr,KGLHDPAR对应了P1的值,KGLLKADR对应了P2的值是一个lock 的地址,KGLNAOBJ就是当前等待的对象
KGLlkREQ定义了需要的模式>0为等待用户1为null 2 为 share 3 Exclusive,通过这个字段然后找到KGLLKSES可以确定是那个会话堵塞。KGLLKMOD是持有模式,KGLLKSNM是SID,其实KGLLKSES也可以找到

SQL> select * from v$session_wait where wait_class<>'Idle';

        SID       SEQ# EVENT                                                            P1TEXT                                                                   P1 P1RAW            P2TEXT                                                                   P2 P2RAW            P3TEXT                                                                   P3 P3RAW            WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                                                        WAIT_TIME SECONDS_IN_WAIT STATE               WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO

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


          139      11264 library cache lock                                               handle address                                                   1949336832 0000000074308500 lock address                                                     1959726640 0000000074CF0E30 100*mode+namespace                                               3301842008 00012C4D00010003    3875070507           4 Concurrency                                                               0               0 WAITING                          87                   -1                          0

 


SQL>  select  KGLLKSNM ,INST_ID,KGLLKSES,KGLLKMOD,KGLLKREQ,USER_NAME,KGLNAOBJ from x$kgllk  where KGLHDPAR='0000000074308500'; 这里是P1RAW

 

  KGLLKSNM    INST_ID KGLLKSES           KGLLKMOD   KGLLKREQ USER_NAME                      KGLNAOBJ

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

       139          2 000000007CBF6228          0          3 SYS                            LIB_TEST

        31          2 000000007CA55248          3          0 SYS                            LIB_TEST

 


这里明显的31 堵塞了139


x$kglob 可以通过library cache lock/pin的p1raw值对应KGLHDPAR即可找到,KGLHDPAR就是句柄的地址(handle address),kglobtyp是对象类型的代码
SQL> select * from x$kglob  where KGLHDPAR='39E43150';
 
ADDR           INDX    INST_ID KGLHDADR KGLHDPAR   KGLHDCLT KGLNAOWN                                                         KGLNAOBJ                                                                         KGLFNOBJ                                                                         KGLNADLK                                                           KGLNAHSH KGLNAHSV                          KGLNATIM    KGLNAPTM      KGLHDNSP   KGLHDLMD   KGLHDPMD   KGLHDFLG KGLHDOBJ   KGLHDLDC   KGLHDIVC   KGLHDEXC   KGLHDLKC   KGLHDKMK   KGLHDDMK   KGLHDAMK   KGLOBFLG   KGLOBSTA   KGLOBTYP   KGLOBHMK   KGLOBHS0   KGLOBHS1   KGLOBHS2   KGLOBHS3   KGLOBHS4   KGLOBHS5   KGLOBHS6   KGLOBHS7 KGLOBHD0 KGLOBHD1 KGLOBHD2 KGLOBHD3 KGLOBHD4 KGLOBHD5 KGLOBHD6 KGLOBHD7   KGLOBPC0   KGLOBPC6 KGLOBTP0   KGLOBT00   KGLOBT01   KGLOBT02 KGLOBT03        KGLOBT04   KGLOBT05   KGLOBT35   KGLOBT06   KGLOBT07   KGLOBT08   KGLOBT09   KGLOBT10   KGLOBT11   KGLOBT12   KGLOBT13   KGLOBT14   KGLOBT15   KGLOBT16   KGLOBT17   KGLOBT18   KGLOBT19   KGLOBT20   KGLOBT21   KGLOBT22   KGLOBT23   KGLOBT24   KGLOBT25   KGLOBT26   KGLOBT28   KGLOBT29   KGLOBT30   KGLOBT31   KGLOBT27   KGLOBT32   KGLOBT33   KGLOBWAP   KGLOBWCC   KGLOBWCL   KGLOBWUI   KGLOBWDW   KGLOBT42   KGLOBT43   KGLOBT44   KGLOBT45   KGLOBT46   KGLOBT47   KGLOBT49   KGLOBT50   KGLOBTL0   KGLOBTL1 KGLOBTS0                                                         KGLOBTS1                                                           KGLOBTN0   KGLOBTN1   KGLOBTN2   KGLOBTN3   KGLOBTN4   KGLOBTN5 KGLOBTS2                                                         KGLOBTS3                                                         KGLOBTS5                                                         KGLOBTT0    KGLOBCCE                                                                          KGLOBCCEH KGLOBCLA      KGLOBCLC   KGLOBCCC KGLOBTS4                       KGLOBCBCA                                                                          KGLOBT48   KGLOBDS
-------- ---------- ---------- -------- -------- ---------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------- --------------------------------- ----------- ----------- ---------- ---------- ---------- ---------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- ---------- ---------- -------- ---------- ---------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- ---------- ----------- ---------- ---------- ------------------------------ -------------------------------------------------------------------------------- ---------- ---------
B7DCCF88       3310          1 39E43150 39E43150          1 SYS                                                              LIB_TEST                                                                         LIB_TEST                                                                                                                                          2173132718 486d0dbc99f10bc85579090681875fae  2012/5/4 22 2012/5/4 22          1          1          0   33554432 40749120     302370          0          0          4          0        157          0          5          1          7          0        348          0       4096          0       4096          0          0       1072 43E9EAA4 00       407492F0 00       40749340 00       00       40749390          0          0 00                0          0          0                        0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0          0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              0                      0          0                                                                                                                          0         
 

v$kglpn:用于找到library cache pin 的相关信息,KGLPNSES对应了session 的saddr,KGLPNHDL对应了P1RAW,KGLPNADR对应了PIN的地址 也就是P2RAW,如果要查找对象可以在x$kglob通过KGLHDPAR对应P1RAW找到。

P3同样是模式和对象,参考上面。

但是注意V$KGLPN中不会有实际的对象,如果要找到实际的对象必须通过X$KGLPN的kglpnhdl字段或者直接用P1RAW连接X$KGLOB视图的 kglhdpar字段

找到相应的object

同时在进行诊断的时候实际上堵塞源头是不会出现在V$SESSION_WAIT中的

SQL> select KGLPNADR,KGLPNHDL,KGLPNMOD,KGLPNREQ,KGLPNSES from x$kglpn  where KGLPNHDL='E760DDE4';
 
KGLPNADR KGLPNHDL   KGLPNMOD   KGLPNREQ KGLPNSES
-------- -------- ---------- ---------- --------
EDD0E97C E760DDE4          0          2 F0ADAB5C
EE650D7C E760DDE4          0          2 F0B4B65C
EDC11F14 E760DDE4          0          2 F0AD9894
EDB4B8C8 E760DDE4          3          0 F0B1B454
 
SQL> select event,p1RAW,p2raw from v$session_wait where wait_Class<>'Idle';
 
EVENT                                                            P1RAW    P2RAW
---------------------------------------------------------------- -------- --------
library cache pin                                                E760DDE4 EDC11F14
library cache pin                                                E760DDE4 EDD0E97C
SQL*Net message to client                                        54435000 00000001
library cache pin                                                E760DDE4 EE650D7C

可以看到EDB4B8C8并不出现在V$SESSION_WAIT中,它就是堵塞源头,通过KGLPNSES找到SID干掉它即可。

KGLPNREQ定义了需要的模式>0为等待用户1为null 2 为 share 3 Exclusive,通过这个字段然后找到KGLLKSES可以确定是那个会话堵塞了那个会话。而KGLPNMOD则是持有模式。
SQL> select * from x$kglpn  where KGLPNHDL='39E43150'; 这里也是P1RAW
 
ADDR           INDX    INST_ID KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK   KGLPNCNT   KGLPNMOD   KGLPNREQ   KGLPNDMK   KGLPNSPN
-------- ---------- ---------- -------- -------- -------- -------- -------- ---------- ---------- ---------- ---------- ----------
B7F44128          9          1 41FA7F30 442F7E38 442F7E38 39E43150 00                0          0          3          0   55077698
B7F4415C         10          1 423F9DF8 4430268C 4430268C 39E43150 423F128C         -2          2          0         17   28437228
  
 我遇到一次ORA - 04021就是有大量的PIN照成的

学习,持续更新中

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

请登录后发表评论 登录
全部评论
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解

注册时间:2008-10-13

  • 博文量
    698
  • 访问量
    2968906