Vsevolod Afanassiev 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.
>
> 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
check your commit frequency...
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"Semper in excremento, sole profundum qui variat."
------------------------------------------------------------
Received on Sun Sep 04 2005 - 08:56:51 CDT
