[20130228]等待事件library cache pin的快速定位与解决.txt

前几天管理的服务器出现library cache pin,当时解决有点乱了阵脚,正好下午空闲做一个例子来定位library cache pin事件以及解决方法,另外我也看许多blog,感觉定位太复杂,不合适快速解决问题:

1.环境以及问题再现:

SQL> select * from v$version where rownumBANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

CREATE OR REPLACE PROCEDURE proc1
IS
BEGIN
   DBMS_LOCK.sleep (1000);
END;
/

--在windows下执行如下命令,按ctrl+c中断它[注在linux下不行!]
SQL> exec proc1;

--再打开一个回话,重新建立过程proc1,执行如下:
CREATE OR REPLACE PROCEDURE proc1
IS
BEGIN
   DBMS_LOCK.sleep (1000);
END;
/

--出现挂起.  

SQL> select sid,event,p1,p2,p3 from v$session_wait where wait_time=0 and event like 'library cache pin%';

       SID EVENT                                            P1         P2                      P3

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

       191 library cache pin                        3001221336 3000130456         416409964314627

参考文档:
http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_library_cache_pin_pct.html

column h_wait format A20

SELECT s.sid,

    waiter.p1raw w_p1r,

    holder.event h_wait,

    holder.p1raw h_p1r,

    holder.p2raw h_p2r,

    holder.p3raw h_p2r,

    count(s.sid) users_blocked,

    sql.hash_value

FROM

    v$sql sql,

    v$session s,

    x$kglpn p,

    v$session_wait waiter,

    v$session_wait holder

WHERE

    s.sql_hash_value = sql.hash_value and

    p.kglpnhdl=waiter.p1raw and

    s.saddr=p.kglpnuse and

    waiter.event like 'library cache pin' and

    holder.sid=s.sid

GROUP BY

    s.sid,

    waiter.p1raw ,

    holder.event ,

    holder.p1raw ,

    holder.p2raw ,

    holder.p3raw ,

    sql.hash_value

;

--我修改一点点加入sql_id,sql_text.

SELECT   s.SID, waiter.p1raw w_p1r, holder.event h_wait, holder.p1raw h_p1r, holder.p2raw h_p2r, holder.p3raw h_p2r,

         COUNT (s.SID) users_blocked, SQL.sql_id, SQL.hash_value, SQL.sql_text

    FROM v$sql SQL, v$session s, x$kglpn p, v$session_wait waiter, v$session_wait holder

   WHERE s.sql_hash_value = SQL.hash_value

     AND p.kglpnhdl = waiter.p1raw

     AND s.saddr = p.kglpnuse

     AND waiter.event LIKE 'library cache pin'

     AND holder.SID = s.SID

GROUP BY s.SID, waiter.p1raw, holder.event, holder.p1raw, holder.p2raw, holder.p3raw, SQL.sql_id, SQL.hash_value, SQL.sql_text

  SID W_P1R            H_WAIT             H_P1R            H_P2R            H_P2R            USERS_BLOCKED SQL_ID        HASH_VALUE SQL_TEXT

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

   68 00000000B2E300D8 PL/SQL lock timer  00000000000186A0 00               00                           1 7ap74x3urn7f7 4118420935 BEGIN proc1; END;

--找到sid=68,kill该进程OK.这个脚本对于快速定位很有用.