The Total size of the log buffer is determined by LOG_BUFFER parameter.
Only Server process may pin a data block in exclusive mode.
LGWR writes to the redo log files when:
- The redo log buffer is 1/3 full.
- 1 MB of data have been written to the redo log buffer.
- A 3-second time-out occurs.
- before dbwr write out dirty buffers
- A thread is closed
the correct sequence for the LGWR algorithm is :
- Acquire the redo writing and redo allocation latches
- determine the buffer to write out
- Release the redo allocation latch
- determine how many writes are required
In Oracle8i, the redo copy latch is always acquired regardless of the redo size.
Because the parameter LOG_SMALL_ENTRY_MAX_SIZE is obsolete, a redo copy latch is always acquired.
Three most relevant redo events under normal operations:
- Log file parallel write
- Log buffer space
- Log file sync
relevant redo statistics and their purpose
- REDO writes === Number of times the log buffer is written
- redo blocks written === Number of times the log buffer is written
- redo write time === Total time required to write all the redos to disk
- redo buffer allocation retires === Total number of retries necessary to allocate space in the redo buffer
Log buffer contention is typically indicated by Redo buffer allocation retries .
The buffer allocation retries indicates that a process has to continually try to allocate buffers that are not available
Redo buffer allocation retries can be gathered by querying the name and value columns from V$ dynamic views:
Stripe the redo log files across physical disks will help flush the redo buffer faster.
The redo log file write batch is 128K, so striping the redo logs across 8 disks with a stripe size of 16K could improve the write time by as much as 75%.
The number of redo copy latches can be defined by setting _Log_simultaneous_copies .
The parameter begins with an underscore because in 8i it is a hidden parameter.
The purpose of the redo writing latch is to: Prevent multiple processes from posting
LGWR when there is no space in the buffer .
The redo writing latch frees space in log buffer and does not want
processes writing to the buffer while space is being freed
_log_io_size is set to reduce contention for the redo writing latch.
But notice that it is a hidden parameter. This is the number of used redo blocks that will automatically initiate a log write.
A standby database must be refreshed following a nologging operation.
When nologging occurs, the redo logs of the standby database are not written to.
log_small_entry_max_size is used to determine if a redo copy latch is required based upon the redo entry size,
however, this parameter is obsolete in Oracle8i, as a copy latch is always acquired.
_log_simultaneous_copies determines the number of redo copy latches to allocate.
Note that this is a hidden parameter in Oracle8i because it is not recommended to have more than one copy latch.
log_checkpoint_interval determines the need for a checkpoint.