Simulation and Problem Analysis of library cache pin in case of a single Oracle instance, oraclepin
Simulation and Analysis of library cache pin problems with a single Oracle instance
Reference:
WAITEVENT: "library cache pin" Reference Note (Doc ID 34579.1)
How to Find the Blocker of the 'library cache pin' in a RAC environment? (Documentation ID 780514.1)
Local Environment: Oracle 10.2.0.5 x86-64bit for RHEL5.8 x86-64bit
First session:
[Oracle @ localhost ~] $ Sqlplus/as sysdbaSQL * Plus: Release 10.2.0.5.0-Production on Fri Jun 12 17:27:28 2015 Copyright (c) 1982,201 0, 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 lc00%9 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 "lc005o9" 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/----------> always sleep
Log on to a new session with lc0029999
[Oracle @ localhost ~] $ Sqlplus lc0029999/aaaaaaSQL * Plus: Release 10.2.0.5.0-Production on Fri Jun 12 17:34:04 2015 Copyright (c) 1982,201 0, 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 lc004.7 9.dummy compile; ------------> always hang
Start a new session as a sys user
[Oracle @ localhost ~] $ Sqlplus/as sysdbaSQL * Plus: Release 10.2.0.5.0-Production on Fri Jun 12 17:31:33 2015 Copyright (c) 1982,201 0, 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 c Ache 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 ---------- -------------------- Jun 158 28 Jun 6bff19b0 library cache pinSQL> col owner For a30SQL> col object for a30SQL> SELECT kglnaown "Owner", kglnaobj "Object" 2 FROM x $ kglob 3 WHERE kglhdadr = '2017100006bff19b0' ----> P1RAW value found above. 4; ----> this statement is used to query the object on which the wait event occurs. Owner Object ------------------------------ lc00%9 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 = 'wait 6bff19b0'; ----> this statement is used to query the waiting sid (REQ> 0) AND blocking sid (Mode> 0) of the event) SID Mode Req ---------- 158 0 3 159 2 0SQL>
From WAITEVENT: "library cache pin" Reference Note (Document 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.