High Availbility

OS & Virtualization

Monday, June 18, 2007

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 name, value from v$sysstat
Where name in ('redo log space requests', 'redo entries');

If the ratio (redo log space requests / redo entries) is greater than 1:5000, 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.

Link of Oracle tuning
http://www.cryer.co.uk/brian/oracle/tuning.htm

No comments: