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 Bolek