Know more about redo log buffer and latches

1. The Total size of the log buffer is determined by LOG_BUFFER parameter. 2. Only Server process may pin a data block in exclusive mode. 3. 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.
• commit!
• before dbwr write out dirty buffers
• A thread is closed

  4. the correct sequence for the LGWR algorithm is :

1. Acquire the redo writing and redo allocation latches
2. determine the buffer to write out
3. Release the redo allocation latch
4. determine how many writes are required

  5. 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. 6. Three most relevant redo events under normal operations:

• Log file parallel write
• Log buffer space
• Log file sync

  7. 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

  8. 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 9. Redo buffer allocation retries can be gathered by querying the name and value columns from V$ dynamic views: V$sesstat V$sysstat 10. 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%. 11. 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. 12. 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 13. _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. 14. A standby database must be refreshed following a nologging operation. When nologging occurs, the redo logs of the standby database are not written to. 15. 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.