Optimizing Oracle Redo Log Buffer for Peak Performance

Optimizing Oracle Redo Log Buffer

for Peak Performance



Alireza Kamrani
17/March/2025

Optimizing Oracle Redo Log Buffer for Peak Performance

In Oracle Database, the Redo Log Buffer is a critical memory structure that temporarily stores redo entries before they are written to the redo log files. Proper tuning of this buffer is essential for optimizing database performance, reducing I/O contention, and ensuring efficient transaction processing.

Why Redo Log Buffer Tuning Matters?

• Prevents excessive waits on "log buffer space" and "log file sync" events.

• Reduces disk I/O overhead by efficiently batching redo log writes.

• Enhances transaction throughput, especially in high-commit environments.

Key Tuning Considerations:

• Size Appropriately – Allocate sufficient memory to avoid frequent log buffer flushes while preventing excessive memory usage.

• Monitor Performance Metrics – Use AWR, ASH, and v$ views (e.g., V$LOG, V$LATCH) to detect bottlenecks.

• Optimize Log Writer (LGWR) Behavior – Ensure LGWR is not a bottleneck by tuning disk I/O performance and adjusting commit_write settings if necessary.

• Consider Workload Characteristics – High-frequency DML operations may require larger redo log buffers to accommodate fast-paced changes.


Start with checking current status and find issue:

SQL>
select retries.value/entries.value "redo buffer retries ratio"
from v$sysstat retries,
v$sysstat entries
where retries.name='redo buffer allocation retries'
and entries.name='redo entries';

redo buffer retries ratio
--------------
      0


Redolog buffer retry ratio should be <1% otherwise need tuning.

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 Round(e.value/s.value,5) “Redo Log Ratio”
From v$sysstat s, v$sysstat e
Where s.name = ‘redo log space requests’
and e.name = ‘redo entries’;

If the ratio of “redo log space requests” to “redo entries” is less than 5000:1 (i.e. less than 5000 in the above), 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.





SQL> select sid,wait_time from v$session_wait;

SID WAIT_TIME

---------- ----------
11            -1
1               0
2               0
3               0
6               0
7               0
10             0
9               0
4               0
5               0
8               0
11 rows selected.

If wait_time for accessing redolog buffer is greater than 0 second and less than 1 second then, the value of wait_time will be -1.

Tuning Redo Log Buffer steps:

1. Increase log buffer

2.improve efficiency of checkpoints.

3 Speed up archiving

4:Reduce redo generation

5: Add more redolog file

Adjust it based on  your workload and database status.

Increase Redo Log Buffer

Oracle log_buffer parameter is not to be directly set anymore. Instead Oracle automatically calculates internally how big this buffer should be. Although it is changeable by DBA.

SQL> show parameter log_buffer;

NAME TYPE VALUE
------------------        -----------        ----------------
log_buffer          integer          524288

Note: Log_buffer is not dynamic

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

System altered.

SQL> startup force;



Improve efficiency of checkpoints.

The maximum length of the checkpoint queue is determined by the parameter from the following list that provides the shortest recovery time:

• LOG_CHECKPOINT_TIMEOUT: The number of seconds that has passed between the checkpoint position and the last write to the redo. No block in the buffer cache will be dirty longer than this time.

• LOG_CHECKPOINT_INTERVAL: The number of operating system block in the redo log between the checkpoint position and the end of the redo log.

• FAST_START_IO_TARGET: The number of I/O operations the database should take to perform crash recovery of a single instance.

• FAST_START_MTTR_TARGET: The average number of seconds the database should take to perform crash recovery of a single instance.


Speed up archiving

SQL> show parameter log_archive_max_process

NAME TYPE VALUE

-------------------------------------------------- ----------- ---------------

log_archive_max_processes      integer           2

SQL> select process,status from v$archive_processes;

Based on your redolog configuration,  if you have some of the Stoped processes , then you can increase it:

SQL> alter system set log_archive_max_processes=10;


Reduce redo generation

If possible use NOLOGGING option or APPEND Hint on INSERT operations.

SQL> alter table scott.emp nologging;


Increase the size of redo logs

Remove inactive old redo logs and add new with larger size.
Becareful for standby env.

Note:

• Online redo log files should be sizes to perform a log switch no more than 15min. These is no a recommendation for all database and must adjust carefully.

• A high value of redo buffer allocation retries indicates that you may want to increase the size of the online redo log files.


The redo log buffer size can be analyzed using V$SYSSTAT, particularly the "redo allocation retries" statistic. This metric indicates how often Oracle couldn't allocate space in the redo log buffer immediately and had to retry.


Steps to Analyze and Tune LOG_BUFFER

1. Query Key Redo Statistics

Run the following query to check redo-related stats:

SELECT name, value FROM v$sysstat
WHERE name IN ('redo allocation retries', 'redo buffer allocation retries', 'redo entries', 'redo size');

2. Interpretation of Results

• redo allocation retries:

0 or very low → No issue, no need to increase LOG_BUFFER.

• High (>1000 per hour or continuously increasing) → Maybe needs to consider increasing LOG_BUFFER.

• redo buffer allocation retries:

This metric should ideally be close to zero. If it’s high, it confirms redo log buffer contention.

• redo size:

Helps estimate total redo generated, useful for setting LOG_BUFFER.

• redo entries:

Shows the number of transactions generating redo.

3. Possible Actions

Recommendations Based on redo buffer allocation retries:

Near Zero: This is optimal; no action is needed.

Consistently Increasing: If this value is continuously increasing, it indicates that user processes have had to wait for space in the redo log buffer. In such cases, consider increasing the size of the redo log buffer by adjusting the LOG_BUFFER parameter.


Additional Considerations:

Monitoring: Regularly monitor the redo buffer allocation retries statistic over time to detect trends or sudden increases.

System Resources: Ensure that increasing the LOG_BUFFER does not adversely affect other memory allocations within the System Global Area (SGA).


By maintaining the redo buffer allocation retries metric near zero, you can help ensure that the redo log buffer is adequately sized, reducing potential contention and improving overall database performance.

If you have a definite diagnosis of insufficient buffer space, then try starting with smaller values ​​and then analyze the database behavior. Do not start with multiple values ​​all at once.

4. Setting an Optimal Value

If redo allocation retries are high, increase the buffer:

ALTER SYSTEM SET LOG_BUFFER = <new_value_in_bytes> SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP;

Note: When processes wait on the Log Buffer Space event, the log buffer is filling up faster than what the LGWR process can write to disk. If this wait event is frequent, throughput is reduced for the entire database. To address this problem, look at increasing the LOG_BUFFER to an optimal size.

Other tuning alternatives include:

• Put log files on faster disks.

• Look at tuning checkpoint or archive processes (log files switches).

• Review application design, use NOLOGGING operations where appropriate, and avoid changing more data than required.

• Finally, check refresh methods when using Materialized Views to reduce logging activity.


Following statistics (which are available in V$SYSSTAT) indicate contention in the redo log buffer at some level:


Contention Statistics in the Redo Log Buffer


Redo buffer allocation retries:
Indicates that a user process has had to wait for space in the redo log buffer. Increase the size of the redo log buffer if you see this latch causing wait issues. This might also imply an  I/O problem with the online redo logs (since the process of writing to the online redo logs can cause delays in allocating space to the redo log buffer).


Redo writer latching time:
High values over a short period of time can indicate latching issues associated with the LGWR process. This might be a result of disk contention causing LGWR writes to the online redo logs to be delayed.

Redo log space wait time:
Indicates sessions have waited for space to be allocated from the redo log buffer. High values can indicate latch contention for the redo logs. This could indicate an insufficient number of redo logs, or redo logs that are not sized properly. Additionally this can indicate IO problems with the disks that the redo logs are located on.


Note:
If you have a definite diagnosis of insufficient buffer space, then try starting with smaller values ​​and then analyze the database behavior. Do not start with multiple values ​​all at once.Also usually, value that consider automatically by Oracle is sufficient.

Written by:
Alireza Kamrani