| Lesson 3 || Understanding the redo log |
| Objective || Describe the main tuning techniques for the redo log. |
Redo Log Tuning Techniques
Tuning for the redo log activity involves:
- Tuning the redo log buffer
- Viewing redo log activity
We will examine each of these topics in the next two lessons.
Redo log buffer tuning
The log_buffer parameter specifies the number of bytes allocated to the redo log buffer in the SGA. In general, larger values reduce redo log file I/O, particularly if transactions are long or numerous. In a busy system, the value 65K or higher is reasonable.
However, values above one megabyte are unlikely to yield significant benefit. The default is set to four times the maximum database block size for the host operating system. For example, a database with 4096 block sizes would have a 16K log buffer.
Absorbing processing spikes
The redo log buffer helps absorb processing spikes during high update/insert/delete periods. If a process is making a lot of changes, the redo it generates will be written to a memory buffer.
As the buffer fills up, the output process (LGWR) is awakened to empty the buffer. LGWR will need some lead time, since a sufficiently large transaction can generate redo faster than LGWR can write it to disk. To prevent slowdowns, Oracle starts the log writer (LGWR) when the log buffer becomes 1/3 full.
Redo log space request
The redo log space request statistic in V$SYSSTAT reflects the number of times a user process waits for space in the redo log buffer. This value of redo log space requests should be 0. If this value is non-zero, then the size of the log buffer should be increased by increasing the value of the init.ora log_buffer parameter, up to a maximum of one megabyte.
The memory set aside for the log_buffer may also be surrounded by "guard" pages to help protect the redo buffer, hence the size of redo buffers shown at instance startup may differ from the value of LOG_BUFFER set in the init.ora file.
The value of the log_buffer must be an exact multiple of the operating system (OS) block size. For example, if the OS block size is 4096, a value of 10*4096 or 40960 will be valid. The next lesson will explore redo log activity.