3 Replies Latest reply: Jun 6, 2007 12:53 AM by branislav.dobrotka RSS

    library cache lock

    530608
      hi all,

      Can anyone tell me what is library cache lock and why it occurs?

      Thanks in advance..............
        • 1. Re: library cache lock
          577207
          Hi,

          you can visit here
          http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm
          and
          http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm

          Adith
          • 2. Re: library cache lock
            578825
            You can use the below query to find if there are Library Cache Lock in the database.
            ----------------------------------------------
            For Library cache Lock
            ----------------------------------------------

            select saddr from v$session where sid in (select sid from v$session_wait where event like 'library cache lock');

            BLOCKER SESSION
            ----------------

            SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
            WHERE SADDR in
            (SELECT KGLLKSES FROM X$KGLLK LOCK_A
            WHERE KGLLKREQ = 0
            AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
            WHERE KGLLKSES = 'saddr_from_v$session above' /* BLOCKED SESSION */
            AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
            AND KGLLKREQ > 0)
            );

            BLOCKED SESSION
            ---------------

            SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
            WHERE SADDR in
            (SELECT KGLLKSES FROM X$KGLLK LOCK_A
            WHERE KGLLKREQ > 0
            AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
            WHERE KGLLKSES = 'saddr_from_v$session above' /* BLOCKING SESSION */
            AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
            AND KGLLKREQ = 0)
            );
            • 3. Re: library cache lock
              branislav.dobrotka
              maybe quick description
              1. situation
              library cache lock / pins is happen when object is pin in memory (executing , compile ...), because is executed and another session want to use id (compilation , grant ...)
              2. situation
              first session make long DML and later second session try DDL (ALTER TABLE)


              ... But when another people can describe or send link to better explanation .. please .. this theme is very important for DBA