the Blocker of the 'library cache pin'

 

How to Find the Blocker of the 'library cache pin' in a RAC environment? [ID 780514.1]
	Modified 15-DEC-2010     Type HOWTO     Status PUBLISHED

In this Document
  
  
  

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 - Release: 9.2 to 11.1
Information in this document applies to any platform.
Add ***Checked for relevance on 15-Dec-2010***

Goal

The goal of this note is to explain how to understand who is blocking a 'library cache pin' in a RAC environment

Solution

a. introduction

V$SESSION blocking fields can't be used for library cache locks/pins.

explains how to diagnose library cache locks/pin in single instance systems. The pin/lock handles are however different on each database instance. So, the pin/lock handle of the  DBA_KGLLOCK can't be used with a  RAC database and view DBA_KGLLOCK can just be used to locate waiting sessions.

can then be used to locate the object locked (via the x$kglob query) on some other instances.

Once you have the object locked, you can query  each instance and drill down the opposite way
to know who is holding a pin on that object via instance views X$KGLOB to get the local instance KGLHDADR => then v$session/DBA_KGLLOCK.

2. scenario example

Scenario to simulate a 'library cache pin' problem

a. Session 1 on instance1: Create a dummy procedure:

Create or replace procedure dummy is
begin
null;
end;
/

b. Session 1 on instance1: Execute the above procedure in a PL/SQL block to block the dummy

procedure
Begin
Dummy;
Dbms_lock.sleep(1000);
End;
/

3. Session 2 on instance2: Compile the  the above procedure.

alter procedure dummy compile;

=> session 2 will be blocked in 'library cache pin'.

3. How to find the blocker

a. find the p1raw value of the 'library cache pin', e.g.

select sid, event, p1raw from v$session_wait where event = 'library cache pin';
SID EVENT             P1RAW
--- ----------------- --------
150 library cache pin 288822D4
select * from dba_kgllock where kgllkreq > 0;
KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLL
-------- -------- -------- -------- ----
2CB1F978 288822D4        0        3 Pin

 

2. find the locked object via x$kglob, e.g.

select kglnaown, kglnaobj from x$kglob where kglhdadr = '288822D4';
KGLNAOWN KGLNAOBJ
-------- --------
SYS      DUMMY

3. find the kglhdadr in the other instances, e.g.

select kglhdadr, kglnaown, kglnaobj from x$kglob where kglnaobj = 'DUMMY';
KGLHDADR KGLNAOWN KGLNAOBJ
-------- -------- --------
28577AD8 SYS      DUMMY

4. find the blocking session on the remote instance, e.g.

select sid, serial#, sql_text from dba_kgllock w, v$session s, v$sqlarea a
where w.kgllkuse = s.saddr and w.kgllkhdl='28577AD8'
and s.sql_address = a.address
and s.sql_hash_value = a.hash_value;
SID SERIAL# SQL_TEXT
--- ------- ---------------------------------------
155    939  begin dummy; dbms_lock.sleep(1000); end;

References

- How to analyze ORA-04021 or ORA-4020 errors?
- WAITEVENT: "library cache pin" Reference Note

Related

Products

·Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Keywords

DBA_KGLLOCK; LIBRARY CACHE PIN; LOCK; LIBRARY CACHE LOCK