How big are your redo logs and how big is your log buffer?
Regards
HJR
"chao_ping" <chao_ping_at_163.com> wrote in message
news: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
>
> 2002/07/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/08/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/09/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/10/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
>
>
>
> --
> an oracle fan,
> an oracle beginner
>
> Posted via dBforums
> http://dbforums.com
Received on Fri Jul 12 2002 - 02:46:55 CDT
