'library cache lock' Waits: Causes and Solutions
(Doc ID 1952395.1)
Last updated on DECEMBER 27, 2023
Applies to:
Oracle Database - Personal Edition - Version 8.1.7.4 and laterOracle 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 |