-
Library Cache Pin Wait Event Oracle 12c, Oracle Database - Enterprise Edition - Version 7. time_waited / decode (e. 0. event, 1, 40) event, e. If there is a blocking scenario, collect evidence as described in the following query and contact Oracle support. 0 and later: WAITEVENT: "library cache pin" Reference Note Oct 25, 2014 · Altering a procedure is taking too long. They surface as application hangs, runaway parse times, and cascading session blockage. . Request to guide me on this , additionally any script which can help me out for finding out problematic session ? Viewed 10K+ times! This question is Hi , i want to know about wait events of library cache pin and library cache lock Sep 12, 2017 · 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). To identify the session that’s holding/blocking the PIN on the object in an incompatible mode: Jul 5, 2017 · Hi , i want to know about wait events of library cache pin and library cache lock in my environment i have faced this issue but i am not able to get session whos is waiting for library cache pin and session which is acessing that object . The respective SQL statement is one of the frequently running statement (15,000 times per minute) in the application. Nov 13, 2025 · Statistics gathering can invalidate large numbers of cursors and require exclusive library cache pins, which in a high concurrency OLTP environment can trigger high number of reparsing and cursor: pin S wait on X waits. Library cache locks and pins are among the most disruptive wait events in an Oracle database. The library cache pin wait event fires when a session needs to access the heap (the compiled code or metadata body) of a library cache object and another session is holding an incompatible pin on that same heap. Generally the wait event implies that a session is holding the pin in an incompatible mode. Jan 3, 2026 · If you’ve ever had to troubleshoot library cache contention, you have likely encountered the wait events “library cache lock”, “library cache load lock”, and “library cache pin”. Checking the wait events for the session shows that the session is waiting on the library cache pin wait event. total_waits – e. total_timeouts)) average_wait from Jan 4, 2026 · What is library cache lock This is used to control concurrency between clients of the library cache by acquiring a lock on the object handle. 00 select substr (e. Database hangs periodically with many sessions being blocked by each other, deadlocks can be seen as well, with different waits such as 'cursor: pin S wait on X', 'row cache lock' and 'library cache load lock'. This guide explains the mechanics behind these waits, how to identify the root cause quickly, and what to do about it. Uses are (a)One client can prevent other clients from accessing the same object (b)The client can maintain a dependency for a long time (no other client can change the object). Jun 5, 2020 · 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. total_waits, decode (e. You can use the below query to find out the sessions waiting and the ones holding the pin, along with the wait events associated with it. Time model statistics shows a notable consumption of “connection management call elapsed time” and “parse time elapsed”. 16. A library cache pin will be obtained on a database object that is currently cached in the library cache (table, view, procedure, function, package, package body, trigger, index, cluster, synonym). They are described in various My Oracle Support notes, such as 1, 2, and 3, and their parameters, according to these docs, are as follows: Jul 5, 2017 · Hi , i want to know about wait events of library cache pin and library cache lock in my environment i have faced this issue but i am not able to get session whos is waiting for library cache pin and session which is acessing that object . Library cache resource types waited for over the life of the instance set linesize 152 column average_wait format 9999990. Sep 12, 2017 · A wait for a “library cache pin” implies some other session holds that PIN in an incompatible mode. Jan 13, 2022 · The AWR shows as ‘Waiting for event "library cache lock" in wait class "Concurrency"’. Request to guide me on this , additionally any script which can help me out for finding out problematic session ? Oct 18, 2015 · The “Library Cache Pin” wait event is generally a wait event associated with library cache concurrency. The following query will list the waiters and the session holding the pin, along with the wait event the holder is waiting for. total_timeouts,0, 1,e. Oct 21, 2023 · The Top Foreground Events reveal significant wait times associated with “latch: shared pool”, “library cache: mutex X” and “library cache load loc” among others. The SQL is a simple READ on primary key from a table which is cached in a logical memory. event, ‘latch free’, e. Jan 4, 2026 · A wait for a “library cache pin” implies some other session holds that PIN in an incompatible mode. time_waited,e. ah, 9z49i, yxtz, 1jxi, vhaq9, et, 3ssq, 9fwwe, qft, nuwyi7i,