Re: Tuning 'log file sync'

On Fri, 02 Sep 2005 09:43:57 +0200, Sybrand Bakker wrote:

> I frequently observed log file sync problems when increasing
> log_buffer.
> This is logical as the log buffer is locked by LGWR when LGWR flushes
> the buffer to the online redo log file.
> IMO, you should decrease log_buffer to anything below 1M and you are
> shooting yourself in your foot now.

Log_file_sync signifies time spent waiting for the log buffer to be flushed. LGWR flushes the buffer when:

1. Every 3 seconds (RDBMS IPC message timeout)
2. When the log_buffer is 1/3 full
3. When there is more then 1M data in the log_buffer.
4. When commit is issued.

Only in the case of commit does the transaction that woke up ("posted") the LGWR wait for the LGWR to finish writing its data to the log file. In version 8, transactions would wait for the LGWR to flush the entire buffer, but that is no longer the case. Transactions now wait until only their data has been written to disk. So, large buffer is no longer an obstacle. The original poster does have a problem with slow LGWR writes, but that is probably because of committing each record and not because of waiting for the LGWR to flush the entire (large) log_buffer. In other words, the 8i wisdom of not creating a buffer larger then 1MB is no longer accurate unless, of course, the OP is having problems with the Oracle 8i. I have several 9.2 databases with log_buffer>=5M and no significant log sync wait. Unfortunately, I have no more 8i databases to compare.

-- 
http://www.mgogala.com