Monday, September 8, 2008

How to Tune Log Buffer


We need to determine the sapce request ratio to tune the value of LOG_BUFFER, this is the ratio of redo log space requests to redo log requests:

Select Round(e.values/s.values,5) "Redo Log Ratio"
From v$sysstat s, v$sysstat e Where s.name = 'redo log space request' and e.name
= 'redo entries';

If (redo log space requests / redo entries) is greater than 1:5000 (i.e. 0.0002 in the above), then we need to increase the size of the redo log buffer until the space request ratio stops falling.

Alternately, if memory is not a restriction then try to decrease the number of period that a process had to wait for the log cache to be flushed:


Select name, value from
v$sysstat Where name = 'redo log space requests';

The number of waits should always be zero. If not, increase the size of LOG_BUFFER, until the number returns to zero. Normally there is no benefit in setting this beyond 1M.

Execute the below mentioned query to find out how long processes had to wait as well as the number of periods:

Select name, value from v$sysstatWhere name in ('redo
log space requests', 'redo log space wait time');

This shows the time in units of 10 milliseconds. Watch out that because of the time granularity, 'redo log space requests' may be greater than zero at the same time 'redo log space wait time' is zero. This happens if none of the waits were for 10ms or longer. Until you have a fully loaded server having 'redo log space wait time' of (or near) zero may show an acceptable level of tuning.

2 comments:

  1. Thanks 4 sharing this....good1 4 dba.....
    :)
    :)

    ReplyDelete
  2. Select Round(e.values/s.values,5) "Redo Log Ratio"
    From v$sysstat s, v$sysstat e Where s.name = 'redo log space request' and e.name
    = 'redo entries';


    Invalid Column... I think it should be value instead of valueS.

    ReplyDelete