Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: November 19, 2015 - 11:03 am UTC

Last updated: October 06, 2016 - 12:57 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hello

In looking into how to tune log_buffer, I came across two different warnings. One claims that if the log buffer is too small, we will get a significant amount of "log buffer space." The solution is to "consider making the log buffer bigger if it is small."

However, if we set it too "big" it can also result in performance problems. they advise: "Ensure that the log_buffer is not too big. A very large log_buffer can have an adverse affect as waits will be longer when flushes occur. When the buffer fills up, it has to write all the data into the redo log file and the LGWR will wait until the last I/O is completed." Doc ID 1376916.1

If it is too "small", we will see "log buffer space" waits. What kind of waits will we see if it is too "big"?

Thanks!

and Connor said...

Both pieces of advice are

a) correct
b) potentially out of date

:-)

The amount of internal changes to enhance redo log buffer management in recent releases is jaw dropping. There is now private vs public redo memory structures, different latching mechanisms etc etc.

My advice - *dont* set the log_buffer parameter. The database will choose an appropriate default based on server spec.

I've never seen a issue with an (unset) log buffer/ redo log system unless there were extenuating circumstances (eg box max'd out on CPU, or storage issues)

Hope this helps.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

John Cantu, November 22, 2015 - 11:35 pm UTC

thank you, Conner. I will not set a size from now on.

BTW: I noticed something odd on in my AWR report. it seems that AWR shows that the log_buffer is sized at 18MB when I set it just under 5MB.

I have the following set:

log_buffer 5005312
sga_target 6442450944

However, in the AWR section:

SGA breakdown difference
• ordered by Pool, Name
• N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot


Pool

Name Begin MB End MB % Diff
...
buffer_cache 4,528.00 4,528.00 0.00
fixed_sga 2.14 2.14 0.00
log_buffer 18.43 18.43 0.00

I set it explicitly to 5MB, but Oracle is choosing to set it to 18MB. Isn't that odd? what is going on here?

Thanks,

John
Connor McDonald
November 23, 2015 - 12:11 am UTC

My understanding is that there is a minimum size it can be dependent on the OS, number of cores, etc.

Juan Cantu, November 23, 2015 - 12:31 pm UTC

Thanks, Connor. That makes sense. The documentation isn't clear on that, though.

It should state that the value will be ignored if it is set too small.

Connor McDonald
November 23, 2015 - 12:33 pm UTC

One of the cool things with the online documentation, is that you can provide feedback on it directly to the documentation team.

being sent to battle with faulty knowledge

John Cantu, November 23, 2015 - 1:19 pm UTC

Hello Connor,

You may recall that I quoted a statement from Doc ID 1376916.1 that states that sizing the log buffer to larger could have performance problems. I just read on the Oracle Online documentation. This is what it states:

“Sizing the Log Buffer
....
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.” Thoughts on this? This is what I believed to be the case.


Connor McDonald
November 24, 2015 - 1:17 am UTC

(In my opinion) thats outdated advice. A *lot* has changed with memory since the days of a 1mb log_buffer. Probably the most dramatic one is the segmentation of the SGA into chunks (or 'granules') which is one of the things that facilitated the ability to dynamically resize it etc.

I havent checked recently, but I'm pretty sure that log_buffer is (by default) sized at 1 granule (minus whats needed for the fixed SGA). That granule is in effect then not considered for any resize or non-fixed/log_buffer style operations.

So ... just dont set it :-)

AWR lagging behind the times?

A reader, October 05, 2016 - 1:30 pm UTC

If we shouldn't set the size of the log_buffer, should the AWR reports stop telling us to set it? This is an extract from an AWR on 12c generated very recently.

Finding 5: Undersized Redo Log Buffer
Impact is .62 active sessions, 4.32% of total activity.
-------------------------------------------------------
Waits for redo log buffer space were consuming significant database time.

   Recommendation 1: Database Configuration
   Estimated benefit is .62 active sessions, 4.32% of total activity.
   ------------------------------------------------------------------
   Action
      Increase the size of the redo log buffer by setting the value of
      parameter "log_buffer" to 32 M.
   Rationale
      The value of parameter "log_buffer" was "4194304" during the analysis
      period.

Connor McDonald
October 06, 2016 - 12:57 am UTC

We choose a *default* size for the log buffer, which should be suitable for *most* workloads.

This isnt a prohibition on setting it :-)

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.