Tuning Redo Log buffer

Oracle database maintains a Redo Log that records the changes made to the datafiles. All such changes that are maintained by Redo Log are first written into Log Buffer until those changes are committed. The purpose of storing the changes in memory is to reduce the disk IO operations. For a transaction, it is necessary to get access of Redo Allocation Latch, before the changes could be written onto Redo Log. Redo Allocation Latch controls the allocation of space for redo entries in the Redo Log buffer. If it doesn't get access of that, it tries for Redo Copy Latch, which gets used when the size of a redo entry is greater than the value of LOG_SMALL_ENTRY_ MAX_SIZE. There is just one such latch so as to avoid simultaneous writing to the log. If none are available, then it results in an “immediate miss”. Oracle keeps recording the number of gets and misses of the Redo Allocation Latches in v$latch table. The following SQL code gives the ratio of Redo Allocation Latch misses.

select name latch_name, immediate_ gets, immediate_misses, round(decode(immediate_gets-immediate_ misses,0,1, immediate_gets-immediate_misses)/ decode(immediate_gets ,0,1,immediate_gets),3)*100 hit_ratio from v$latch where name = 'redo copy';

If this ratio keeps falling below 99%, then you should lower the value for LOG_SMALL_ENTRY_MAX_SIZE parameter in init.ora file under Oracle home directory. This way the Redo Copy Latch will be used more as the redo entry size would be greater than the LOG_SMALL_ENTRY_MAX_SIZE value. Alternately, the number of Redo Copy Latches can be changed upto twice the number of CPUs. For this, the value of LOG_SIMULTANEOUS_COPIES needs to be changed.