Redo Log Buffer and Performance Issues related to redo log buffer

Redo log buffers, a component of the System Global Area (SGA) in Oracle, temporarily store redo entries - the changes made to data. These entries are written into an online redo log file by a Log Writer (LGWR) process. The efficient functioning of this system is vital for Oracle Database's overall performance. However, certain issues can hinder its smooth operation.

1. Inadequate Redo Log Buffer Size: If the redo log buffer size is too small, it can cause frequent log buffer space waits. The redo log buffer should be large enough to accommodate the changes made during transactions to avoid such waits and to prevent the LGWR from being invoked too frequently.
2. Frequent Log Switches: Redo log files should be sized appropriately to prevent too frequent log switches, which can cause waits and affect performance. If the alert log or the dynamic performance view V$SYSTEM_EVENT shows 'log file switch (checkpoint incomplete)' events, it indicates that the log files may be too small.
3. High Redo Buffer Allocation Retries: A high value of 'redo buffer allocation retries' from the V$SYSSTAT view indicates that the user processes often had to wait for space in the redo log buffer. This usually occurs when the LGWR process cannot write the contents of the redo log buffer to the disk fast enough, which could be due to an undersized redo log buffer or I/O system performance issues.
4. Inefficient Disk I/O Performance: Inefficient disk I/O performance can result in the redo entries not being written quickly enough from the redo log buffer to the online redo log files. This can cause 'log file parallel write' events where LGWR waits for I/O operations to complete.
5. Inadequate Redo Log Files: Having too few redo log groups can lead to increased checkpointing and log file switches. More groups and members can offer better multiplexing and reduce the probability of 'log file switch (checkpoint incomplete)' events.
6. Excessive 'redo wastage': This issue arises when transactions generate redo that is not written to disk because of a log switch. Redo wastage can be viewed in the V$SYSSTAT dynamic performance view. If this is high, consider resizing the log files.
7. Unoptimized Redo Log File Configuration: If redo log files are not mirrored or multiplexed, any failure could lead to a loss of data. Multiplexing redo log files can prevent this and increase system reliability.
8. Inefficient use of NOLOGGING option: The NOLOGGING option allows certain bulk operations to bypass redo logging, improving performance. However, misuse of this option can lead to data recovery problems.

To rectify these issues, one could tune the redo log buffer, resize redo log files, increase the number of redo log groups, and improve disk I/O performance. Furthermore, efficient usage of the NOLOGGING option and implementing redo log file multiplexing can help improve the overall performance of the Oracle Database. Monitoring and optimizing these parameters will significantly enhance the efficiency of the Oracle Database's redo logging mechanism.

The redo log buffer contains the Oracle redo logs that hold previous versions of Oracle transactions and are used to roll-forward the database in cases of disk failure. When a database is in ARCHIVELOG mode, Oracle will write these redo logs onto a disk file with the ARCH background process. Regardless of the ARCHIVELOG mode, Oracle must still manage the redo images and write them to the online redo logs, via the redo log buffer.

There are several areas where redo log processing can cause a performance problem:

1. When the redo logs switch too frequently (> 2 per hour)
2. When the database experiences a high value for redo log space requests
3. When the archived redo log file system becomes full.

Since the redo log buffer is used to cache update, insert or delete activity, high update activity would be associated with redo log buffer activity. A high rate of redo log switching or high redo log space requests always indicates a problem. The remedy is to increase the log_buffer init.ora parameter.

While we will explore these issues in detail in a later module, let us look at a simple query that can detect a high redo log space request. Note that it queries the V$SYSSTAT view. When you see high redo log space requests you can increase the init.ora parameter called log_buffer.
To increase the size of the log buffer, you increase the log_buffer parameter in your init.ora file and re-start the Oracle instance. This parameter determines the amount of memory to allocate for Oracle's redo log buffers. If there is a high amount of update activity, you should allocate more space in the log_buffer.

prompt
prompt         =========================
prompt         REDO LOG BUFFER
prompt         =========================
prompt (should be near 0, else increase size of LOG_BUFFER in init.ora)
prompt
set heading off
column value format 999,999,999
select substr(name,1,30),
       value
from v$sysstat where name = 'redo log space requests';
Here is what the output from this query might look like:
=========================
REDO LOG BUFFER
=========================
(should be near 0, else increase size of LOG_BUFFER in init.ora)
redo log space requests                   0

The next lesson concludes this module.

Before you go on, click the Exercise link below to check your knowledge of basic SGA tuning concepts.
Redo Log - Exercise