How do I find the ' Library cache pin ' Waiting event blocker (blocker) in a RAC environment?

How to find the ' library cache pin ' Waiting event blocker (blocker) in a RAC environment

Reference from
How do I Find the blocker of the ' Library cache pin ' in a RAC environment? (Document ID 780514.1)

This article does not do translation, the full text reproduced:

Applies To:

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 are to explain what to understand who's blocking a ' library cache pin ' in a RAC environment

Fix

A. Introduction

V$session blocking fields can ' t is 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 is however different on each database instance. So, the pin/lock handle of the Dba_kgllock can ' t is used with a RAC database and view Dba_kgllock can just is used to lo Cate waiting sessions.

note:34579.1 can then is used to locate the object locked (via the X$kglob query) on some other instances.

Once you has the object locked, you can query each instance and drill down the opposite
To know who is holding a pin on this object via instance views X$kglob to get the local instance Kglhdadr = then v$ses Sion/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 the above procedure.

ALTER PROCEDURE dummy compile;=> session 2 would be a 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----------------------------Library cache pin 288822d4select * from Dba_kgllock where Kgllkreq &G T 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 performed 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 performed 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= ' 28577 AD8 ' 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

How do I find the ' Library cache pin ' Waiting event blocker (blocker) in a RAC environment?