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 DUMMY SQL> 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 0 SQL> ---补充：例如以下文字来源于： <blockquote><strong><code>Mode</code></strong><span> </span>is the mode in which the pin is wanted. This is a number thus:<ul><li>2 - Share mode</li><li>3 - Exclusive mode</li></ul></blockquote>
例如以下摘自：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.