Re: Help me tuning this wait event:log file sync

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 12 Jul 2002 10:50:10 +0100
Message-ID: <1026467519.16669.0.nnrp-12.9e984b29@news.demon.co.uk>

Log file sync can be very misleading.

I note that log file WRITES do not appear as a problem. Remember that a log file write wait will cause a log file sync for every user who commits for the duration of that wait.

So if you have two hundred very busy users committing very frequently, you could easily end up with log file sync showing up with 200 times the impact of log file sync.

Look at the session level - is any one session being impacted significantly by log file sync ?

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

chao_ping wrote in message <3d2e60f8$1_at_usenetgateway.com>...


>hi, dbas: One of the database i manage face a serious wait event, log

>file sync. This is a big and busy oltp system, and using disk array of

>Sun T3 with raid-5. We are using Veritas QuickIO for datafile and redo

>log files. The pressure on the database is growing fast, and more and

>more the redo log becomes the bottleneck of the database. Here is some

>data showing the fact:

>

>2002/05/03 Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits

>        Time (cs) Wt Time --------------------------------------------

>        ------------ ------------ ------- log file sync 82,244 92,442

>        33.68 db file sequential read 298,301 80,719 29.41 log file

>        parallel write 81,849 36,989 13.48 db file parallel write 5,427

>        33,615 12.25 control file parallel write 4,673 6,104 2.22

>

>

>2002/06/07 Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits

>        Time (cs) Wt Time --------------------------------------------

>        ------------ ------------ ------- log file sync 6,352,383

>        15,785,313 40.09 db file sequential read 26,862,699 12,538,922

>        31.85 log file parallel write 5,971,229 3,990,066 10.13 db file

>        parallel write 290,479 3,164,391 8.04 db file scattered read

>        1,749,137 814,981 2.07

>

Received on Fri Jul 12 2002 - 04:50:10 CDT