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 Size2095672 bytesVariable Size96470472 bytesDatabase Buffers176160768 bytesRedo Buffers6291456 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———- ———- ———-15901SQL> 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———- ———- ———-15801SQL> 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———- ———- —————————————————————-15828 library cache pinSQL> select sid, serial#,p1raw,event from v$session where event like '%library cache pin%';SID SERIAL# P1RAWEVENT———- ———- —————- ————————————————–15828 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上。OwnerObject—————————— ——————————LC0019999DUMMYSQL> SQL> SELECT s.sid, kglpnmod "Mode", kglpnreq "Req" 2FROM x$kglpn p, v$session s 3WHERE p.kglpnuse=s.saddr 4AND kglpnhdl='000000006BFF19B0';—->该语句是查询出这个等待事件的等待者sid（REQ>0）和阻塞者sid（Mode>0）SIDModeReq———- ———- ———-1580315920SQL> 如下摘自：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.
，走马观花之外，这才是深入体验，探索自我的最佳时间，
