Resolve library cache pin in oracle
'library cache lock' Waits: Causes and Solutions (Doc ID 1952395.1)
wait: library cache lock
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).
This lock is also obtained as part of the operation to locate an object in the library cache (a library cache child latch is obtained to scan a list of handles, then the lock is placed on the handle once the object has been found).
What to look for:
- TKProf:
- Overall wait event summary for non-recursive and recursive statements shows significant amount of time for library cache lock waits.
- AWR or statspack:
- Significant waits for library cache lock
Troubleshooting Steps More details in (Doc ID 1952395.1)
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
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 Justification
TKProf :
- Use the report sorted by elapsed parse time
- Look at the top statements and determine if they are being hard parsed; these will have "Misses in the library cache" equal or close to the total number of parses
- Examine the statements that are being hard parsed and look for the presence of literal values.
An X request (3) will be blocked by any pins held S mode (2) on the object.
An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.
First, we can find the session waiting on the library cache pin
set line 200 pages 200
column "USER" format a20
column "SID_SERIAL" format a15
column "EVENT" format a60 wrap
select
b.username || '('|| b.osuser || ')' "USER",
a.sid || ','|| b.serial# "SID_SERIAL",
a.event || '=>'|| a.p1text || '=' || a.p1raw || ' ' ||
a.p2text || '=' || a.p2 || ' ' ||
a.p3text || '=' || a.p3 "EVENT"
from v$session_wait a, v$session b
where a.sid = b.sid
and a.event like 'library cache pin%'
order by 3;
Now we can find database-wide library cache pin blockers using the below query
set line 200 pages 200
select /*+ all_rows */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/
Or
set line 200 pages 200
select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event like 'library cache%'
order by lock_mode_held desc
/
Or you can get the blocking using the below method also
set line 200 pages 200
select a.p1text "handle address"
from v$session_wait a, v$session b
where a.sid = b.sid
and a.event like 'library cache pin%'
order by 3
;
Use the above handle address and query the below sql
set line 200 pages 200
col "User" format a8
col "Object" format a40
select b.sid "SID", b.username "User", c.kglnaobj "Object", a.KGLPNMOD "Mode"
from x$kglpn a, v$session b, x$kglob c
where a.KGLPNUSE = b.saddr
and a.kglpnhdl = c.kglhdadr
and a.kglpnhdl = '&handladdr';
SQL given above is instance-specific, so please use this in all the instances in case of Oracle RAC