Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to tune LIBRARY CACHE latch contention
So finally it reappeared again, this time it has an impact on a
different statement. I have used this query:
select * from v$latch where latch# in (
select child_latch from v$sql
where address in (select sql_address from v$session
where sid in (select sid from v$session_wait where event = 'latch free') )
to get more information about currently sleeping latch and the outcome is always similar to this:
ADDR LATCH# LEVEL# NAME GETS ---------------- ---------- ---------- ------------------------------------------------------------- WAITS_HOLDING_LATCH SPIN_GETS SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP ------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --- 07000000000078A8 4 5 session allocation 258519482 16827 573959 16612 221 0 0 0 0 0
and the latch name is switching between session allocation and session switching. And from v$latch_misses for these latches:
PARENT_NAME 'WHERE' SLEEP_COUNT LOCATION -------------------------------------------------- -------------------------------- ----------- ---- session allocation where 0 ksucre: ONERROR session allocation where 13 ksuprc session allocation where 7 ksudlc session allocation where 2 ksucrp session allocation where 1 ksudlp session allocation where 5757 ksufap: active sessions session allocation where 0 ksucas session allocation where 4375 ksucri session allocation where 589 ksuxds: KSUSFCLC not set session allocation where 0 ksuxds: KSUSFCLC set session allocation where 0 ksudlc: SO not init or PMON session allocation where 0 ksuxdp session allocation where 0 ksukip session allocation where 5842 ksuxds: not user session session allocation where 0 ksuxds: KSSOINIT not set session allocation where 0 ksuevnt session allocation where 28 ksusin session allocation where 109 ksursi session allocation where 0 kskschclass session allocation where 0 kskuchclass session allocation where 353 kxfpqidqr session allocation where 1 ksusroll session switching where 0 ksusus session switching where 0 ksudus session switching where 0 ksukls session switching where 0 ksuded session switching where 0 ksuxdp session allocation where 0 kspallmod
-- Dusan BolekReceived on Tue Jan 31 2006 - 03:47:52 CST
![]() |
![]() |