Problem simulation and problem analysis of library cache pin in Oracle Single instance case
Reference from:
Waitevent: "Library cache Pin" Reference Note (document ID 34579.1)
How do I Find the blocker of the ' Library cache pin ' in a RAC environment? (Document ID 780514.1)
Native environment: Oracle 10.2.0.5 x86-64bit for RHEL5.8 x86-64bit
First session:
[[email protected] ~]$ sqlplus/as sysdbasql*plus:release 10.2.0.5.0-production on Fri June 17:27:28 2015Copyri Ght (c) 1982, Oracle. All rights reserved.connected to an idle instance. Sql> Startuporacle instance started. Total System Global area 281018368 bytesfixed size 2095672 bytesvariable size 96470472 byte Sdatabase 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 all the time
A new session opened with the lc0029999 landing
[Email protected] ~]$ sqlplus lc0029999/aaaaaasql*plus:release 10.2.0.5.0-production on Fri June 17:34:04 2015Copyri Ght (c) 1982, Oracle. All rights reserved.connected to:oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit productionwith the part Itioning, OLAP, Data Mining and Real application testing optionssql> SELECT * from V$mystat where rownum<2; SID statistic# VALUE------------------------------ 158 0
A new session is opened with the SYS user
[[email protected] ~]$ sqlplus/as sysdbasql*plus:release 10.2.0.5.0-production on Fri June 17:31:33 2015Copyri Ght (c) 1982, Oracle. All rights reserved.connected to:oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit productionwith the part Itioning, OLAP, Data Mining and Real application testing optionssql> set line 200sql> Select SIDs, serial#,event from V$session where event like '%library cache pin% '; SID serial# EVENT------------------------------------------------------------------------------------158 The library cache pinsql> Select SID, Serial#,p1raw,event from v$session where event like '%library cache pin% '; SID serial# p1raw EVENT------------------------------------------------------------------------------- -------158 000000006bff19b0 Library Cache pinsql> Col owner for a30sql> Col object for a30sql> Select Kglnaown "Owner", Kglnaobj "Object" 2 from X$kglob3 WHERE kglhdadr= ' 000000006bff19b0 '----> P1raw value found above. 4; ----> The statement is to query which object the wait event occurred on. 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 ';----> The statement is the waiting-time sid (Req>0) and the blocker sid (Mode>0) that are queried for this wait event SID Mode Req------------------------------158 0 3 159 2 0sql>
An X request (3) would be blocked by any pins held S mode (2) on the object. An S request (2) would be blocked by any x mode (3) Pin held, or may queue behind some and other X request.
Problem simulation and problem analysis of library cache pin in Oracle Single instance case