RE: log_buffer

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]).

Regards,

mwf

-----Original Message-----
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
To: jhowerton_at_uabmc.edu
Cc: oracle list
Subject: Re: log_buffer

jim,

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:


> DBA's,

>

> 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

Class


> ------------------------------ ------------ ----------- ---------

--------------


> log file sync                       356,123     113,472     64.41

Commit


> log buffer space                     35,114      26,771     15.20

Configuration


> buffer busy waits                    21,139      13,489      7.66

Concurrency


> CPU time                                          7,996      4.54

> gcs drm freeze in enter server      564,623       5,753      3.27

Other


>

> TIA

> ...JIM...

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l