|
|
|
Oracle
library cache pin waits tips
Oracle Consulting Tips by Burleson
|
Oracle library cache pin waits are
caused by contention with the library cache, the area used to store
SQL executables for re-use. The library cache pin Oracle
metric takes place if the process wants to pin an object in memory
in the library cache for examination, ensuring no other processes
can update the object at the same time.
The library cache pin wait
usually happens when you are compiling or parsing a PL/SQL object or
a view.
The following query provides clues
about whether Oracle has been waiting for library cache activities:
select
sid,
event,
p1raw,
seconds_in_wait,
wait_time
from
v$session_wait
where
event = 'library cache pin'
and
state = 'WAITING';
The columns of the v$session_wait view that are of
particular interest for a buffer busy wait event are:
- P1—The absolute file number for the data file involved
in the wait.
- P2—The block number within the data file referenced in
P1 that is being waited upon.
- P3—The reason code describing why the wait is
occurring.
Here's an Oracle data dictionary query for these values:
select p1 "File #", p2 "Block #",
p3 "Reason Code" from v$session_wait where
event = 'library
cache pin';
You
can then
trace and p1 value
back to the specific Oracle library cache pin wait component.
Mark Bobak published this script for locating the source of
library cache pin waits:
This is my script for
tracking down who is blocking who in the event of a library
cache pin event:
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 = 'library cache pin'
order by lock_mode_held desc
/
This script will also
locate library cache pin waits:
select
. . .
from
sys.v$system_event a,
sys.v$event_name b
where
See Code depot for complete script
b.name in ('latch free','library cache load lock',
'library cache lock','library cache pin')
group by
b.name
Output from the above script might
resemble the following library cache waits:
NAME WAITS
--------------------------------
latch free 16
library cache load lock 2
library cache lock 0
library cache pin 0
|