The library cache lock controls the concurrency between clients of the library cache by acquiring 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 (no other client can change the object).

This lock is also obtained as part of the operation to locate an object in the library cache (a library cache child latch is obtained to scan a list of handles, then the lock is placed on the handle once the object has been found).

There can be several causes for library cache lock, some of the common ones are discussed below along with the quick solutions:-

• Cause #1: Unshared SQL Due to Literals
  • Solution: Rewrite the SQL to use bind values
  • Solution: Use the CURSOR_SHARING initialization parameter
• Cause #2: Shared SQL being aged out
  • Solution: Increase the size of the shared pool
  • Solution: Use the Automatic Shared Memory Manager (ASMM) to adjust the shared pool size
  • Solution: Keep (“pin”) frequently used large PL/SQL and cursor objects in the shared pool
• Cause #3: Library cache object Invalidations
  • Solution: Do not perform DDL operations during busy periods
  • Solution: Do not collect optimizer statistics during busy periods
  • Solution: Do not perform TRUNCATE operations during busy periods
• Cause #4: Objects being compiled across sessions
  • Solution: Avoid compiling objects in different sessions at the same time or during busy times
• Cause #5: Auditing is turned on
  • Solution: Evaluate the need to audit
• Cause #6: Unshared SQL in a RAC environment
  • Solution: Rewrite the SQL to use bind values
  • Solution: Use the CURSOR_SHARING initialization parameter
• Cause #7: Extensive use of row level triggers
  • Solution: Evaluate the need for the row trigger
• Cause #8: Excessive Amount of Child Cursors
  • Solution: Inappropriate use of parameter CURSOR_SHARING