Performance Tuning ‘latch: row cache objects’ Event

This latch comes into play when user processes are attempting to access or update the cached data dictionary values.

row cache lock Depends on which cache the lock is happening on

ash.session_id sid,
ash.blocking_session bsid,
nvl(o.object_name,to_char(CURRENT_OBJ#)) obj,
o.object_type otype,
CURRENT_FILE# filen,
CURRENT_BLOCK# blockn,
ash.SQL_ID,
nvl(rc.name,to_char(ash.p3)) row_cache
from v$active_session_history ash,
( select cache#, parameter name from v$rowcache ) rc,
all_objects o
where event='row cache lock'
and rc.cache#(+)=ash.p1
and o.object_id (+)= ash.CURRENT_OBJ#
and ash.session_state='WAITING'
and ash.sample_time > sysdate - &minutes/(60*24)
Order by sample_time;

Check clue From AWR REPORT.

1. 1.Dictionary Cache statistics
2. 2.Latch Sleep Breakdown
3. 3.Latch Miss Sources

To determine if the row cache is being used efficiently, execute the following SQL. If the ratio is not close to 1 then some tuning required

round(100*sum(gets - getmisses) / sum(gets),2) pct_succ_gets,
sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter
order by 2;

OR

from v$rowcache where gets > 0 order by gets;

OR

col parameter head "Parameter" form a25
col type head "Type" form a12
col subordinate# head "Sub|ordi|nate" form 9999
col rcgets head "Cache|Gets" form 999999999999
col rcmisses head "Cache|Misses" form 999999999999
col rcmodifications head "Cache|Modifica|tions" form 999999999999
col rcflushes head "Cache|Flushes" form 999999999999
col kqrstcln head "Child#" form 999
col lagets head "Latch|Gets" form 999999999999
col lamisses head "Latch|Misses" form 999999999999
col laimge head "Latch|Immediate|gets" form 999999999999
select
dc.kqrstcid CACHE#, dc.kqrsttxt PARAMETER, decode(dc.kqrsttyp, 1,'PARENT','SUBORDINATE') type,
decode(dc.kqrsttyp, 2, kqrstsno, null) subordinate#,
dc.kqrstgrq rcgets, dc.kqrstgmi rcmisses, dc.kqrstmrq rcmodifications, dc.kqrstmfl rcflushes, dc.kqrstcln,
la.gets lagets, la.misses lamisses, la.immediate_gets laimge
from x$kqrst dc,
v$latch_children la
where dc.inst_id = userenv('instance')
and la.child# = dc.kqrstcln
and la.name = 'row cache objects'
order by rcgets desc;