My Oracle Support Banner

'library cache lock' Waits: Causes and Solutions (Doc ID 1952395.1)

Last updated on NOVEMBER 07, 2019

Applies to:

Oracle Database - Personal Edition - Version 8.1.7.4 and later
Oracle Database - Standard Edition - Version 8.1.7.4 and later
Oracle Database - Enterprise Edition - Version 8.1.7.4 and later
Information in this document applies to any platform.

Purpose

 Troubleshoot waits for 'library cache lock'.

NOTE: The information in this article is taken from the Oracle Performacne Diagnostic Guide (OPDG):

<Document 390374.1> Oracle Performance Diagnostic Guide (OPDG)

This article also contains similar diagnostics for other wait events.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Troubleshooting Steps
 wait: library cache lock
 Cause Identified: Unshared SQL Due to Literals
 Solution Identified: Rewrite the SQL to use bind values
 Solution Identified: Use the CURSOR_SHARING initialization parameter
 Cause Identified: Shared SQL being aged out
 Solution Identified: Increase the size of the shared pool
 Solution Identified: 10g+: Use the Automatic Shared Memory Manager (ASMM) to adjust the shared pool size
 Solution Identified: Keep ("pin") frequently used large PL/SQL and cursor objects in the shared pool
 Cause Identified: Library cache object Invalidations
 Solution Identified: Do not perform DDL operations during busy periods
 Solution Identified: Do not collect optimizer statistics during busy periods
 Solution Identified: Do not perform TRUNCATE operations during busy periods
 Cause Identified: Objects being compiled across sessions
 Solution Identified: Avoid compiling objects in different sessions at the same time or during busy times
 Cause Identified: Auditing is turned on
 Solution Identified: Evaluate the need to audit
 Cause Identified: Unshared SQL in a RAC environment
 Solution Identified: Rewrite the SQL to use bind values
 Solution Identified: Use the CURSOR_SHARING initialization parameter
 Cause Identified: Extensive use of row level triggers
 Solution Identified: Evaluate the need for the row trigger
 Cause Identified: Excessive Amount of Child Cursors
 Solution Identified: Inappropriate use of parameter CURSOR_SHARING set to SIMILAR
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.