Re: Tuning 'log file sync'

Late feedback that may be relevant on this one.

I recently saw a cluster of examples about unusual log file sync/log file parallel write waits - (yours was one of them) and decided that something odd was happening that needed further investigation.

The results are written up here:

    http://www.jlcomp.demon.co.uk/log_file_writes.html

The bottom line is this:
Bug 2669566 - log writer does not report log file parallel write wait times properly, so a high average wait time for log file SYNC may be telling you that lgwr has an I/O problem,

    The bug is in 9.2 - it's fixed in 10.1     It's not going to be fixed in 9.2

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005







"Vsevolod Afanassiev" <vafanassiev_at_yahoo.com> wrote in message 
news:1125615265.135322.10050_at_f14g2000cwb.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

>