解決Library Cache latchs

可參照metalink文章

Subject: HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK
Doc ID: Note:122793.1Type: TROUBLESHOOTING
Last Revision Date: 25-JUL-2005Status: PUBLISHED

[@more@]

查詢v$session_wait

p1raw表示: 16進位制Libarry Cache Handle Address.

SQL> select sid,event,p1,p1raw from v$session_wait where event like 'library%';

SID EVENT
---------- ----------------------------------------------------------------
P1 P1RAW
---------- --------
138 library cache lock
1213129416 484EE2C8

139 library cache pin
1213129416 484EE2C8

查詢session id為138，139的OS Process和Oracle Process

SQL> SELECT SPID,PID FROM V$PROCESS WHERE ADDR=(SELECT PADDR FROM V$SESSION WHERE SID=138);

SPID PID
------------ ----------
7534 21

SQL> SELECT SPID,PID FROM V$PROCESS WHERE ADDR=(SELECT PADDR FROM V$SESSION WHERE SID=139);

SPID PID
------------ ----------
7485 17

使用SYSTEMSTATE事件察看當前Oracle Process正在經歷哪些等待
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

Session altered.

進入$ORACLE_BASE/admin/sid/udump/目錄

[oracle@ly udump]$ ll -ctl
total 106756
-rw-r----- 1 oracle oinstall 835396 Jun 6 17:22 lion_ora_7578.trc

我們以關鍵字 "PROCESS 21"和"PROCESS 17"遍歷lion_ora_7578.trc.

發現PROCESS 21正在waiting for 'library cache lock',handle address=484ee2c8

PROCESS 21:
----------------------------------------
SO: 0x48a1d48c, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=21, calls cur/top: 0x48b2a3dc/0x48b2a3dc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 0
last post received-location: No post
last process to post me: none
last post sent: 0 0 0
last post sent-location: No post
last process posted by me: none
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0x48a4c604
O/S info: user: oracle, term: pts/2, ospid: 7534
OSD pid info: Unix process pid: 7534, image: (TNS V1-V3)
Dump of memory from 0x48A09944 to 0x48A09AC8
48A09940 00000004 483D48C8 00000010 [.....H=H....]
48A09950 0003139D 48B2A3DC 00000003 0003139D [.......H........]
48A09960 4807ADB4 0000000B 0003139D 48AF2B94 [...H.........+.H]
48A09970 00000004 00031291 00000000 00000000 [................]
48A09980 00000000 00000000 00000000 00000000 [................]
Repeat 19 times
48A09AC0 00000000 00000000 [........]
----------------------------------------
SO: 0x48af2b94, type: 4, owner: 0x48a1d48c, flag: INIT/-/-/0x00
(session) sid: 138 trans: 0x47601ec0, creator: 0x48a1d48c, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
txn branch: (nil)
oct: 17, prv: 0, sql: 0x4871c418, psql: 0x484f1d68, user: 67/LIYONG
O/S info: user: oracle, term: pts/2, ospid: 7530, machine: ly
program: (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache lock' blocking sess=0x(nil) seq=33 wait_time=0 seconds since wait started=327
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
Dumping Session Wait History
for 'library cache lock' count=1 wait_time=2936004
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
for 'library cache lock' count=1 wait_time=2929666
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
for 'library cache lock' count=1 wait_time=2929670
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
for 'library cache lock' count=1 wait_time=2929666
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
for 'library cache lock' count=1 wait_time=2929668
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
for 'library cache lock' count=1 wait_time=2929641
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
for 'library cache lock' count=1 wait_time=2929681
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
for 'library cache lock' count=1 wait_time=2929653
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
for 'library cache lock' count=1 wait_time=2929668
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9
for 'library cache lock' count=1 wait_time=2929666
handle address=484ee2c8, lock address=467e8dc4, 100*mode+namespace=c9

發現PROCESS 17正在waiting for 'library cache pin',handle address=484ee2c8
PROCESS 17:
----------------------------------------
SO: 0x48a1bdbc, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=17, calls cur/top: 0x48b2980c/0x48b2980c, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 0
last post received-location: No post
last process to post me: none
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: 48a17f00 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0x48a4c604
O/S info: user: oracle, term: pts/1, ospid: 7485
OSD pid info: Unix process pid: 7485, image: (TNS V1-V3)
Dump of memory from 0x48A09334 to 0x48A094B8
48A09330 00000004 483D47A8 00000010 [.....G=H....]
48A09340 0003139D 48B2980C 00000003 0003139D [.......H........]
48A09350 4807AD18 0000000B 0003139D 48AF3E48 [...H........H>.H]
48A09360 00000004 00031291 00000000 00000000 [................]
48A09370 00000000 00000000 00000000 00000000 [................]
Repeat 19 times
48A094B0 00000000 00000000 [........]
----------------------------------------
SO: 0x48af3e48, type: 4, owner: 0x48a1bdbc, flag: INIT/-/-/0x00
(session) sid: 139 trans: 0x47619440, creator: 0x48a1bdbc, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0011-00000014, short-term DID: 0000-0000-00000000
txn branch: (nil)
oct: 25, prv: 0, sql: 0x4857c470, psql: 0x484f1d68, user: 67/LIYONG
O/S info: user: oracle, term: pts/1, ospid: 7484, machine: ly
program: (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache pin' blocking sess=0x(nil) seq=37 wait_time=0 seconds since wait started=405
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
Dumping Session Wait History
for 'library cache pin' count=1 wait_time=2929663
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
for 'library cache pin' count=1 wait_time=2929672
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
for 'library cache pin' count=1 wait_time=2929658
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
for 'library cache pin' count=1 wait_time=2929662
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
for 'library cache pin' count=1 wait_time=2929660
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
for 'library cache pin' count=1 wait_time=2929657
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
for 'library cache pin' count=1 wait_time=2929674
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
for 'library cache pin' count=1 wait_time=2929653
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
for 'library cache pin' count=1 wait_time=2929677
handle address=484ee2c8, pin address=4671034c, 100*mode+namespace=12d
for 'library cache pin' count=1 wait_time=2929654

以關鍵字"484ee2c8"繼續搜尋，發現PROCESS 16正遇到一個 LIBRARY OBJECT LOCK，而且他LIBRARY OBJECT HANDLE的地址484ee2c8

LIBRARY OBJECT LOCK: lock=46775d8c handle=484ee2c8 mode=N
call pin=0x467a6f6c session pin=(nil) hpc=0000 hlc=0000
htl=0x46775dd8[0x4670e360,0x4670e360] htb=0x4670e360 ssga=0x4670dbfc
user=48b0b458 session=48b0b458 count=1 flags=PNC/[0400] savepoint=0x91
LIBRARY OBJECT HANDLE: handle=484ee2c8 mutex=0x484ee37c(0)
name=LIYONG.PINING
hash=b55f4101981a4a55bdc48998f72434d6 timestamp=06-06-2006 13:49:45
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-001d-20bf lock=X pin=S latch#=1 hpc=000e hlc=000e
lwt=0x484ee324[0x467e8de0,0x467e8de0] ltm=0x484ee32c[0x484ee32c,0x484ee32c]
pwt=0x484ee308[0x46710368,0x46710368] ptm=0x484ee310[0x484ee310,0x484ee310]
ref=0x484ee344[0x484ee344,0x484ee344] lnd=0x484ee350[0x484df22c,0x484d73a8]
LIBRARY OBJECT: object=45c6f1f8

查詢Oracle Process為16的OS Process
SQL> select spid from v$process where pid=16;

SPID
------------
7439

在作業系統級kill掉這個程式後問題解決.
[oracle@oracle oracle]$ kill -9 7439

SQL> select sid,event,p1,p1raw from v$session_wait where event like 'library%';

no rows selected.