Problem simulation and problem analysis of library cache pin in Oracle Single instance case

Problem simulation and problem analysis of library cache pin in Oracle Single instance case

References 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 SID (Req>0) and bearer SIDs (MODE>0) that are queried for this wait event          SID Mode Req------------------------------158 0 3 159 2 0sql>---Supplement: for example, the following text is derived from: <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>

For example, the following excerpt from: waitevent: "Library cache Pin" Reference Note (document ID 34579.1) an X request (3) would be a blocked by any pins held S m Ode (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