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

    Library cache lock wait event

    565226
      Hi,

      I am experience hanging in my database. I am using 10.2.0.3 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?

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

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

          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
          http://www.oracleinternals.blogspot.com