怎么發現RAC環境中的'library cache pin'等待事件的堵塞者（Blocker）

參考自
How to Find the Blocker of the 'library cache pin' in a RAC environment? (文檔 ID 780514.1)

本文不做翻譯。全文轉載：

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 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 is begin null; end; /

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

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;

?

總結

以上是生活随笔為你收集整理的怎么发现RAC环境中#39;library cache pin#39;等待事件的堵塞者（Blocker）?的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。