Tuning the log buffer
To tune the value for
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:
The number of waits should always be zero. If not, increase the size
If you want to know how long processes had to wait as well as the number of times then try the following script instead:
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.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.