Tuning 'log file sync'

From: Vsevolod Afanassiev <vafanassiev_at_yahoo.com>
Date: 1 Sep 2005 16:08:52 -0700
Message-ID: <1125616132.225746.193630@g14g2000cwa.googlegroups.com>

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.

Thanks

                            Time  Average
                          Waited     Wait
Event                      (sec)    (sec)
------------------------  -------  ------
SQL*Net message from c    9836478     .19
rdbms ipc message          798023     .30
PX Idle Wait               763886    2.15
enqueue                    387408     .71
log file sync              237811     .21
pmon timer                 190927    3.11
smon timer                 186319    5.18
db file sequential read     85030       0
db file parallel write      57577     .20
local write wait            41371     .42
rdbms ipc reply             32636     .33
log file parallel write     27843     .03
latch free                   8538     .01

Received on Thu Sep 01 2005 - 18:08:52 CDT