Configure and use Redo Log Buffer

When the data block in the buffer cache is modified, the server process generates redo data and writes it to the redo log buffer. When the following conditions are met, LGWR will write the entries in the redo log buffer into the online redo log:
-- Redo log buffer is 1/3 full.
-- Timeout (Timeout) every 3 seconds ).
-- The data in log_buffer reaches 1 M.
-- When the transaction is committed.

After LGWR writes the redo log buffer redo entries to the online redo log file or disk, the user process can copy new entries to the redo log buffer, to overwrite the entries written to the disk in the memory. The LGWR write speed is usually fast enough to ensure that the buffer space can always write new entries, even when the redo log is frequently accessed.

If our system has a fast processor and a disk with a relatively low I/O speed, the processor may fill up the rest of the cache space, which will prompt LGWR to move part of the data in the buffer zone to the disk. In this case,A large log buffer can temporarily mask the impact of slow disks on the system. You can make the following options:
-- Upgrade the checkpoint or archive Process
-- Improves LGWR performance (maybe you can store all online redo logs to faster bare devices)
Rational use of redo log buffer:
-- Batch commit is used for batch operations, so that LGWR can write redo entries to online redo log files more efficiently.
-- Nologging is used when a large amount of data is loaded.


Set Log Buffer
Redo log buffer is determined by the initialization parameter LOG_BUFFER. to modify this parameter, restart the instance.
The appropriate redo log buffer parameter value can significantly improve the system throughput, especially for systems with large data volumes such as insertion, update, and deletion.
Default log buffer value: MAX (0.5 M, (128 K * number of cpus). Generally, the default value is sufficient.
Increasing the value of log buffer does not negatively affect system performance or recoverability, but only uses additional memory.

Evaluate Log Buffer

Follow the redo buffer allocation retries wait event: the number of times the user process waits for the redo log buffer space.

[SQL]

1. SQL>SELECT NAME, VALUE
2. 2FROMV $ SYSSTAT
3. 3WHERE NAME='Redo buffer allocation retries';
5. NAMEVALUE
6. --------------------------------------------------------------------------
7. Redo buffer allocation retries 0

The redo buffer allocation retries value should be close to 0.. This wait is caused by log buffer being too small or checkpointing. You You can increase the value of log buffer or improve the checkpointing or archiving process.

Another waiting event to be concerned: log buffer space.
The v $ session_wait view specifies whether the write speed of data to the log buffer is higher than that of LGWR during the "log buffer space %" event, any wait for the log buffer space.

[SQL]

1. SQL>SELECTSid, event, seconds_in_wait, state
2. 2FROMV $ session_wait
3. 3WHEREEvent ='Log buffer space %';
5. Sid event SECONDS_IN_WAIT STATE
6. ----------------------------------------------------------------------------------------------

If a log buffer space wait event occurs,
You can increase the value of log buffer.

Or move the redo log files to a faster disk, such as a striped disk.