Re: log_buffer sizing

  • From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
  • To: Venkat Krish <venkat.lear@xxxxxxxxx>
  • Date: Mon, 15 Aug 2011 22:12:26 +0100

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@xxxxxxxxx> wrote:

> 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@xxxxxxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>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@xxxxxxxxx> 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
>>>>>
>>>>
>>>>
>>>
>>
>

Other related posts: