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

Cause Identified: Unshared SQL Due to Literals

SQL statements are using literal values where a bind value could have been used. The literal values cause the statement to be unshared and will force a hard parse.

Cause Identified: Shared SQL being aged out

The shared pool is too small and is causing many statements that could be shared to age out of the library cache and later reloaded. Each reload requires a hard parse and impacts the CPU and latches.

Cause Identified: Library cache object Invalidations

When objects (like tables or views) are altered via DDL or collecting statistics, the cursors that depend on them are invalidated. This will cause the cursor to be hard parsed when it is executed again and will impact CPU and latches.

Cause Identified: Objects being compiled across sessions

One or more sessions are compiling objects (typically PL/SQL) while another session wants to pin the same object prior to executing or compiling it. One or more sessions will wait on library cache pin in Share mode (if it just wants to execute it) or eXclusive mode (if it want to compile/change the object).

Cause Identified: Auditing is turned on

Auditing will increase the need to acquire library cache locks and potentially increase contention for them. This is especially true in a RAC environment where the library cache locks become database-wide (across all instances).

Cause Identified: Unshared SQL in a RAC environment

Library cache locks waits may occur in RAC environments when applications are not sharing SQL. In single-instance environments, library cache and shared pool latch contention is typically the symptom for unshared SQL. However, in RAC, the main symptom may be library cache lock contention.

Cause Identified: Extensive use of row level triggers

When row level triggers are fired frequently, higher than usual library cache activity may occur, because of the need to check if mutating tables are being read. During trigger execution, it is possible that the application tries to read mutating tables, i.e., tables that are in the process of being modified by the statement that caused the trigger to fire. As this may lead to inconsistencies, it is not allowed, and the application should receive the error ORA-4091. The mechanism to detect this error involves one library cache lock acquisition per table referenced in each select statement executed.
​
The extent of the problem depends on how many times the row triggers fire rather than on the number of row triggers have been created (i.e., one trigger that fires 10000 times will cause more problems than 100 triggers that fire once).

Cause Identified: Excessive Amount of Child Cursors

A large number of child cursors are being created for some SQL statements. This activity is causing contention among various sessions that are creating child cursors concurrently or with other sessions that also need similar resources (latches and mutexes).
​
Cause Justification
​
AWR / Statspack reports; look in the "SQL ordered by Version Count" section. If there are any SQL statements with more than 500 versions, then this problem is likely to be occurring. Alternatively, you can query V$SQLAREA to look for any SQL with version_count greater than 500.
​
Query V$SQL_SHARED_CURSOR to see the reasons why SQL isn't being shared.