RE: library cache lock

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 5 Mar 2004 12:32:15 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC42@bosmail00.bos.il.pqe>

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_at_APPL.GE.COM] Sent: Friday, March 05, 2004 9:14 AM
To: 'oracle-l_at_freelists.org'
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_at_appl.ge.com 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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Mar 05 2004 - 11:59:04 CST