Date: Wed, 21 May 2008 07:09:53 -0500
Thanks for the info Mark. We do have peaks of commits, its a vendor applicationn so its difficult but not impossible to get them to change code. I'm working on 15,000 rpm disks for redo log files but it will take a while to convince management.
>>> "Mark W. Farnham" <mwf_at_rsiz.com> 5/20/08 9:45 AM >>>
Increasing your log buffer size may tend to reduce log buffer space requests. A recent post by JL explained some of the system total counting complications when many sessions are simultaneously spewing commits, so I won't repeat it.
The fact that you're generating a significant fraction of space request waits (log buffer space) tends to indicate that you are actually overdriving your i/o, so to the extent your commit rate is peaked, increasing the space may tend to reduce the space waits to the extent it allows caching transaction redo in flux and not yet committed. That's only about 10% of the reported wait count and 20% of the wait time compared to the "log file sync" amounts, but since "log file sync" may tend to be over-reported, there is a good chance you will increase throughput in your case by increasing the buffer. It may exacerbate the reported "log file sync" waits, so you'll have to evaluate whether it improves overall throughput some other way. (Like maybe the elapsed time of your most important individual session action(s); so if the individual important session space waits tend to go down without the individual session log file syncs going up, then you did the right thing. Unless memory is very tight on your machine it won't cost much to give it a bump, like maybe double it as the previous poster suggests.)
If your real problem is commit rate and your load is not peaked, then increasing space probably won't help much and you'll need to figure out how to drain commits faster or accomplish your workload with less of them. Unfortunately you can't make a certain decision from system level aggregates (though a quick look at disk service times on the devices fielding the log writes is probably also worthwhile. If the service times are peaked then log writer is probably periodically fighting with archiver or something else. If they are chronically slow you might want to get the log destinations on some faster media by themselves [if you can't execute your work load with fewer commits]).
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ujang Jaenudin
Sent: Monday, May 19, 2008 10:21 PM
Cc: oracle list
Subject: Re: log_buffer
I never set log_buffer > 10mb
on high oltp system, I set it to 3mb.
mostly log file sync is due to frequent commit and "may be" slow io.
-- thanks and regards ujang | oracle dba jakarta - indonesia On Tue, May 20, 2008 at 5:04 AM, James Howerton <jhowerton_at_uabmc.edu> wrote:Received on Wed May 21 2008 - 07:09:53 CDT
> What is you're log_buffer set to??? I have a 2 Tb oltp two node RAC
database on 10.1.0.5 that is showing lots of log file sync waits. My current setting is 1.5 Mb. Redo log files are 2Gb each.
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~ % Total
> Event Waits Time (s) DB Time Wait
> ------------------------------ ------------ ----------- ---------
> log file sync 356,123 113,472 64.41
> log buffer space 35,114 26,771 15.20
> buffer busy waits 21,139 13,489 7.66
> CPU time 7,996 4.54
> gcs drm freeze in enter server 564,623 5,753 3.27
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l