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.
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).
Received on Fri Jul 12 2002 - 04:48:21 CDT
