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" <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] > > zhu chao > DBA of Eachnet.com > 86-021-32174588-667 > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: chaos > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
