2 Replies Latest reply on Nov 15, 2011 1:16 PM by Joel_C

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


      Firstly -

      Oracle Version:
      Apex Version:

      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:
               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,
             x$kglpn pn,
             v$session ses,
             x$kglob lob,
             v$session_wait vsw
         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):
      ---------- ------------------------- ------------------ ------------------- ---------- ---------- ---------------
      PACKAGE    PKG_FOOBAR                             2                   0        356      21694 HTMLDB_PUBLIC_U
      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
      Edited by: Joel_C on 11-Nov-2011 14:39