How Redo Works in Oracle Database

How Redo Works in Oracle Database

Oracle sets the default value for the log_buffer parameter somewhere between 5 MB and 32 MB, depending on your SGA and PGA sizes as well as on whether you’re using a 32-bit or 64-bit operating system. You can set the value of the parameter as low as 2 MB and as high as 64 MB (32 bit) or 266 MB (64 bit).

When the Oracle server processes change data blocks in the buffer cache, those changes are written to the redo logs in the form of redo log entries, before they are written to disk. The redo log entries enable the database to redo or reconstruct the database changes by using various operations such as INSERT, UPDATE, and DELETE, as well as DDL operations. The Oracle redo logs are thus critical for any database recovery because it’s these redo log entries that enable the database to apply all the changes made to the database from a point in time in the past. The changed data doesn’t directly go to the redo logs, however; Oracle first writes the changes to a memory area called the redo log buffer. It’s the value of this memory buffer that you can configure with the LOG_BUFFER parameter. The Oracle log writer (LGWR) process writes the redo log buffer entries to the active redo log file (or group of files). LGWR flushes the contents of the buffer to disk whenever the buffer is one-third full or if the database writer requests the LGWR to write to the redo log file. Also, upon each COMMIT or ROLLBACK by a server process, the LGWR process writes the contents of the buffer to the redo log file on disk.

The redo log buffer is a reusable cache, so as entries are written out to the redo log file, user processes copy new entries into the redo log buffer. While the LGWR usually works fast enough so there’s space in the buffer, a larger buffer will have more room for new entries. Be aware that a larger buffer might increase the time for commits because there are more bytes to write to disk following each commit in the database. If your database is processing large updates,the LGWR has to frequently flush the redo log buffer to the redo log files even in the absence of a COMMIT statement, so as to keep the buffer no more than a third full. Raising the size of the redo log buffer is an acceptable solution in this situation and allows the LGWR to catch up with the heavy amount of entries into the redo log buffer. This also offsets a slow I/O system in some ways if you think the performance of the LGWR process is not fast enough. There are a couple of ways in which you keep the pressure on the redo log buffer down: you can batch COMMIT operations for all batch jobs and also specify the NOLOGGING option where possible, say during regular data loads. When you specify the NOLOGGING option during a data load, Oracle doesn’t need to use the redo log files, and hence it also bypasses the redo log buffer.

It’s fairly easy to tune the size of the LOG_BUFFER parameter. Just execute the following statement to get the current “redo log space request ratio”:

SQL> select round(t.value/s.value,5) "Redo Log Space Request Ratio"
from v$sysstat s, v$sysstat t
where s.name = 'redo log space requests'
and t.name = 'redo entries'

The redo log space request ratio is simply the ratio of total redo log space requests to redo entries. You can also query the V$SYSSTAT view to find the value of the statistic redo buffer allocation retries. This statistic shows the number of times processes waited for space in the redo log buffer:

SQL> select name,value from V$SYSSTAT where name= 'redo buffer allocation retries';

Execute this SQL query multiple times over a period of time. If the value of the “redo buffer allocation retries”

statistic is rising steadily over this period, it indicates that the redo log buffer is under space pressure, and as a result,processes are waiting to write their redo log entries to the redo log buffer. You must increase the size of the redo log buffer if you continue to see this.