RE: library cache lock

From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 5 Mar 2004 12:32:15 -0500
Hi Matt,
 
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@xxxxxxxxxxx]
Sent: Friday, March 05, 2004 9:14 AM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxx 
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
RE: library cache lock

Other related posts:
