Tuning the log buffer

To tune the value for LOG_BUFFER first determine the space request ratio, this is the ratio of redo log space requests to redo log requests:

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

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

Alternately, if memory is not a constraint then try to reduce the number of times 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. Typically, there is no advantage in setting this beyond 1M.

If you want to know how long processes had to wait as well as the number of times then try the following script instead:

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

This shows the time in units of 10 milliseconds. Be ware that because of the time granularity, 'redo log space requests' may be greater than zero whilst 'redo log space wait time' is zero. This happens if none of the waits were for 10ms or longer. Unless you have a busy server having 'redo log space wait time' of (or near) zero may indicate an acceptable level of tuning. (Tip contributed by Gianni Quattrocchi.)

These notes have been tested against Oracle 7.3.4, Oracle 8.0.5, Oracle 8.1.5 and Oracle 9.0.1 and may apply to other versions as well.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.