Post date: 06-Jun-2012 21:43:02
Note:
Library Cache contention is a serious issue. In most cases it would be good to analyze what is holding the library cache lock and killing it will resolve the issue. Library cache events can even bring the database to a hang state. It’s a good idea to identify and kill appropriately as early as possible. But do not kill any mandatory processes or sessions as it may lead to an outage. Contact Oracle support for critical issues.
Library cache resource types waited for over the life of the instance
=======================================================================
set linesize 152
column average_wait format 9999990.00
select substr(e.event, 1, 40) event,
e.time_waited,
e.time_waited / decode(
e.event,
'latch free', e.total_waits,
decode(e.total_waits - e.total_timeouts,0, 1,e.total_waits - e.total_timeouts)) average_wait
from sys.v$system_event e,
sys.v$instance i
where e.event like '%library cache%';
Detect sessions waiting for a Library Cache Locks
=======================================================================
select sid Waiter, p1raw,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like '%library cache%';
Sessions waiting for lib cache in RAC
select a.sid Waiter,b.SERIAL#,a.event,a.p1raw,
substr(rawtohex(a.p1),1,30) Handle,
substr(rawtohex(a.p2),1,30) Pin_addr
from v$session_wait a,v$session b where a.sid=b.sid
and a.wait_time=0 and a.event like 'library cache%';
Objects locked by Library Cache based on sessions detected above
=======================================================================
select to_char(SESSION_ID,'999') sid ,
substr(LOCK_TYPE,1,30) Type,
substr(lock_id1,1,23) Object_Name,
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
lock_id2 Lock_addr
from dba_lock_internal
where
mode_requested<>'None'
and mode_requested<>mode_held
and session_id in ( select sid
from v$session_wait where wait_time=0
and event like '%library cache%') ;
Detect Library Cache holders that sessions are waiting for
=======================================================================
select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req
from x$kglpn , v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like '%library cache%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse ;
Sessions holding the lib cache in RAC
=======================================================================
select a.sid Holder ,a.SERIAL#,b.INST_ID,b.KGLPNUSE Sesion , b.KGLPNMOD Held, b.KGLPNREQ Req
from x$kglpn b , v$session a
where b.KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache%')
and b.KGLPNMOD <> 0
and a.saddr=b.kglpnuse ;
What are the holders waiting for?
=======================================================================
select sid,substr(event,1,30),wait_time
from v$session_wait
where sid in (select sid from x$kglpn , v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse );
Note:
Sometimes using the library query below to identify the holding sessions can cause temp tablespace to run out of space….
ORA-01652: unable to extend temp segment by 256 in tablespace TEMP
Current SQL statement for this session:
select to_char(SESSION_ID,'999') sid ,
substr(LOCK_TYPE,1,30) Type,
substr(lock_id1,1,23) Object_Name,
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
lock_id2 Lock_addr
from dba_lock_internal
where
mode_requested<>'None'
and mode_requested<>mode_held
and session_id in ( select sid
from v$session_wait where wait_time=0
and event like 'library cache%')
Solution: Henceforth, Please use queries using x$kglob or x$kgllk or x$kglpn.. or tweak the following sql ( picked up from metalink) to gather lib cache lock event details
ReWritten SQL
select /*+ ordered */ 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;
Library cache pin sessions
=======================================================================
SELECT s.sid,
waiter.p1raw w_p1r,
holder.event h_wait,
holder.p1raw h_p1r,
holder.p2raw h_p2r,
holder.p3raw h_p2r,
count(s.sid) users_blocked,
sql.hash_value
FROM
v$sql sql,
v$session s,
x$kglpn p,
v$session_wait waiter,
v$session_wait holder
WHERE
s.sql_hash_value = sql.hash_value and
p.kglpnhdl=waiter.p1raw and
s.saddr=p.kglpnuse and
waiter.event like 'library cache pin' and
holder.sid=s.sid
GROUP BY
s.sid,
waiter.p1raw ,
holder.event ,
holder.p1raw ,
holder.p2raw ,
holder.p3raw ,
sql.hash_value;
Library Cache lock Query
=======================================================================
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 sys.v$session_wait vsw,
sys.x$kglob lob,
sys.x$kglpn pn,
sys.v$session ses
where vsw.event = 'library cache lock '
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr
/
Detect Library Cache holders that sessions are waiting for
=======================================================================
set pagesize 40
select x$kglpn.inst_id,sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req
from x$kglpn , gv$session
where KGLPNHDL in (select p1raw from gv$session_wait
where wait_time=0 and event like 'library cache%')
and KGLPNMOD <> 0
and gv$session.saddr=x$kglpn.kglpnuse ;
PROMPT Detect Library Cache holders that sessions are waiting for
Detect sessions waiting for a Library Cache Locks
=======================================================================
select sid Waiter, p1raw,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from gv$session_wait where wait_time=0 and event like 'library cache%';
Sessions waiting on Library Cache events
=======================================================================
set pagesize 80
undefine spid
col spid for a8
col INST_ID for 99
col sid for 99999
set linesize 140
col action format a20
col logon_time format a15
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col event for a20
col program for a20
col "Last SQL" for a30
col last_call_et_hrs for 999.99
col sql_hash_value for 9999999999999
select p.INST_ID,p.spid,s.sid, s.serial#, s.status,s.last_call_et/3600 last_call_et_hrs ,
s.process cli_process,s.machine cli_mach,sw.event,
s.action,s.module,s.program,s.sql_hash_value,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p,gv$session_wait sw
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.sid=sw.sid and
sw.event like '%library cache%'
order by p.spid;
set lines 152
col sid for a9999999999999
col name for a40
select a.sid,b.name,a.value,b.class
from gv$sesstat a , gv$statname b
where a.statistic#=b.statistic#
and name like '%library cache%';
select sid
from gv$session_wait where wait_time=0
and event like 'library cache%';
Objects waiting for Library Cache lock
=======================================================================
col type for a20
set linesize 150
set pagesize 80
col OBJECT_NAME for a20
col LOCK_ADDR for a20
select to_char(SESSION_ID,'999') sid ,
substr(LOCK_TYPE,1,30) Type,
substr(lock_id1,1,23) Object_Name,
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
lock_id2 Lock_addr
from dba_lock_internal
where
mode_requested<>'None'
and mode_requested<>mode_held
and session_id in ( select sid
from gv$session_wait where wait_time=0
and event like 'library cache%') ;
This script shows the Library cache resource types waited for over the life of the instance
================================================================================================
set linesize 152
column average_wait format 9999990.00
col event for a30
col HANDLE for a20
col PIN_ADDR for a20
col TYPE for a20
col OBJECT_NAME for a35
col LOCK_ADDR for a30
select substr(e.event, 1, 40) event,
e.time_waited,
e.time_waited / decode(
e.event,
'latch free', e.total_waits,
decode(e.total_waits - e.total_timeouts,0, 1,e.total_waits - e.total_timeouts)) average_wait
from sys.v$system_event e,
sys.v$instance i
where e.event like 'library cache%';
Count of sessions waiting
=======================================================================
select count(SESSION_ID) total_waiting_sessions
from dba_lock_internal
where
mode_requested<>'None'
and mode_requested<>mode_held
and session_id in ( select sid
from v$session_wait where wait_time=0
and event like 'library cache%') ;
What are the holders waiting for?
=======================================================================
select sid,substr(event,1,30) event,wait_time
from v$session_wait
where sid in (select sid from x$kglpn , v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse );
Process ids/sid/pid
=======================================================================
col spid for a6
col sid for 99999
set linesize 152
set pagesize 80
col action format a20
col logon_time format a15
col program for a10
col terminal for a10
col module format a13
col cli_process format a7
col cli_mach for a10
col status format a10
col username format a10
col last_call_et for 999.999
select a.sid ,
a.serial# ,
a.username ,
a.status ,
a.machine cli_mach,
a.terminal ,
a.program ,
a.module ,
a.action ,
a.sql_hash_value ,
to_char(a.logon_time,'DD-Mon-YYYY HH24:MI:SS') logon_time ,
round((a.last_call_et/60),2) last_call_et,
a.process cli_process,
b.spid spid,
b.event event,
b.state state
from gv$session a, gv$process b, gv$session_wait sw
where a.paddr=b.addr and a.inst_id=b.inst_id
and a.sid in (583,743,669,766
)
and a.inst_id=sw.inst_id
and a.sid=sw.sid;
Session details
=======================================================================
set linesize 150
col action format a25
col logon_time format a16
col module format a13
col program for a15
col process format a7
col status format a10
col username format a10
col last_call_et for 9999.99
select s.sid,p.spid,s.program, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.last_call_et/3600 last_call_et,s.process
from gv$session s, gv$process p
where p.addr=s.paddr and s.sid= '&sid';
Last SQL
=======================================================================
col username for a10
col "Last SQL" for a65
col process for a10
set pagesize 48
select s.username, s.sid,s.process, s.status,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid = '&sid';
Wait event
=======================================================================
column seq# format 99999
column EVENT format a30
column p2 format 999999
column STATE format a10
column WAIT_T format 9999
select SID,SEQ#,EVENT,P1,P2,WAIT_TIME WAIT_T,SECONDS_IN_WAIT,STATE
from v$session_wait
where sid in (159,610);
Last SQL Multiple inputs
col username for a10
col "Last SQL" for a65
col process for a10
set pagesize 48
select s.username, s.sid,s.process, s.status,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid in (190,224,217,306);
Active transactions multiple inputs
=======================================================================
select username,s.sid,
t.used_ublk,t.used_urec
from v$transaction t,v$session s
where t.addr=s.taddr and
s.sid in (159,610);
Session details thru server process id
=======================================================================
undefine spid
col spid for a10
set linesize 150
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
select p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.last_call_et/3600 last_call_et,s.process cli_process,s.machine cli_mach
from gv$session s, gv$process p
where p.addr=s.paddr and p.spid= '&spid';
Session details thru Client process name
=======================================================================
undefine spid
col spid for a6
col sid for 99999
set linesize 140
col action format a20
col logon_time format a15
col module format a13
col cli_process format a7
col cli_mach for a10
col status format a10
col username format a10
select p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.last_call_et/3600,s.process cli_process,s.machine cli_mach
from gv$session s, gv$process p
where p.addr=s.paddr and s.process = '&cli_process';
Details thru multiple inputs of SIDs
=======================================================================
undefine spid
col last_call_et for 9999999
set pagesize 40
col spid for a6
col sid for 99999
set linesize 150
col action format a20
col logon_time format a15
col module format a13
col cli_process format a7
col cli_mach for a10
col status format a10
col last_call_et for 999.99
col username format a10
select p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.last_call_et/3600 last_call_et,s.process cli_process,s.machine cli_mach
from gv$session s, gv$process p
where p.addr=s.paddr and s.sid in (51,146,407,397,389,377,345,302,239,214)
order by sid;
This script points to the session that is holding a library cache lock for any object. A typical session will wait on a library cache lock when when it is trying to modify the object definition. The object may be a package/procedure or just a table or index definition
SELECT a.KGLPNMOD, a.KGLPNREQ, b.username, c.KGLNAOBJ, c.KGLOBTYP
FROM
x$kglpn a,
v$session b,
x$kglob c
WHERE
a.KGLPNUSE = b.saddr and
upper(c.KGLNAOBJ) like upper('%&obj_name%') and
a.KGLPNHDL = c.KGLHDADR
/