This discussion is archived
2 Replies Latest reply: Nov 15, 2011 5:16 AM by Joel_C RSS

Library Cache Pin Wait Event (within the context of APEX)

Joel_C Pro
Currently Being Moderated
Hello,

Firstly -

Oracle Version: 10.2.0.4.0
Apex Version: 3.0.1.00.08

Okay, my colleague (no really! This isn't one of those "Ahem ... A friend of mine has contracted something nasty +downstairs+..."-type questions) is having problems compiling a package (using TOAD incidentally, but it's the same in SQL Developer).

I've searched the forum and the web for a bit of help on what's maybe happening here and it appears to be related to a concurrency conflict with the package definition - from what I can understand it's a case of the package is in use by another session, therefore another session cannot alter it at the same time (which makes sense)

"What does this have to do with APEX?"... well, he is working on this package using the following methodology:

1. Compile the package body/spec (as necessary - body more often obviously)
2. run an apex page which uses the code in a process, which may or may not result in the error page being displayed
3. Making changes to the package body/spec

repeat steps 1-3 ad nauseum...

He is the only user directly accessing the schema (and the only user accessing the page via APEX too, although I appreciate this isn't quite the same thing).

I was wondering if, due to the architecture of APEX (the use of session pools etc), the state of a package might be being retained in some manner, thus resulting in this library cache pin wait event? If so, is there anything I can do to mitigate against this occurring?

p.s. the only difference I can see between this particular package and any other package in the schema is that this one interacts with blobs (including making references to the wwv_flow_files view) - with blobs being passed as parameters between procedures (thus potentially creating temporary blobs which may or may not being closed).

Any ideas?

p.p.s. there are also no DBMS_SCHEDULER jobs or anything that might potentially be running the code incidentally...

Edited by: Joel_C on 11-Nov-2011 11:58

We got our DBAs to run a bit of code to identify the blocking session:
 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
results as follows (I've changed some object names to protect the ignorant):
OBJECT_TYP OBJECT_NAME                   LOCK_MODE_HELD LOCK_MODE_REQUESTED        SID    SERIAL# USERNAME
---------- ------------------------- ------------------ ------------------- ---------- ---------- ---------------
PACKAGE    PKG_FOOBAR                             2                   0        356      21694 HTMLDB_PUBLIC_U
                                                                                                  SER

PACKAGE    PKG_FOOBAR                             0                   3        463      22309 FOO
HTMLDB_PUBLIC_USER is the apex user incidentally. The session is marked in the v$session table as "inactive", the last statement being
Begin
   Dbms_session.reset_package;
End;
Edited by: Joel_C on 11-Nov-2011 14:39

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points