Traditional log buffer tuning has been changed in 10gR2, which prompts some problems for highly intensive DB. The first post is a quick recap of log buffer generalities, the second will focus on 10gR2 changes and suggest some log buffer management tips.
Part 1: log buffer generalities
Undersized log buffer is bound to cause sessions to fight for space in the log buffer. Those events are seen under the “log buffer space” event (configuration wait class in 10g). Oversized log buffer are the cause of less known issues: infrequent commits database wise allow log entries to pile up in the log buffer, causing high volumes of LGWR I/Os at commit time, longer “log file sync” experienced by foreground processes and “log file parallel writes” by the LGWR. Remember that only four cases (and not 3 as often written – there is one additional known case that is RAC specific) trigger a LGWR write:
- A transaction commits or rollbacks
- Every 3 seconds
- When the log buffer is 1/3 full or the total of redo entries is 1MB (default _LOG_IO_SIZE), whichever case occurs first.
- A less documented case occurs on RAC infrastructure when a dirty block has to be read by another instance: redo entries associated with a block must be flushed prior to the transfer. This event is called write ahead logging.
To wrap up the case for log buffer tuning, a couple of latches must be reckon with: the redo writing and redo copy latches which may cause SLEEPS (session waiting) when the LGWR is overactive.