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

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

前幾天管理的伺服器出現library cache pin,當時解決有點亂了陣腳,正好下午空閒做一個例子來定位library cache pin事件以及解決方法,另外我也看許多blog,感覺定位太複雜,不合適快速解決問題:

1.環境以及問題再現:

SQL> select * from v$version where rownum<=1;

BANNER
--------------------------------------------------------------------------------
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.這個指令碼對於快速定位很有用.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-754965/，如需轉載，請註明出處，否則將追究法律責任。