“library cache pin” wait event in Oracle database and useful queries

The “library cache pin” wait event in an Oracle database occurs when a session is waiting to acquire a shared lock on an object in the library cache.

The library cache is a memory area within the Oracle database that stores the parsed representations of SQL statements and PL/SQL programs. When a session needs to execute a SQL statement or PL/SQL program, it first checks the library cache to see if the parsed representation of that statement or program is already available. If it is, the session can reuse the parsed representation and avoid the overhead of parsing the statement or program again.

The library cache pin wait event occurs when multiple sessions are trying to access the same parsed representation of a SQL statement or PL/SQL program at the same time. One session may be trying to execute the statement or program while another session is trying to modify or invalidate the parsed representation. In this case, the sessions will wait for the shared lock on the object in the library cache to be released before they can continue.

The library cache pin wait event can occur for a variety of reasons, including:

High concurrency: If there are a large number of concurrent sessions trying to access the same parsed representation of a statement or program, it can lead to a high number of library cache pin waits.
Stale statistics: If the statistics on a table or index used in a SQL statement or PL/SQL program are stale, the optimizer may choose a suboptimal execution plan, resulting in increased contention for the parsed representation of the statement or program.
Misuse of Bind Variables: If a SQL statement is written with literals instead of bind variables, it will generate a separate parsed representation for each different literal value, which can lead to increased contention in the library cache.

To prevent library cache pin wait events, there are several steps that can be taken:

Monitor the library cache pin wait event: By monitoring the library cache pin wait event, you can identify which statements or programs are causing the most contention and take steps to address the issue.
Tune the SQL statements and PL/SQL programs: By reviewing the SQL statements and PL/SQL programs that are causing the most contention and optimizing them, you can reduce the number of library cache pin waits.
Gather optimizer statistics: By regularly gathering optimizer statistics on the tables and indexes used in your SQL statements and PL/SQL programs, you can ensure that the optimizer is making informed decisions about the execution plan.
Use bind variables: By using bind variables instead of literals in your SQL statements, you can ensure that the same parsed representation is reused for different values, which reduces the contention in the library cache.
Increase the shared pool size: Increasing the size of the shared pool can help to reduce the contention in the library cache by providing more space for parsed representations of statements and programs.

Few handy SQL queries that can be used to troubleshoot the “library cache pin” wait event in an Oracle database are below:

To find the top SQL statements that are causing the most library cache pin waits:

SELECT sql_text, count(*)
FROM v$session s, v$session_wait sw, v$sqltext t
WHERE s.sid = sw.sid
AND s.sql_hash_value = t.hash_value
AND event = 'library cache pin'
GROUP BY sql_text
ORDER BY count(*) DESC;

This query will return the SQL text and the number of times it caused a library cache pin wait event.

To identify the top objects causing library cache pin waits:

SELECT namespace, count(*)
FROM v$librarycache
WHERE namespace IN ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER')
GROUP BY namespace
ORDER BY count(*) DESC;

This query will return the object types and the number of times they caused a library cache pin wait event.

To identify the top modules causing library cache pin waits:

SELECT module, count(*)
FROM v$session s, v$session_wait sw
WHERE s.sid = sw.sid
AND event = 'library cache pin'
GROUP BY module
ORDER BY count(*) DESC;

This query will return the module names and the number of times they caused a library cache pin wait event.

To find the library cache pin wait duration:

SELECT time_waited_micro, sql_text
FROM v$session_wait
WHERE event = 'library cache pin';

This query will return the wait time of library cache pin event for all the sessions and the sql_text for which the event occurred.

To find the library cache pin waits grouped by session

SELECT s.sid, s.username, s.status, sw.event, sw.time_waited_micro
FROM v$session s, v$session_wait sw
WHERE s.sid = sw.sid
AND sw.event = 'library cache pin'
ORDER BY sw.time_waited_micro DESC;

To find the top objects causing library cache pin waits grouped by session:

SELECT s.sid, s.username, l.namespace, count(*)
FROM v$session s, v$librarycache l, v$session_wait sw
WHERE s.sid = sw.sid
AND sw.event = 'library cache pin'
AND l.hash_value = s.sql_hash_value
GROUP BY s.sid, s.username, l.namespace
ORDER BY count(*) DESC;

To find the library cache pin waits grouped by SQL statement:

SELECT sql_text, count(*)
FROM v$session s, v$session_wait sw, v$sqltext t
WHERE s.sid = sw.sid
AND s.sql_hash_value = t.hash_value
AND event = 'library cache pin'
GROUP BY sql_text
ORDER BY count(*) DESC;

To find the library cache pin waits grouped by module:

SELECT module, count(*)
FROM v$session s, v$session_wait sw
WHERE s.sid = sw.sid
AND event = 'library cache pin'
GROUP BY module
ORDER BY count(*) DESC;

To find the library cache pin waits grouped by program:

SELECT program, count(*)
FROM v$session s, v$session_wait sw
WHERE s.sid = sw.sid
AND event = 'library cache pin'
GROUP BY program
ORDER BY count(*) DESC;

To find the library cache pin waits grouped by event:

SELECT event, count(*)
FROM v$session s, v$session_wait sw
WHERE s.sid = sw.sid
GROUP BY event
ORDER BY count(*) DESC;

Reference:

How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)
WAITEVENT: “library cache pin” Reference Note (Doc ID 34579.1)
How to Find the Blocker of the ‘library cache pin’ in a RAC environment? (Doc ID 780514.1)

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

“library cache pin” wait event in Oracle database and useful queries

Related posts: