WAITEVENT: "latch: row cache objects" Reference Note (Doc ID 1550722.1)

latch: row cache objects" Reference Note

• Brief definition
• Individual wait details (eg: For waits seen in <<View:V$SESSION_WAIT>>)
• Systemwide wait details (eg: For waits seen in <<View:V$SYSTEM_EVENT>>)
• Reducing waits / wait times
• Known Bugs

Definition:

• Versions:10.1 - 11.2
  Documentation:
  
• Latches are like short duration locks that protect critical bits of code. This wait indicates that the process is waiting for a "row cache latch" latch that is currently busy (held by another process).
• There are several child latches each one covering one or more row cache parameters. The specific child latch or latches involved can be determined from the "latch address" wait parameter (P1) exposed in V$SESSION_WAIT and related views / traces.

Individual Waits:

 Parameters:

• P1 = Latch address
• P2 = Latch number
• P3 = Tries

• Latch address

  The address of the latch that the process is waiting for. The hexadecimal value of P1 (P1RAW) can be used to determine which latch is waited for thus:

    set pages 1000
    SELECT name, 'Child '||child#, gets, misses, sleeps
      FROM v$latch_children 
     WHERE addr='&P1RAW'
    ;

  
  You can find which row cache parameter the waiting session is after using a select like the following as SYSDBA:

    SELECT 
          kqrsttxt PARAMETER, 
  	-- kqrstcid CACHE#, 
          kqrstcln "Lchild#", 
          kqrstgrq "DCGets", 
          l.gets   "LGets", 
          l.misses "Misses"
    FROM X$KQRST, V$LATCH_CHILDREN l
   WHERE l.addr='&P1RAW'
     and l.child#=KQRSTCLN
   ORDER BY 1,2
   ;

• Latch number

  This is the latch number that indexes the V$LATCHNAME view. It will be the "row cache objects" entry as indicated in the wait name itself.

• Tries

  This is basically a counter that counts the number of times we tried to get the latch (slow with spinning) and the process had to sleep. See the "Wait Time" notes below.

 Wait Time:

When a session waits on latch: row cache objects it usually sleeps for a short time then re-tests the latch to see if it is free . If it still cannot be acquired then P3 is incremented and the session waits again. The wait time can increase exponentially and does not include spinning on the latch (active waiting). The exact latch wait behaviour depends on the platform/version/configuration .

The SECONDS_IN_WAIT figure in <<View:V$SESSION_WAIT>> shows the total time spent waiting for the latch including all sleeps.

  Finding Blockers:

The blocker is the session holding the latch. As latches are usually held for very short durations then the waits are usually related to contention rather than a "stuck" blocking session. For the rare cases where a session is holding a latch indefinitely the latch holder should show in the <<View:V$LATCHHOLDER>> .

Systemwide Waits:

As a latch wait is typically quite short it is possible to see a large number of latch waits which only account for a small percentage of time.

If the TIME spent waiting for "row cache object" latches is significant then it is best to determine which row cache is suffering from contention. AWR and other performance reports include sections which show latch activity and row cache activity in the period sampled. See the "Row Cache" section (based on <<View:V$ROWCACHE>>) to see which row caches are incurring high numbers of gets. The latch contention will typically be related to the row cache/s with high GETS figures but to be sure it is best to check <<View:V$LATCH_CHILDREN>> (or a related historic view such as DBA_HIST_LATCH_CHILDREN)
eg:

  SELECT child#, gets, misses, sleeps
    FROM v$latch_children
   WHERE name='row cache objects'
     and sleeps>0
   ORDER BY sleeps,misses,gets
  ;
  
  Note that the above select gives the worst latches at the BOTTOM of the list.

The above gives the systemwide number of waits for each child "row cache objects" latch since instance startup. For specific time periods one can use the view DBA_HIST_LATCH_CHILDREN but data for this view is only captured for STATISTICS_LEVEL=ALL.
eg:

Use DBA_HIST_SNAPSHOT to find the relevant SNAP_ID values - data will only be present for snapshots at SNAP_LEVEL=2.

  SELECT a.child#, 
         b.gets-a.gets GETS, 
         b.misses-a.misses MISSES, 
	 b.sleeps - a.sleeps SLEEPS
    FROM DBA_HIST_LATCH_CHILDREN b, DBA_HIST_LATCH_CHILDREN a
   WHERE a.latch_name='row cache objects'
     and b.latch_name='row cache objects'
     and a.child#=b.child#
     and b.sleeps-a.sleeps>0
     and a.snap_id=&BEGIN_SNAPID and b.snap_id=&END_SNAPID ORDER BY 4,3,2
  ;

  Note that the above select gives the worst latches at the BOTTOM of the list.

If there are multiple rows the important thing to note is whether the SLEEPS are reasonably distributed or if there are one or two child latches responsible for 80% of the SLEEPS. If the contention is focused on one or two child latches make a note of which children are seeing a problem - note the CHILD# column above. This can be mapped to row cache entries using an X$ view.
eg:

  SELECT 
        kqrsttxt PARAMETER, 
        kqrstcln "Lchild#", 
        kqrstgrq "DCGets"
   FROM X$KQRST
  WHERE KQRSTCLN = &LATCH_CHILD_NUMBER
  ;

One can also look at:

• Does the same session/s keep appearing in <<View:V$LATCH_HOLDER>>
• Sessions with high latch waits in <<View:V$SESSION_EVENT>>
  (Although it is important to note that innocent sessions may show high numbers of waits if some other session is repeatedly holding the latch)

Reducing Waits / Wait times:

There is no general advice to reduce latch waits as the action to take depends heavily on the row cache entry which is causing the waits. If there is no particular child latch and waits occur across all latches then check for CPU starvation or uneven O/S scheduling policies - a CPU bound system will often show numerous latch waits especially for row cache objects latches.

If the activity is focused on specific row cache entries then one normally needs to look at what that row cache stores and why the system needs to be looking at the data so much. For example, if there is a high hard parse rate then this is likely to cause high usage on dc_users, dc_objects etc.. so one might consider options to reduce the hard parse rate.

There is also a V$LATCH_MISSES view which may be of help to Oracle Support in more obscure cases:

  SELECT "WHERE", SLEEP_COUNT, WTR_SLP_COUNT, LONGHOLD_COUNT
    FROM v$latch_misses
   WHERE parent_name='&ADDR_OF_PROBLEM_LATCH'
   ORDER BY 1
  ;

This shows where-abouts in the code the latch holder and latch waiters were when the latch was requested but not obtained immediately.

Known Issues / Bugs:

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:

NB	Prob	Bug	Fixed	Description
	II	17799716	11.2.0.4.BP10, 12.1.0.2, 12.2.0.0	High row cache requests for a query with many indexes
	II	17720709	12.1.0.2, 12.2.0.0	High "latch: row cache objects" waits on dc_rollback_segments when using Securefiles
	II	16636772	12.1.0.2, 12.2.0.0	Database Vault secure applocations role always enabled if the rule set fail option is silent
	-	16221589	11.2.0.4, 12.1.0.2, 12.2.0.0	Streams capture latch contention / 'latch: row cache objects' waits
	-	15836926	11.2.0.4, 12.1.0.2, 12.2.0.0	Streams / preparer contention due to 'latch: row cache objects'
	I	15857552	11.2.0.3.BP17, 11.2.0.4, 12.1.0.1	High "latch: row cache objects" waits for LOB segments in SecureFile
D	III	13902396	12.1.0.1	Hash joins cause "row cache objects" latch gets and "shared pool" latch gets (disabled fix)
	IIII	18199537	11.2.0.4.4, 11.2.0.4.BP10, 12.1.0.2, 12.2.0.0	RAC database becomes almost hung when large amount of row cache are used in shared pool
	III	17608518		"row cache objects" latch contention on dc_rollback_segments
	III	14226599	11.2.0.3.6, 11.2.0.3.BP12, 11.2.0.4, 12.1.0.1	Increase dc_rollback_segs hash buckets to reduce 'latch: row cache objects' waits
	II	14207317	11.2.0.3.7, 11.2.0.3.BP10, 11.2.0.4, 12.1.0.1	"latch: row cache objects" latch contention for LANGUAGE_MISMATCH cursors with VPD policies
	III	12772404	11.2.0.2.BP18, 11.2.0.3.7, 11.2.0.3.BP08, 11.2.0.4, 12.1.0.1	Significant "row cache objects" latch contention when using VPD - superseded
	II	10023443	11.2.0.3, 12.1.0.1	High 'latch: row cache objects' when RPC calls are used by non-owner - superceded
	IIII	8666117	10.2.0.5.5, 11.2.0.2, 12.1.0.1	High row cache latch contention in RAC
	IIII	7291739	10.2.0.4.4, 10.2.0.5, 11.2.0.1	Contention with auto-tuned undo retention or high TUNED_UNDORETENTION
	II	6870994	10.2.0.5, 11.1.0.7.3, 11.2.0.1	High US enqueue / rowcache lock while trying to online a NEW undo segment
	II	5570942	10.2.0.4, 11.1.0.6	Query Rewrite causes high parse time with high hits on 'latch: row cache objects' and dc_object_ids / Dump in STRLEN

• '*' indicates that an alert exists for that issue.
• '+' indicates a particularly notable issue / bug.
• See Note:1944526.1 for details of other symbols used

Related:

Tracing User sessions Note:1274511.1