1 Reply Latest reply on Apr 5, 2010 10:13 AM by Pavan Kumar

    Library cache lock wait event


      I am experience hanging in my database. I am using on Solaris 5.10.
      I am having automatic processes that drop the schema and recreate the schema and upload the new data. Since the last few days i am facing in hanging while dropping the schema. I checked and found "Library cache lock" event that caused other processes also in the hanged state. I am trying to find out the reason but not able to crack.

      Can some one advice me?

        • 1. Re: Library cache lock wait event
          Pavan Kumar

          There exists an concurrency problem with respect to the object - resource utilization.

          - One client can prevent other clients from accessing the same object
          - The client can maintain a dependency for a long time (for example, no other client can change the object)

          check the from v$session_wait (query taken from the http://www.dba-oracle.com/m_library_cache_pin.htm - for your reference)

          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',
          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

          Resolution might - you need to check the time it takes (the script to drop the schema) and it loads - either doing from parallel sessions or in serial order. I doubt it, till the schema get dropped completely it should not be accessible to any other applications - either objects too, that makes the rise to concurrency Issue with respect to library cache waits - the other sessions are trying access the object.

          - Pavan Kumar N
          - ORACLE 9i/10g - OCP