Thursday, February 26, 2009

library cache lock

Most of the info discussed here are from Metalink Note 444560.1.
The library cache lock event is also associated with library cache concurrency.
A session must acquire a library cache lock on an object handle to prevent other sessions from accessing it at the same time,
or to maintain a dependency for a long time, or to locate an object in the library cache.
Both locks and pins are provided to access objects in the library cache.

Locks manage concurrency between processes, whereas pins manage cache coherence. In order to access an object, a process must first lock the object handle, and then pin the object heap itself.
Requests for both locks and pins will wait until granted. This is a possible source of contention, because there is no NOWAIT request mode.

By acquiring a lock on the object handle, a process can prevent other processes from accessing the object, or even finding out what type it is.
It can even maintain a dependency on an object without preventing other processes from accessing the object.
Acquiring a lock is also the only way to locate an object in cache—a process locates and locks an object in a single operation.

If the process wants to actually examine or modify the object, then it must acquire a pin on the object itself (after acquiring a lock on the handle).
Pinning the object causes information about the object and its heaps to be loaded into memory if they were not already there.
This information is guaranteed to remain in memory at least until the pin is released. Locks and pins are externalized in X$KGLLK and X$KGLPN, respectively.

Oracle needs Library Cache Lock and Library Cache Pin for the compilation/Parsing of Package/Procedure/Function/View.
This is to ensure that no one is using the object during compilation/parsing as we are making some changes to the definition of the object
and need to drop and recreate the object with the new definition.

When a SQL statement is hard parsed by a session, the session has to acquire a library cache lock so that the other sessions can be prevented from accessing or modifying the same object.
If this event accumulates a lot of time, then it may indicate that the shared pool is too small or is being flushed regularly.
Otherwise, it indicates that database object definitions are being changed regularly.
Apart from hard parsing, if the session wants to change the definition of the object specified in the SQL or do any modifications,
then it has to acquire a library cache lock along with the library cache pin.
It is pinning because it needs the dictionary information to be loaded in the memory to access the same to modify/change the object.

You may use X$KGLLK to find the session which holds the lock:
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 = 'C0000006D1AFE550' --saddr of blocked session from v$session
                                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
                                 AND KGLLKREQ > 0));

No comments:

Post a Comment