ANBOB™

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

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).
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.

Note If row cache objects latch contention occurs while looking up objects in the dc_rollback_segments cache and if the database has a very large number of undo segments (high thousands) , such as init parameter _rollback_segment_count high > 6000, Set _ROLLBACK_SEGMENT_COUNT to a high number to keep undo segments online.

You can check the maxconcurrency:

select max(maxconcurrency) from v$undostat;
and
select max(maxconcurrency) from wrh$_undostat;

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>> .

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
  ;

  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
  ;

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

Check clue From AWR REPORT.
1.Dictionary Cache statistics
2.Latch Sleep Breakdown
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

SELECT parameter, sum(gets), sum(getmisses),
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

select cache#, type, parameter, gets, getmisses, modifications mod
from v$rowcache where gets > 0 order by gets;

OR

col cache# head "Cache|no" form 999
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;

打赏

微信扫一扫，打赏作者吧～