WAITEVENT: “latch: row cache objects“ Reference Note (文档 ID 1550722.1)

最新推荐文章于 2024-09-08 10:55:06 发布

Dav_2099

最新推荐文章于 2024-09-08 10:55:06 发布

阅读量564

点赞数 17

文章标签：服务器 oracle

本文链接：https://blog.csdn.net/Dav_2099/article/details/140225981

版权

"latch: row cache objects" Reference Note

This is a reference note for the wait event "latch: row cache objects" which includes the following subsections:

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

See Note:61998.1 for an introduction to Wait Events.

Definition:

Versions:10.1 - 11.2
Documentation: 11.2 11.1 10.2 10.1
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

 NOTE: There may be a need to prepend the &P1RAW value with zeroes, such that it becomes a sixteen-character value.
 ;

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
III 17799716 11.2.0.4.BP10, 12.1.0.2, 12.2.0.1 High row cache requests for a query with many indexes
III 17720709 12.1.0.2, 12.2.0.1 High "latch: row cache objects" waits on dc_rollback_segments when using Securefiles
II 16636772 12.1.0.2, 12.2.0.1 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.1 Streams capture latch contention / 'latch: row cache objects' waits
II 15836926 11.2.0.4, 12.1.0.2, 12.2.0.1 Streams / preparer contention due to 'latch: row cache objects'
C II 25997804 12.2.0.1 'set role' statement under high concurrency causes more 'latch:row cache objects'
III 22568797 11.2.0.4.200114, 12.1.0.2.190115, 12.2.0.1 latch: row cache objects contention with editions.
I 21474080 12.2.0.1 Latch: Row Cache Objects Waits on dc_props
II 20948239 12.2.0.1 on 11.2.0.4 rac environment, high waits on 'latch: row cache objects'
IIII 18199537 11.2.0.4.4, 11.2.0.4.BP10, 12.1.0.2, 12.2.0.1 RAC database becomes almost hung when large amount of row cache are used in shared pool
II 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 IIII 13902396 12.1.0.1 Hash joins cause "row cache objects" latch gets and "shared pool" latch gets (disabled fix)
IIII 17608518 12.1.0.1 "row cache objects" latch contention on dc_rollback_segments
IIII 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

Tracing User sessions Note:1274511.1