怎么发现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 aRAC 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 wayto know who is holding a pin on that object via instance views X$KGLOB to get the local instanceKGLHDADR => 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 the above procedure.
alter procedure dummy compile;=> session 2 will be blocked in 'library cache pin'.3. How to find the blockera. 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 EVENTP1RAW— —————– ——–150 library cache pin 288822D4select * from dba_kgllock where kgllkreq > 0;KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLL——– ——– ——– ——– —-2CB1F978 288822D403 Pin
2. find the locked object via x$kglob, e.g.
select kglnaown, kglnaobj from x$kglob where kglhdadr = '288822D4';KGLNAOWN KGLNAOBJ——– ——–SYSDUMMY3. find the kglhdadr in the other instances, e.g.—>这是在其他的实例上执行。
select kglhdadr, kglnaown, kglnaobj from x$kglob where kglnaobj = 'DUMMY';KGLHDADR KGLNAOWN KGLNAOBJ——– ——– ——–28577AD8 SYSDUMMY4. find the blocking session on the remote instance, e.g.—>这是在其他的实例上执行。
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;
如果说对云南有进一步的了解的话就是鲜花。
