Library Cache Lock、Library Cache Pin、Library Cache Load Lock等待事件

  • minxuan2013

    2014-12-30 13:37:36
  • Linux操作系统
  • 转载
        今天有个客户的RAC数据库的两个节点的AWR报告中，Library Cache Lock都排在Top 5等待事件的最前面，并且Total Call Time都在60%以上，在METALINK上找到了一篇非常棒的文章，在此分享，注意加红的文字描述。

Troubleshooting Library Cache: Lock, Pin and Load Lock [ID 444560.1]
 修改时间 09-MAR-2012     类型 TROUBLESHOOTING     状态 PUBLISHED 

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details
     What is  "Library cache lock" ?
     What is "Library cache pin"?
     Why are these two type of locks needed?
     How can Library Cache Contention be reduced in General?
     How can Library cache lock be reduced?
     How can Library cache pin be reduced?
     What is Library cache load lock?
     How can  Library cache load lock be reduced?
     How are library cache pins and library load lock related?
     SOME HINTS
     Known Bugs:

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.3 - Release: 8.1.7 to 11.2
Oracle Server - Personal Edition - Version: 8.1.7.4 to 11.2.0.3   [Release: 8.1.7 to 11.2]
Oracle Server - Standard Edition - Version: 8.1.7.4 to 11.2.0.3   [Release: 8.1.7 to 11.2]
Information in this document applies to any platform.

Purpose

Purpose

The idea behind the document is to give some brief information on Library cache lock, Library cache pin and Library cache load lock.

Scope & Application

For all DBAs 

Last Review Date

September 20, 2011

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details


  • What is  "Library cache lock" ?

    This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:

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

    This lock is also obtained to locate an object in the library cache.
    Library cache lock will be obtained on database objects referenced during parsing or compiling of SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, synonym). The lock will be released at the end of the parse or compilation.

    Cursors (SQL and PL/SQL areas), pipes and any other transient objects do not use this lock.
    Library cache lock is not deadlock sensitive and the operation is synchronous.

    Parameters:

    • handle address
      Address of the object being loaded.

    • lock address
      Address of the load lock being used. This is not the same thing as a latch or an enqueue, it is a State Object.

    • Mode
      Indicates the data pieces of the object which need to be loaded.

    • Namespace
      The name of the object namespace as it is displayed in V$DB_OBJECT_CACHE view

  • What is "Library cache pin"?

    This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock. Pin can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered as a special form. of lock. A wait for a "library cache pin" implies some other session holds that PIN in an incompatible mode.

    Library cache pin will be obtained on a database objects that is currently cached in the library cache (table, view, procedure, function, package, package body, trigger, index, cluster, synonym); in the library cache, a database object is cached in 2 parts: "handle" and "object"; only when the "object" part is cached is this lock held;

    It is not deadlock sensitive and the operation is synchronous.

    Note: In 10g and later, "library cache pins" are replaced by mutexes.
    See: Note 1298015.1 WAITEVENT: "cursor: pin S wait on X" Reference Note 


  • Why are these two type of locks needed?

    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 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.

    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 while changes are being made to the definition of the object and the object dropped 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 to prevent other sessions 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. The pin is required because it needs the dictionary information to be loaded into memory to access the same code.

    Note 34579.1 WAITEVENT: "library cache pin" Reference Note

  • How can Library Cache Contention be reduced in General?

    The following sections provide specific ways of addressing the various events that you may see as a result of contention. However, these waits or often a symptom of a general issue with cursor versions. If you see contention for any library cache events, it is worth immediately checking in an AWR report that you do not have hundreds or even thousands of versions of cursors:



  • if you do then using the following to resolve that:

    Document 296377.1 Troubleshooting: High Version Count Issues

  • How can Library cache lock be reduced?

    In general , high contention on library cache lock is usually a result of an under-sized shared pool or non-sharing of sql. Some ways of reducing the contention are:

    • Reduce the reloads by increasing the shared pool size as the locks may take a long time if the pool is undersized.

    • Increase sharing by setting the cursor_sharing to similar or force.
      Be aware this may change the execution plan; so setting the parameter should be thoroughly tested.

    • Reduce the invalidations by running the batch jobs to collect statistics or any other maintenance jobs separately from OLTP.


    Note 122793.1 How to Find which Session is Holding a Particular Library Cache Lock


  • How can Library cache pin be reduced?

    If "library cache pin" waits form. a significant amount of the wait time then it is important to determine if this is one or two sessions waiting long periods of time or a more serious general contention issue among lots of processes:

    • If there is a blocking scenario where one or two processes are being blocked by one process, the reason for the process not releasing the pin needs to be determined

    • If there is general widespread waiting then the shared pool may need tuning


      Note 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention

  • What is Library cache load lock?

    The session tries to find the load lock for the database object so that it can load the object.

    The load lock is always obtained in exclusive mode, so that no other process can load the same object.If the load lock is busy the session will wait on this event until the lock becomes available.

    Wait Time: 3 seconds (1 second for PMON)

    Parameters:

    • object address 
      Address of the object being loaded.
    • lock address
      Address of load lock being used

  • How can  Library cache load lock be reduced?

    If an object is not in memory, then a library cache lock cannot be acquired on it.
    The object has to be loaded into the memory to acquire the lock.
    The session tries to find the load lock for the database object so that it can load the object.
    In order to prevent multiple processes requesting the load of the same object simultaneously, the other requesting sessions have to wait for the library cache load lock as the lock is busy with loading the object into the memory.

    The waits on the library cache load lock is due to the objects not being available in memory.
    The unavailability of the library cache object in the library cache is due to the undersized shared pool causing frequent reloads or too many hard parse as a result of unshared sqls.

    To avoid this, the general recommendation would be:

    • Increase the shared pool ( to avoid high reloads)
    • Increase the session cached cursors (to avoid the cursors flushing out of shared pool)
    • Set cursor_sharing to force (to reduce hard parsing)--again this may change the plan and performance of query. This will change literals to use binds; so plan may change:

    • Note:94036.1 Init.ora Parameter "CURSOR_SHARING" Reference Note

  • How are library cache pins and library load lock related?

    Library cache pins and load locks may occur during PL/SQL, views, types, etc. compilation or recompilation. The compilation is always explicit (application installation, upgrades, applying patches),but object recompilation may happen transparently due to object invalidations.

    Dealing with slow downs related to "mysterious" library cache pins and load locks we should look for the reason of the database object invalidations. They are likely to be triggered by actions causing changes to "LAST_DDL" attribute of database objects that have other dependent ones. Typically they are the object maintenance operations - ALTER, GRANT, REVOKE, replacing views, etc. Also gathering optimizer statistics will invalidate the cursor, causing reload in the library cache.  This behavior. is described in Oracle Server Application Developer's Guide as object dependency maintenance.

    After object invalidation, Oracle tries to recompile the object at the time of the first access to it. It may be a problem when other sessions have pinned the object in the library cache. It is obvious that it is more likely to occur with more active users and with more complex dependencies (eg. many cross-dependent packages or package bodies). In some cases waiting for object recompilation may even take hours blocking all the sessions trying to access it. 



    This may be seen in library cache dump, level 10:
    Look for ALTER ... COMPILE statement and objects/handles with lock=X or pin=X.

  • SOME HINTS

    • Be very careful with altering, granting or revoking privileges on database objects that frequently used stored PL/SQL is dependent on. In fact, resolving this issue mostly depends on application project and system maintenance practices. Application developers should also consider that some project decisions have negative impact to the application scalability and performance.

    • The load lock is obtained in EXCLUSIVE mode. 
      If the load lock is busy the session will wait on this event until the lock becomes available. 

Known Bugs:



Note:10018789.8Spin in kgllock / DB hang with high library cache lock waits 
Note:7423411.8Process may hang waiting for "library cache load lock" with no holder 
Note:7706138.8Process may hang waiting for "library cache load lock" with no holder 
Note:9675816.8Bug 9675816 - Self deadlock with 'library cache lock' waits

下面对Library Cache Lock和Library Cache pin做如下补充：

1.参考文章：
http://space6212.itpub.net/post/12157/220673

2.Shared Pool中，主要组件占用空间情况如下：
select *
  from (select pool, name, round(bytes / 1024 / 1024, 1) mb
          from V$SGASTAT
         where pool = 'shared pool'
         order by bytes desc)
 where rownum <= 10
union all
select pool, name, round(bytes / 1024 / 1024, 1) mb
  from v$sgastat
 WHERE name = 'row cache';

shared pool sql area        2793.4
shared pool CCursor                1230.9
shared pool PCursor                1025.2
shared pool free memory        909.4
shared pool library cache        620.5
shared pool gcs resources        179.5
shared pool Cursor Stats        118.6
shared pool gcs shadows        113.1
shared pool kglsim object batch 105.3
shared pool db_block_hash_buckets 90
shared pool row cache       7.2
        从上面的输出可以看出，最大的是sql area，library cache保存的应该是数据字典缓存信息。


参考文章：
        http://www.itpub.net/thread-1801066-1-1.html
        http://space.itpub.net/35489/viewspace-664252
        http://www.dbsnake.net/library-cache-lock-and-pin.html
        http://www.dbsnake.net/library-cache-pin-and-lock-continue.html
        http://blog.csdn.net/tianlesoftware/article/details/6560956
        http://blog.csdn.net/tianlesoftware/article/details/6629869

--end--


Version_count.jpg

请使用浏览器的分享功能分享到微信等