原创：转载请说明
X$KSMLRU that tracks allocations in the shared pool that cause other objects in th e shared pool to be aged out
诊断 library cache lock/pin类型：
不管是访问还是修改library中的heap的信息，都需要先获得library cache lock这个锁实际是对handle进行锁定，修改需要加独占模式，访问需要共享模式，然后访问heap0的信息
访问heap的信息通过library cache pin进行锁定，访问加共享，修改加独占。下面是一些诊断时需要的x$视图说明，另外还说明一年v$session_wait中p3值是模式和对象类型的和，比如
301=3(mode)*100+1(namespace) 3是独占，1是table/procedure
mode 1=null 2=shared 3=exclusive
namespace
0=cursor（sql area）
1=table,procedure and others
2=package body
3=trigger
4=index
5=cluster
6=object
7=pipe
13=java source
14=java resource
32=java data
x$kgllk 找到引起library cache lock 的信息，其中KGLLKSES对应了session saddr，KGLHDPAR对应了P1的值，KGLLKADR对应了P2的值是一个lock 的地址，KGLNAOBJ就是当前等待的对象
KGLlkREQ定义了需要的模式>0为等待用户1为null 2 为 share 3 Exclusive，通过这个字段然后找到KGLLKSES可以确定是那个会话堵塞。KGLLKMOD是持有模式，KGLLKSNM是SID，其实KGLLKSES也可以找到
SQL> select * from v$session_wait where wait_class'Idle';
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- ------------------- --------------- -------------------- --------------------------
139 11264 library cache lock handle address 1949336832 0000000074308500 lock address 1959726640 0000000074CF0E30 100*mode+namespace 3301842008 00012C4D00010003 3875070507 4 Concurrency 0 0 WAITING 87 -1 0
SQL> select KGLLKSNM ,INST_ID,KGLLKSES,KGLLKMOD,KGLLKREQ,USER_NAME,KGLNAOBJ from x$kgllk where KGLHDPAR='0000000074308500'; 这里是P1RAW
KGLLKSNM INST_ID KGLLKSES KGLLKMOD KGLLKREQ USER_NAME KGLNAOBJ
---------- ---------- ---------------- ---------- ---------- ------------------------------ ------------------------------------------------------------
139 2 000000007CBF6228 0 3 SYS LIB_TEST
31 2 000000007CA55248 3 0 SYS LIB_TEST
这里明显的31 堵塞了139
x$kglob 可以通过library cache lock/pin的p1raw值对应KGLHDPAR即可找到，KGLHDPAR就是句柄的地址（handle address）,kglobtyp是对象类型的代码
SQL> select * from x$kglob where KGLHDPAR='39E43150';
ADDR INDX INST_ID KGLHDADR KGLHDPAR KGLHDCLT KGLNAOWN KGLNAOBJ KGLFNOBJ KGLNADLK KGLNAHSH KGLNAHSV KGLNATIM KGLNAPTM KGLHDNSP KGLHDLMD KGLHDPMD KGLHDFLG KGLHDOBJ KGLHDLDC KGLHDIVC KGLHDEXC KGLHDLKC KGLHDKMK KGLHDDMK KGLHDAMK KGLOBFLG KGLOBSTA KGLOBTYP KGLOBHMK KGLOBHS0 KGLOBHS1 KGLOBHS2 KGLOBHS3 KGLOBHS4 KGLOBHS5 KGLOBHS6 KGLOBHS7 KGLOBHD0 KGLOBHD1 KGLOBHD2 KGLOBHD3 KGLOBHD4 KGLOBHD5 KGLOBHD6 KGLOBHD7 KGLOBPC0 KGLOBPC6 KGLOBTP0 KGLOBT00 KGLOBT01 KGLOBT02 KGLOBT03 KGLOBT04 KGLOBT05 KGLOBT35 KGLOBT06 KGLOBT07 KGLOBT08 KGLOBT09 KGLOBT10 KGLOBT11 KGLOBT12 KGLOBT13 KGLOBT14 KGLOBT15 KGLOBT16 KGLOBT17 KGLOBT18 KGLOBT19 KGLOBT20 KGLOBT21 KGLOBT22 KGLOBT23 KGLOBT24 KGLOBT25 KGLOBT26 KGLOBT28 KGLOBT29 KGLOBT30 KGLOBT31 KGLOBT27 KGLOBT32 KGLOBT33 KGLOBWAP KGLOBWCC KGLOBWCL KGLOBWUI KGLOBWDW KGLOBT42 KGLOBT43 KGLOBT44 KGLOBT45 KGLOBT46 KGLOBT47 KGLOBT49 KGLOBT50 KGLOBTL0 KGLOBTL1 KGLOBTS0 KGLOBTS1 KGLOBTN0 KGLOBTN1 KGLOBTN2 KGLOBTN3 KGLOBTN4 KGLOBTN5 KGLOBTS2 KGLOBTS3 KGLOBTS5 KGLOBTT0 KGLOBCCE KGLOBCCEH KGLOBCLA KGLOBCLC KGLOBCCC KGLOBTS4 KGLOBCBCA KGLOBT48 KGLOBDS
-------- ---------- ---------- -------- -------- ---------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------- --------------------------------- ----------- ----------- ---------- ---------- ---------- ---------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- ---------- ---------- -------- ---------- ---------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- ---------- ----------- ---------- ---------- ------------------------------ -------------------------------------------------------------------------------- ---------- ---------
B7DCCF88 3310 1 39E43150 39E43150 1 SYS LIB_TEST LIB_TEST 2173132718 486d0dbc99f10bc85579090681875fae 2012/5/4 22 2012/5/4 22 1 1 0 33554432 40749120 302370 0 0 4 0 157 0 5 1 7 0 348 0 4096 0 4096 0 0 1072 43E9EAA4 00 407492F0 00 40749340 00 00 40749390 0 0 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
v$kglpn：用于找到library cache pin 的相关信息，KGLPNSES对应了session 的saddr，KGLPNHDL对应了P1RAW，KGLPNADR对应了PIN的地址 也就是P2RAW，如果要查找对象可以在x$kglob通过KGLHDPAR对应P1RAW找到。
P3同样是模式和对象，参考上面。
但是注意V$KGLPN中不会有实际的对象，如果要找到实际的对象必须通过X$KGLPN的kglpnhdl字段或者直接用P1RAW连接X$KGLOB视图的 kglhdpar字段
找到相应的object
同时在进行诊断的时候实际上堵塞源头是不会出现在V$SESSION_WAIT中的
SQL> select KGLPNADR,KGLPNHDL,KGLPNMOD,KGLPNREQ,KGLPNSES from x$kglpn where KGLPNHDL='E760DDE4';
KGLPNADR KGLPNHDL KGLPNMOD KGLPNREQ KGLPNSES
-------- -------- ---------- ---------- --------
EDD0E97C E760DDE4 0 2 F0ADAB5C
EE650D7C E760DDE4 0 2 F0B4B65C
EDC11F14 E760DDE4 0 2 F0AD9894
EDB4B8C8 E760DDE4 3 0 F0B1B454
SQL> select event,p1RAW,p2raw from v$session_wait where wait_Class'Idle';
EVENT P1RAW P2RAW
---------------------------------------------------------------- -------- --------
library cache pin E760DDE4 EDC11F14
library cache pin E760DDE4 EDD0E97C
SQL*Net message to client 54435000 00000001
library cache pin E760DDE4 EE650D7C
可以看到EDB4B8C8并不出现在V$SESSION_WAIT中，它就是堵塞源头，通过KGLPNSES找到SID干掉它即可。
KGLPNREQ定义了需要的模式>0为等待用户1为null 2 为 share 3 Exclusive，通过这个字段然后找到KGLLKSES可以确定是那个会话堵塞了那个会话。而KGLPNMOD则是持有模式。
SQL> select * from x$kglpn where KGLPNHDL='39E43150'; 这里也是P1RAW
ADDR INDX INST_ID KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNCNT KGLPNMOD KGLPNREQ KGLPNDMK KGLPNSPN
-------- ---------- ---------- -------- -------- -------- -------- -------- ---------- ---------- ---------- ---------- ----------
B7F44128 9 1 41FA7F30 442F7E38 442F7E38 39E43150 00 0 0 3 0 55077698
B7F4415C 10 1 423F9DF8 4430268C 4430268C 39E43150 423F128C -2 2 0 17 28437228
我遇到一次ORA - 04021就是有大量的PIN照成的
学习，持续更新中