library cache pin分析一例

library cache pin

    经常遇到修改存储过程后，编译时session挂起情况
   

       —拿到挂起session的 sid

select event,wait_time,SECONDS_IN_WAIT,STATE

 from v$session_wait

 where sid in(select sid from v$session where machine like ‘%ZHIRUO%’);

我们发现sid=17的session在等待’library cache pin’事件

 
–查到p1raw (KGL Handle address的16进制表示) 为573F4980 这个是library cache pin等待的对像的handle地址

SQL>    select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,WAIT_TIME ,SECONDS_IN_WAIT, state

   from v$session_wait where event like ‘library%’;

       SID       SEQ#

———- ———-

EVENT                                                                    P1

—————————————————————- ———-

P1RAW            P2 P2RAW            P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT

——– ———- ——– ———- ——– ———- —————

STATE

——————-

        17        119

library cache pin                                                1463765376

573F4980 1450601668 56766CC4        301 0000012D          0              83

WAITING

–查到handle的具体信息

SQL>   col KGLNAOWN for a10

SQL> col KGLNAOBJ for a20

SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ

  2  from X$KGLOB

  3  where KGLHDADR =’573F4980′

  4  /

SQL> /

ADDR     KGLHDADR KGLHDPAR KGLNAOWN   KGLNAOBJ               KGLNAHSH KGLHDOBJ

——– ——– ——– ———- ——————– ———- ——–

96F9BC48 573F4980 573F4980 TAOBAO     WINNER               1402406113 573C9D48

–当前持有handle的用户信息,可见是sid=20的session持有handle

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

  2  b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ

  3  from v$session a,x$kglpn b

  4  where a.saddr=b.kglpnuse and b.kglpnhdl = ‘573F4980’ and b.KGLPNMOD<>0

  5  /

       SID USERNAME

———- ——————————

PROGRAM                                          ADDR     KGLPNADR KGLPNUSE

———————————————— ——– ——– ——–

KGLPNSES KGLPNHDL KGLPNLCK   KGLPNMOD   KGLPNREQ

——– ——– ——– ———- ———-

        20 TAOBAO

                                                 96F9B9EC 5683484C 54296BD0

54296BD0 573F4980 56765374          2          0

–查看为什么sid=20会持有handle,它在等待什么？

SQL>  select * from v$session_wait where sid=20;

       SID       SEQ# EVENT                                                            P1TEXT                                                                   P1 P1RAW    P2TEXT                                                                   P2 P2RAW    P3TEXT                                                                   P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT STATE

———- ———- —————————————————————- —————————————————————- ———- ——– —————————————————————- ———- ——– —————————————————————- ———- ——– ———- ————— ——————-

        20      25857 SQL*Net message from dblink                                      driver id                                                                 0 00       #bytes                                                                    1 00000001                                                                           0 00                0             422 WAITING

–以下两句查看sid=20正在执行的sql
–我们知道，执行的sql刚好在winner过程中，所以重编译时导致挂起的原因是,另一个session正在执行它!

SQL> select SQL_HASH_VALUE from v$session where sid=20;

SQL_HASH_VALUE

————–

    3301010193

SQL> select sql_text

  2  from V$SQLTEXT_WITH_NEWLINES

  3  where HASH_VALUE=3301010193

  4  order by piece;

SQL_TEXT

—————————————————————-

SELECT sum(q)

  from (select count(*) q

          from tt.auction_auctions@lnk_db212

         where OLD_STARTS < STARTS

           and approve_status >= 0

           and starts >= :b2

           and starts < :b1

        union all

        select count(*) q

          from tt.auction_auctions@lnk_db210

         where OLD_STARTS < STARTS

           and approve_status >= 0

           and starts >= :b2

           and starts < :b1)

   –将该session kill掉后再编译正常

SQL> select serial# from v$session where sid=20;

   SERIAL#

———-

       534          

SQL> alter system kill session ‘20,534’;

参考资料：  http://www.eygle.com/internal/shared_pool-5.htm

未经允许不得转载：SRE空间 » library cache pin分析一例