How does one diagnose and fix 'library cache pin' waits?

Oracle database - How does one diagnose and fix 'library cache pin' waits? posted by Babu Kunwar

<<Previous   Next>>

How does one diagnose and fix 'library cache pin' waits?

LIBRARY CACHE PIN usually occurs during compiling and recompiling PL / SQL, VIEW, TYPES. First we will determine the blocker information as:

SQL> select SID, event from v $ session_wait where event like 'library%';

Then we need to determine the SID username program which can be done as:
SQL> SELECT a.SID, a.username, a.program
FROM v $ session a, x $ kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl = '2 B9E5B50 '; Where 2 B9E5B50 is the library cache pin.

Then select object_id, session_id, locked_mode from v $ locked_object. After getting the details
Identify the associated processes, and can kill off as
Select b.username username, b.terminal terminal, b.program program, b.spid
FROM v $ session a, v $ process b
WHERE a.PADDR = b.ADDR and a.sid = '& sid';

<<Previous   Next >> 

More links

Write a PL/SQL program for a trigger.

Latest answer: PL/SQL program for tracking operation on a emp table
Create or Replace Trigger EmpTracking
Before Insert or Delete or Update on Emp...................