Tuning the Redo Log Buffer

The redo log buffer is a memory structure in the SGA that temporarily stores redo entries before they are written to the redo log files on disk. Redo entries are generated as a result of changes (like INSERT, UPDATE, DELETE operations) made to the database. You want to know how to tune the redo log buffer because you’ve reviewed several AWR reports that pointed out that the redo log buffer setting for your production database is too small. You configure the size of the redo log buffer by setting the value of the initialization parameter LOG_BUFFER. This parameter is static, so any changes to it require a restart of the database. You set the parameter in your init.ora file as follows:

 log_buffer=xxxxxx        

You can also change the size of the log buffer with the following ALTER SYSTEM statement:

SQL> alter system set log_buffer=xxxxxx scope=spfile; 

System altered.        

LOG_BUFFER specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file.

The log buffer size depends on the number of redo strands in the system. One redo strand is allocated for every 16 CPUs and has a default size of 2 MB. Oracle allocates a minimum of 2 redo strands per instance. When the log buffer size is not specified, any remaining memory in the redo granules is given to the log buffer.

Sql> select strand_size_kcrfa from x$kcrfstrand where ptr_kcrf_pvt_strand = hextoraw(0);        

In Oracle databases, the maximum size of the LOG_BUFFER parameter, which determines the size of the redo log buffer, can vary based on several factors including the Oracle database version, the operating system, and the specific architecture of the system (such as 32-bit or 64-bit).

Redo entries are generated as a result of changes made to the database. 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. 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:

1. the buffer is one-third full
2. if the database writer requests the LGWR to write to the redo log file.
3. 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.

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:

1. you can batch COMMIT operations for all batch jobs
2. specify the NOLOGGING option where possible, say during regular data loads.

Tuning Querys

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'        

Redo Log Space Request Ratio: This query calculates the ratio of redo log space requests to redo entries. It's a useful metric for understanding how often the database is waiting for space in the redo log buffer.

The term "redo entries" in the context of an Oracle database refers to records in the redo log that capture all changes made to the database. t.VALUE represents the number of redo entries. This is the total count of individual redo records generated due to changes in the database.

A "redo log space request" occurs when a session (user or process) needs to write redo entries to the redo log buffer but cannot do so immediately because there is not enough space available in the buffer. This situation typically arises when the redo log buffer is either too small or when redo entries are generated at a rate that exceeds the ability of the Log Writer (LGWR) process to write them to disk. s.VALUE represents the number of redo log space requests. This counts how many times sessions had to wait because there wasn’t enough space in the redo log buffer for new entries.

When a redo log space request occurs, the session that needs to write redo entries must wait until enough space becomes available in the redo log buffer. This waiting can lead to performance degradation, as transactions are delayed while they wait for buffer space.

Interpreting the "Redo Log Space Request Ratio":

• The "Redo Log Space Request Ratio" provides insight into the relationship between the total amount of redo activity (as indicated by the number of redo entries) and the frequency of waits for redo log space (as indicated by the number of redo log space requests).
• A high ratio indicates that for each redo log space request, there are many redo entries. This could be seen as a healthy sign, suggesting that redo log space requests are relatively infrequent in comparison to the amount of redo activity.
• Conversely, a low ratio might suggest that redo log space requests are frequent relative to the amount of redo activity. This could be a sign of potential performance issues, such as an inadequately sized redo log buffer, leading to frequent waits for space in the buffer.

Let's use a simple analogy to explain the concept of redo entries, redo log space requests, and their ratio in an Oracle database.

Analogy: A Busy Coffee Shop

Imagine a coffee shop where baristas (database processes) are making coffee (processing database transactions). Every time they make a coffee, they need to write down the order details (redo entries) on a notepad (redo log buffer). Once the notepad is full, the details are transferred to a larger record book (redo log files on disk) for safekeeping.

1. Redo Entries (Coffee Order Details):

?? - Each coffee order represents a redo entry. The more orders there are, the more entries the baristas write in the notepad. A high number of coffee orders means a busy coffee shop with lots of transactions (database changes).

2. Redo Log Space Requests (Need for Notepad Space):

?? - If a barista goes to write an order but finds the notepad is full, they have to wait until someone transfers the current notes to the record book to make space. This waiting is a redo log space request. Each wait represents one space request.

3. Redo Log Space Request Ratio (Orders per Wait):

?? - The ratio of coffee orders to the number of times baristas have to wait for notepad space is like your redo log space request ratio. A high ratio means that for many coffee orders, there's only an occasional need to wait for space on the notepad.

- Think of it as having taken over 32 billion coffee orders with about 2 million waits for notepad space. On average, for about every 15,966 orders, the baristas had to wait once to write down an order.

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.

References:

1. Oracle Database 19c Documentation:

?? - "LOG_BUFFER." Oracle Database Reference 19c. Available at: [Oracle Documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/LOG_BUFFER.html#GUID-17321A45-A53E-42E3-8367-4B13FB5EACA3). This reference provides official information and guidelines on the LOG_BUFFER parameter in Oracle Database 19c, offering insights into its functionality, usage, and impact on database performance.

2. Book on Oracle Database Performance Tuning:

?? - Alapati, Sam R., Darl Kuhn, and Bill Padfield. Oracle Database 12c: Performance Tuning Recipes: A Problem-Solution Approach. This book offers practical solutions and recipes for performance tuning in Oracle Database 12c, likely including strategies and best practices related to LOG_BUFFER and other performance-critical settings.

These references are valuable resources for understanding the intricacies of Oracle's LOG_BUFFER parameter and its role in database performance tuning, providing both official documentation and expert insights.

Have thoughts or questions? Feel free to share in the comments!