The Oracle (tm) Users' Co-Operative FAQ

The number of redo copy latch misses reported in V$LATCH is a large fraction of the gets. What should I do ?


Author's name: Jonathan Lewis

Author's Email: Jonathan@jlcomp.demon.co.uk

Date written: 26th July 2001 - 7th Sept 2001

Oracle version(s): 7.3 - 8.1.7.0

The number of "willing to wait" redo copy latch misses reported in V$LATCH is a large fraction of the "willing to wait" gets. Why is this, and do I need to do anything about it. There are a lot of scripts/hints on the Internet saying that a high ratio of misses to gets is a bad thing..

Back to index of questions


The quick answer to the question is that you are probably looking at the half of the latch report that lists 'Willing to Wait' latches which means that you don't have a problem.

It is important to remember that there are two sets of 'gets' and 'misses' recorded by the V$LATCH dynamic performance view. Two columns are simply named gets and waits, the other two are named immediate_gets and immediate_waits. If you look at the complete set of latch statistics for your system, you will notice that there are two high usage latches on 7.3 and 8.0 - Redo Allocation and Redo Copy. Oracle 8.1 introduces a third latch, the Redo Writing latch.

It is worth looking at just the redo latches, and the gets/misses on just those latches , for example with the following SQL:

column name format a15

select
	name, 
	gets, misses, 
	immediate_gets, immediate_misses
from	v$latch
where	name like '%redo%'
;


NAME                 GETS    MISSES IMMEDIATE_GETS IMMEDIATE_MISSES 
--------------- --------- --------- -------------- ---------------- 
redo allocation   1937729         1              0                0 
redo copy              13         3        1814906                6 
redo writing       645563         1              0                0 

It takes only a moment, when presented with the results in this form, to realise that the Redo Copy latch is almost invariable acquired on an immediate get, and rarely accessed on a 'willing to wait' get. The fact that the ratio of misses to gets (3 to 13) is rather high is totally irrelevant. In fact it is actually the expected behaviour as Oracle only acquires the redo copy latch in this fashion when it is trying to suspend redo activity temporarily whilst doing some special log file activity.

If you do see a relatively large number of gets (as opposed to immediate gets) on the redo copy latch, it may suggest that your redo log file size is too small, and you have a completely different performance problem to address anyway. In this case, check the alert log for indications of 'checkpoint not complete' and check the system statistics for excessive writes to disc due to checkpointing.


Further reading: For a detailed description of the actvity associated with redo, the best reference site is Steve Adams' site http://www.ixora.com.au


Back to top

Back to index of questions