How can I find the library cache pin in the RAC environment )?, Racblocker

How can we find the 'library cache pin' In the RAC environment to wait for the Blocker of the event )?, Racblocker

How to find the 'library cache pin' In the RAC environment and wait for the Blocker of the event)

Reference
How to Find the Blocker of the 'library cache pin' in a RAC environment? (Documentation ID 780514.1)

This article is not translated. The full text is reprinted:

Applies:

Oracle Database-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

Fix

A. introduction

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

Note: 169139.1 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.

Note: 34579.1 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 isbeginnull;end;/

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

BeginDummy;Dbms_lock.sleep(1000);End;/

3. Session 2 on instance2: Compile 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 288822D4select * 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. ---> This is executed on other instances.

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. ---> This is executed on other instances.

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