Tuning 'log file sync'

From: Vsevolod Afanassiev <vafanassiev_at_yahoo.com>
Date: 1 Sep 2005 15:54:32 -0700
Message-ID: <1125615271.983719.184400@g43g2000cwa.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.

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 - 17:54:32 CDT