Re: Tuning 'log file sync'

On Mon, 05 Sep 2005 01:17:52 +0000, Mladen Gogala wrote:

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

PS:

---
If you don't believe me, see 10.2 reference manual and "log_file_sync"
event. It now has a parameter saying how many actual blocks have to
be written.

log file sync

When a user session commits, the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session.

Wait Time: The wait time includes the writing of the log buffer and the post.
Parameter 	Description
buffer# 	The number of the physical buffer in the redo log buffer that
needs to be synchronized


So, when the transaction incurs this wait event, it knows how many
log blocks it is actually waiting for. That is a HUGE difference from
Oracle8, so that the common 8i wisdom no longer applies. You should
increase log_buffer until waits for log buffer space are insignificant.

-- 
http://www.mgogala.com