Oracle單一實例情況下的library cache pin的問題類比與問題分析，oraclepin

Oracle單一實例情況下的library cache pin的問題類比與問題分析，oraclepin

Oracle單一實例情況下的library cache pin的問題類比與問題分析

參考自：

WAITEVENT: "library cache pin" Reference Note (文檔 ID 34579.1)
How to Find the Blocker of the 'library cache pin' in a RAC environment? (文檔 ID 780514.1)

 

本機環境：Oracle 10.2.0.5 x86-64bit for RHEL5.8 x86-64bit

 

第一個session：

[oracle@localhost ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Fri Jun 12 17:27:28 2015Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  281018368 bytesFixed Size                  2095672 bytesVariable Size              96470472 bytesDatabase Buffers          176160768 bytesRedo Buffers                6291456 bytesDatabase mounted.Database opened.SQL> create user lc0019999 identified by aaaaaa;User created.SQL> grant dba to lc0019999;Grant succeeded.SQL> create user lc0029999 identified by aaaaaa;User created.SQL> grant dba to lc0029999;Grant succeeded.SQL> create user lc0039999 identified by aaaaaa;User created.SQL> grant dba to lc0039999;Grant succeeded.SQL> conn lc0019999/aaaaaaConnected.SQL> show userUSER is "LC0019999"SQL> select * from v$mystat where rownum<2;       SID STATISTIC#      VALUE---------- ---------- ----------       159          0          1SQL> Create or replace procedure dummy is  2   begin  3   null;  4   end;  5   /Procedure created.SQL> Begin  2   Dummy;  3   Dbms_lock.sleep(1000);  4   End;  5   /------------>一直sleep著

以lc0029999登陸新開的一個session

[oracle@localhost ~]$ sqlplus lc0029999/aaaaaaSQL*Plus: Release 10.2.0.5.0 - Production on Fri Jun 12 17:34:04 2015Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from v$mystat where rownum<2;       SID STATISTIC#      VALUE---------- ---------- ----------       158          0          1SQL> SQL> alter procedure lc0019999.dummy compile;------------>一直hang著 

 

以sys使用者新開一個session

[oracle@localhost ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Fri Jun 12 17:31:33 2015Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> set line 200SQL> select sid, serial#,event from v$session where event like '%library cache pin%';       SID    SERIAL# EVENT---------- ---------- ----------------------------------------------------------------       158         28 library cache pinSQL> select sid, serial#,p1raw,event from v$session where event like '%library cache pin%';       SID    SERIAL# P1RAW            EVENT---------- ---------- ---------------- --------------------------------------------------       158         28 000000006BFF19B0 library cache pinSQL> col owner for a30SQL> col object for a30SQL> SELECT kglnaown "Owner", kglnaobj "Object"  2  FROM x$kglob  3  WHERE kglhdadr='000000006BFF19B0'---->上面查出的P1RAW值。  4  ;                                ---->該語句是查詢出這個等待事件發生在哪個object上。Owner                          Object------------------------------ ------------------------------LC0019999                      DUMMYSQL> SQL> SELECT s.sid, kglpnmod "Mode", kglpnreq "Req"  2      FROM x$kglpn p, v$session s  3     WHERE p.kglpnuse=s.saddr  4       AND kglpnhdl='000000006BFF19B0';---->該語句是查詢出這個等待事件的等待者sid（REQ>0）和阻塞者sid（Mode>0）       SID       Mode        Req---------- ---------- ----------       158          0          3       159          2          0SQL>  

 如下摘自：WAITEVENT: "library cache pin" Reference Note (文檔 ID 34579.1) An X request (3) will be blocked by any pins held S mode (2) on the object. An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.