RE: Re: Help me tuning the log file sync wait event

 Definitely there is a bottleneck in disks or the response time is very high.

Since you are using Raid-5, I would suggest as a test to make your redo groups one member only or to move the other members to another raid while keeping the log_buffer at 2M.

Regards,

Waleed

-----Original Message-----
To: Multiple recipients of list ORACLE-L Sent: 7/13/02 11:03 AM

Bunyamin Karadeniz,

? 2002-07-12 01:48:00 You wrote:
>Hi ,
>You encounter log file sync and log file parallel write events .
>This can be because of a slow device on which redo log files are on .
OR
>your redo log sizing is bad.

hi, you said the redo log size have something to do with this log file sync wait event? I do not understand it, can you explain it to me? Maybe my log buffer is too big, now it is 2M in size. But when it was 1M, i see much wait time for log buffer space:(, so wondering whether downsize the log buffer.

        Thanks for your suggestions.

Good luck!

            chaos
            chaospku_at_163.net

zhu chao
DBA of Eachnet.com
86-021-32174588-667

>
>Bunyamin Karadeniz
>Oracle DBA
>Havelsan A.S. Eskisehir yolu 7.km
>Ankara / Turkey
>Tel : +90 535 3357729
>
>
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Friday, July 12, 2002 6:33 AM
>
>
>> 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/05/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
>>
>> 2002/05/21
>> Top 5 Wait Events
>> ~~~~~~~~~~~~~~~~~ Wait
%
>Total
>> Event Waits Time (cs)
Wt
>Time
>> -------------------------------------------- ------------
------------ ---
>----
>> log file sync 2,207,609
6,688,751
>59.47
>> log file parallel write 2,044,977
1,385,379
>12.32
>> db file parallel write 103,155
1,203,077
>10.70
>> db file sequential read 8,772,908
1,088,922
>9.68
>> log buffer space 3,284
222,604
>1.98
>>
>> 2002/05/28
>> Top 5 Wait Events
>> ~~~~~~~~~~~~~~~~~ Wait
%
>Total
>> Event Waits Time (cs)
Wt
>Time
>> -------------------------------------------- ------------
------------ ---
>----
>> log file sync 2,247,585
20,529,779
>63.71
>> db file parallel write 441,052
4,377,899
>13.59
>> log file parallel write 1,724,089
3,806,535
>11.81
>> db file sequential read 8,854,525
2,044,020
>6.34
>> enqueue 78,759
592,411
>1.84
>>
>> 2002/07/04
>> Top 5 Wait Events
>> ~~~~~~~~~~~~~~~~~ Wait
%
>Total
>> Event Waits Time (cs)
Wt
>Time
>> -------------------------------------------- ------------
------------ ---
>----
>> log file sync 3,838,694
13,158,371
>63.28
>> db file sequential read 2,189,863
2,401,275
>11.55
>> log file parallel write 3,401,035
2,098,018
>10.09
>> db file parallel write 97,086
1,503,608
>7.23
>> enqueue 71,251
432,706
>2.08
>>
>> 2002/07/11
>> Top 5 Wait Events
>> ~~~~~~~~~~~~~~~~~ Wait
%
>Total
>> Event Waits Time (cs)
Wt
>Time
>> -------------------------------------------- ------------
------------ ---
>----
>> log file sync 453,862
9,679,513
>68.11
>> db file sequential read 500,157
1,660,982
>11.69
>> db file parallel write 67,235
1,137,017
>8.00
>> log file parallel write 163,180
1,075,201
>7.57
>> log file sequential read 32,012
254,327
>1.79
>>
>>
>>
>>
>> As you can see, log file sync is a big bottleneck, and i have to
solve
>this problem.
>> I think the best solution maybe convert to dedicated raid1 or single
disk
>with oracle multiplex, but this need to rebuild the whole disk array ,
need
>too much downtime, and with some risk.
>> I tried to disable CachedQuick IO on the redo log file, but did not
see
>performance gains.
>> Veritas Said quick io is as fast as RAW, but i did not tested it
before, i
>wonder whether move to raw for redo log will helps?
>> It is difficult to modify the application to reduce the frequency of
>commit, for every transaction finishes, it have to commit.
>>
>> Another strange question is that: from the iostat result and sar -d
>result, there is not much write to the disk array, maybe
2Mbytes/second, and
>it causes so much wait( average 10%time wait during normal workload).
Maybe
>some misconfiguration in the disk array?
>> Thanks for everyone reading and answering, please give me some
suggestions
>>
>>
>>
>>
>> Good luck!
>>
>> chaos
>> chaospku_at_163.net
>>
>> zhu chao
>> DBA of Eachnet.com
>> 86-021-32174588-667
>>
>>
>>
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.com
>> --
>> Author: chaos
>> INET: chaospku_at_163.net
>>
>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>> San Diego, California -- Public Internet access / Mailing
Lists
>> --------------------------------------------------------------------
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from). You may
>> also send the HELP command for other information (like subscribing).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Bunyamin Karadeniz
> INET: bunyamink_at_havelsan.com.tr
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: chaos
  INET: chaospku_at_163.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).