Re: Tuning 'log file sync'

On 1 Sep 2005 16:08:52 -0700, "Vsevolod Afanassiev" <vafanassiev_at_yahoo.com> wrote:

>One of our database experiences heavy waits on 'log file sync'.
>Top waits from V$SYSTEM_EVENT are given below. 'Log file sync'
>exceeds 'db file sequential read' and 'db file scattered read'.
>Metalink Note 223117.1 says that they key to understanding
>'log file sync' is to compare average times waited for
>'log file sync' and 'log file parallel write': if they are similar then
>it is I/O problem, but if they are different then "the delay is
>caused by the other parts of Redo Logging mechanism
>that occur during a COMMIT/ROLLBACK (and are not I/O-related)".
>How to find out what are these "other parts"?
>
>This is 9.2.0.5.0 on Tru64. Redo logs are on filesystem,
>500 MB each. Parameter LOG_BUFFER is set to 2M.
>The database has agressive setting for FAST_START_MTTR_TARGET (60
>seconds).
>It is my understanding that this parameter affects writes to the
>datafiles
>(checkpoints), not to log files.

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.

--
Sybrand Bakker, Senior Oracle DBA