RE: Why Library Cache Pin on both sides?

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 7 Feb 2007 13:48:40 -0500
Message-ID: <AA29A27627F842409E1D18FB19CDCF270B33725B@AABO-EXCHANGE02.bos.il.pqe>

Hi Binh,  

This is my script for tracking down who is blocking who in the event of a library cache pin event:  

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
       x$kglpn pn,
       v$session ses,
       x$kglob lob,
       v$session_wait vsw

  WHERE
   pn.KGLPNUSE = ses.saddr and
   pn.KGLPNHDL = lob.KGLHDADR
   and lob.kglhdadr = vsw.p1raw
   and vsw.event = 'library cache pin'
order by lock_mode_held desc
/  

Hope that helps,  

-Mark  

--

Mark J. Bobak

Senior Oracle Architect

ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005

 

________________________________

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Binh Pham
Sent: Wednesday, February 07, 2007 12:35 PM
To: oracle-l_at_freelists.org
Subject: Why Library Cache Pin on both sides?


Why there are library cache pin on both sides when someone tries to
compile a package that are also being used by users and BOTH SIDES ARE
FROZEN.
 
I thought that if one side is currently using it then only the later one
should wait not both.
 
Thanks.

________________________________

Check out all that glitters with the MSN Entertainment Guide to the
Academy Awards(r) <http://g.msn.com/8HMAENUS/2755??PS=47575> --
http://www.freelists.org/webpage/oracle-l 

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 07 2007 - 12:48:40 CST