Re: log_buffer sizing
Date: Mon, 15 Aug 2011 22:12:26 +0100
Message-ID: <CAMHX9JJVz7+oTSJ8e8gYgyqcSo=6Je+kWDSKV_be+7R+YtMZhg_at_mail.gmail.com>
Oops, I think just said something stupid. Please ignore it (it's crap :) Not sure where it came from... maybe I mixed a couple of different issues together and come up with this new "problem".
I just tested yes indeed you still can increase your log buffer size across SGA granule boundary sizes (unlike fixed arrays like the ones underlying v$session / v$process, but that's another story). It's just that Oracle will automatically increase the log buffer size up to the next granule boundary. So if your SGA granule size is 16M, and 5M is taken by fixed SGA, log buffer will take 11MB (to fill the last granule which would be otherwise left half-unused). If you set log_buffer to 20MB, then you'll be using the first granule (with SGA in it), but as everything wouldn't fit into a single granule, then the next 16 MB is taken as well and filled with log buffer.
One reason which prompted me to write the previous reply is that if you set the log_buffer lower, then Oracle may not obey that really and still use all the available memory (after fixed SGA has been allocated) in the first granule for log buffer ... on the other hand, if you only look into V$SGA and V$SGAINFO etc views, you may not see how much of that allocated memory is actually used for redo log buffer data... you should also look into X$KCRFSTRAND for the full picture (look into strand_size_kcrfa column for example and you'll have to know how to ignore the private strands as these live inside shared pool).
Anyway, I don't care about things like log buffer size nowadays as the default is usually enough, fix the root cause instead (why isn't LGWR able to process the redo & write it to disk fast enough).
-- Tanel Poder New seminars: http://blog.tanelpoder.com/seminar/ On Mon, Aug 15, 2011 at 9:19 PM, Venkat Krish <venkat.lear_at_gmail.com> wrote:Received on Mon Aug 15 2011 - 16:12:26 CDT
> Tanel -
>
> >>You can't tune log buffer on 11g (and some 10g versions) anymore as the
> buffer size is calculated automatically
> Can you throw some more light on the above statement?
>
> 11.2 docs<http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/memory.htm#PFGRF94347>states this
>
> ..Increase the size of the redo log buffer, if necessary, by changing the
>> value of the initialization parameter LOG_BUFFER. The value of this
>> parameter is expressed in bytes. Alternatively, improve the checkpointing or
>> archiving process.
>>
>
> Thanks,
> Venkat
>
>
> On Mon, Aug 15, 2011 at 4:04 PM, Tanel Poder <tanel_at_tanelpoder.com> wrote:
>
>> You can't tune log buffer on 11g (and some 10g versions) anymore as the
>> buffer size is calculated automatically and is related to your SGA granule
>> size.
>>
>> The undocumented way for increasing the log buffer is to increase the SGA
>> granule size by increasing the _ksmg_granule_size parameter, but you
>> probably don't need to do this.
>>
>> I would look into this only if I saw the "log buffer space" wait event
>> taking significant part of the response time and even then, before
>> increasing the buffer, I would drill down deeper into the root cause of the
>> problem - why can't LGWR cope with the redo volume. Snapper on LGWR session
>> is a good starting point. The main reasons are:
>>
>> 1) Slow IO - writes into redolog file can't be done fast enough to cope
>> with the incoming redo volume
>> 2) LGWR not getting enough CPU time for doing its work
>>
>> --
>> Tanel Poder
>> New seminars:
>> http://blog.tanelpoder.com/seminar/
>>
>> On Mon, Aug 15, 2011 at 8:54 PM, K R <kp0773_at_gmail.com> wrote:
>>
>>> sorry for not mentioning earlier it is on 11.2.0.2 running on solaris
>>>
>>>
>>> On Mon, Aug 15, 2011 at 12:37 PM, Venkat Krish <venkat.lear_at_gmail.com>wrote:
>>>
>>>> what version? Hopefully you are employing batch commits.
>>>>
>>>> from 11.2 docs<http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/memory.htm#PFGRF94347>
>>>>
>>>> *A reasonable first estimate for such systems is to the default value,
>>>> which is:*
>>>> * *
>>>>
>>>> *MAX(0.5M, (128K * number of cpus))*
>>>>
>>>> * *
>>>>
>>>> *On most systems, sizing the log buffer larger than 1M does not provide
>>>> any performance benefit. Increasing the log buffer size does not have any
>>>> negative implications on performance or recoverability. It merely uses extra
>>>> memory.*
>>>>
>>>>
>>>> You definitely should look for more convincing evidence (redo related
>>>> wait events) before you go ahead and start tuning log_buffer size
>>>>
>>>> Regards,
>>>> Venkat
>>>>
>>>>
>>>> On Mon, Aug 15, 2011 at 3:22 PM, K R <kp0773_at_gmail.com> wrote:
>>>>
>>>>> All ,
>>>>>
>>>>> my online redo logs are 3G . This is a batch intensive datawarehouse
>>>>> application. What should i be keeping my log_buffer .
>>>>>
>>>>> currently it is set at 29425664 and it just seems too low .
>>>>>
>>>>> thanks
>>>>> Kart
>>>>>
>>>>
>>>>
>>>
>>
>
-- http://www.freelists.org/webpage/oracle-l