Ok, this was fun! I actually started to dig into this a while ago, and it sort of fell by the wayside, and I didn 't really get anywhere. Now I 've got a workable solution.
First, a simple testcase:
Open three sessions to a database.
In the first session, create the following procedure:
create or replace procedure selflock
as
begin
execute immediate 'alter procedure selflock compile ';
end;
/
Now, in the first session, do: exec selflock
This will hang. If you look at v$session_wait, you 'll see it 's waiting on a library cache pin.
Now, in the second session, do: alter procedure selflock compile;
This also will hang, this time on library cache lock.
Now, if you execute the following in the third session, you 'll see session that 's blocking the library cache lock:
select decode(lob.kglobtyp, 0, 'NEXT OBJECT ', 1, 'INDEX ', 2, 'TABLE ', 3, 'CLUSTER ',
4, 'VIEW ', 5, 'SYNONYM ', 6, 'SEQUENCE ',
7, 'PROCEDURE ', 8, 'FUNCTION ', 9, 'PACKAGE ',
11, 'PACKAGE BODY ', 12, 'TRIGGER ',
13, 'TYPE ', 14, 'TYPE BODY ',
19, 'TABLE PARTITION ', 20, 'INDEX PARTITION ', 21, 'LOB ',
22, 'LIBRARY ', 23, 'DIRECTORY ', 24, 'QUEUE ',
28, 'JAVA SOURCE ', 29, 'JAVA CLASS ', 30, 'JAVA RESOURCE ',
32, 'INDEXTYPE ', 33, 'OPERATOR ',
34, 'TABLE SUBPARTITION ', 35, 'INDEX SUBPARTITION ',
40, 'LOB PARTITION ', 41, 'LOB SUBPARTITION ',
42, 'MATERIALIZED VIEW ',
43, 'DIMENSION ',
44, 'CONTEXT ', 46, 'RULE SET ', 47, 'RESOURCE PLAN ',
48, 'CONSUMER GROUP ',
51, 'SUBSCRIPTION ', 52, 'LOCATION ',
55, 'XML SCHEMA ', 56, 'JAVA DATA ',
57, 'SECURITY PROFILE ', 59, 'RULE ',
62, 'EVALUATION CONTEXT ',
'UNDEFINED ') object_type,
lob.kglnaobj object_name,
pn.kglpnmod lock_mode_held,
pn.kglpnreq lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
from v$session_wait vsw,
x$kglob lob,
x$kglpn pn,
v$session ses
where vsw.event = 'library cache lock '
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr
/
So, yeah, that was fun! ;-) Yes, I clearly have a demented sense of fun.....;-)
-Mark
-- --Original Message-- --
From: Adams, Matthew (GE Consumer & Industrial) [mailto:MATT.ADAMS@(protected)]
Sent: Friday, March 05, 2004 9:14 AM
To: 'oracle-l@(protected) '
Subject: library cache lock
Oracle 9.2.0 on hp-ux 11
An attempt to truncate a table is hanging for a very long
time.
The processes is waiting over and over for 'library cache lock ' for
the same p1 and p2 (handle address and lock address respectively).
Shouldn 't the P2 value (lock_address) map to a KADDR in v$lock?
----
Matt Adams - GE Appliances - matt.adams@(protected)
Don 't ask a man what kind of computer he uses.
If it 's a Macintosh, he 'll tell you.
If not, why embarass him? -- Tom Clancy
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD >
<META HTTP-EQUIV= "Content-Type " CONTENT= "text/html; charset=iso-8859-1 " >
<TITLE >library cache lock </TITLE >
<META content= "MSHTML 6.00.2713.1100 " name=GENERATOR > </HEAD >
<BODY >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >Hi
Matt, </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >Ok,
this was fun! I actually started to dig into this a while ago, and it sort
of fell by the wayside, and I didn 't really get anywhere. Now I 've got a
workable solution. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >First,
a simple testcase: </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >Open
three sessions to a database. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >In the
first session, create the following procedure: </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >create
or replace procedure selflock <BR > as <BR > begin <BR >
execute immediate 'alter procedure selflock compile '; <BR > end; <BR >/ <BR >Now,
in the first session, do: exec selflock </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >This
will hang. If you look at v$session_wait, you 'll see it 's waiting on a
library cache pin. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >Now,
in the second session, do: alter procedure selflock compile; </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >This
also will hang, this time on library cache lock. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >Now,
if you execute the following in the third session, you 'll see session that 's
blocking the library cache lock: </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >select
decode(lob.kglobtyp, 0, 'NEXT OBJECT ', 1, 'INDEX ', 2, 'TABLE ', 3,
'CLUSTER ', <BR >
4, 'VIEW ', 5, 'SYNONYM ', 6,
'SEQUENCE ', <BR >
7, 'PROCEDURE ', 8, 'FUNCTION ', 9,
'PACKAGE ', <BR >
11, 'PACKAGE BODY ', 12,
'TRIGGER ', <BR >
13, 'TYPE ', 14, 'TYPE
BODY ', <BR >
19, 'TABLE PARTITION ', 20, 'INDEX PARTITION ', 21,
'LOB ', <BR >
22, 'LIBRARY ', 23, 'DIRECTORY ', 24,
'QUEUE ', <BR >
28, 'JAVA SOURCE ', 29, 'JAVA CLASS ', 30, 'JAVA
RESOURCE ', <BR >
32, 'INDEXTYPE ', 33,
'OPERATOR ', <BR >
34, 'TABLE SUBPARTITION ', 35, 'INDEX
SUBPARTITION ', <BR >
40, 'LOB PARTITION ', 41, 'LOB
SUBPARTITION ', <BR >
42, 'MATERIALIZED
VIEW ', <BR >
43,
'DIMENSION ', <BR >
44, 'CONTEXT ', 46, 'RULE SET ', 47, 'RESOURCE
PLAN ', <BR >
48, 'CONSUMER
GROUP ', <BR >
51, 'SUBSCRIPTION ', 52,
'LOCATION ', <BR >
55, 'XML SCHEMA ', 56, 'JAVA
DATA ', <BR >
57, 'SECURITY PROFILE ', 59,
'RULE ', <BR >
62, 'EVALUATION
CONTEXT ', <BR >
'UNDEFINED ') object_type, <BR > lob.kglnaobj
object_name, <BR > pn.kglpnmod
lock_mode_held, <BR > pn.kglpnreq
lock_mode_requested, <BR >
ses.sid, <BR >
ses.serial#, <BR > ses.username <BR > from
v$session_wait vsw, <BR > x$kglob
lob, <BR > x$kglpn
pn, <BR > v$session ses <BR > where
vsw.event = 'library cache lock ' <BR > and vsw.p1raw =
lob.kglhdadr <BR > and lob.kglhdadr = pn.kglpnhdl <BR > and
pn.kglpnmod != 0 <BR > and pn.kglpnuse =
ses.saddr <BR >/ </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff size=2 >So,
yeah, that was fun! ;-) Yes, I clearly have a demented sense of
fun.....;-) </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=870333316-05032004 > <FONT face=Arial color=#0000ff
size=2 >-Mark </FONT > </SPAN > </DIV >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Original Message-- -- <BR > <B >From: </B > Adams, Matthew (GE Consumer
& Industrial) [mailto:MATT.ADAMS@(protected)] <BR > <B >Sent: </B > Friday,
March 05, 2004 9:14 AM <BR > <B >To: </B >
'oracle-l@(protected) ' <BR > <B >Subject: </B > library cache
lock <BR > <BR > </FONT > </DIV >
<P > <FONT size=2 >Oracle 9.2.0 on hp-ux 11 </FONT > </P >
<P > <FONT size=2 >An attempt to truncate a table is hanging for a very
long </FONT > <BR > <FONT size=2 >time. </FONT > </P >
<P > <FONT size=2 >The processes is waiting over and over for 'library cache
lock ' for </FONT > <BR > <FONT size=2 >the same p1 and p2 (handle address and lock
address respectively). </FONT > </P >
<P > <FONT size=2 >Shouldn 't the P2 value (lock_address) map to a KADDR in
v$lock? </FONT > </P > <BR >
<P > <FONT size=2 >---- </FONT > <BR > <FONT size=2 >Matt Adams - GE Appliances -
matt.adams@(protected) </FONT > <BR > <FONT size=2 >Don 't ask a man what kind of
computer he uses. </FONT > <BR > <FONT size=2 >If it 's a Macintosh, he 'll tell you.
</FONT > <BR > <FONT size=2 >If not, why embarass him? -- Tom Clancy </FONT >
</P > </BLOCKQUOTE > </BODY > </HTML >
