|Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
|Date written: 26th July 2001 - 7th Sept
Oracle version(s): 7.3 - 184.108.40.206
|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