Redo Log Buffer sizing

[MCubitt]

An Oracle health check app (Toad) reports "The redo log buffer appears to be too large".

"...On most systems, sizing the log buffer larger than 1 MB 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."

It states they are 2MB but I actually have 5 x 50Mb files. Is this really so bad?




Applications Support
UK

 

[SantaMufasa]

Martin,

There is really no relationship between the size of the redo log buffer and the on-line redo log files. The references you read are absolutely correct: a redo log buffer that is too large buys you absolutely nothing...in fact, it wastes memory.

Remember, one of the actions that causes the log buffer to dump its contents to the on-line redo log files is a COMMIT. If your log buffer is 2MB, but typically your applications write only 50KB of changes to the log buffer before COMMIT happens, then you are wasting 1,950,000 bytes of memory since you are never using more than 50KB of log buffer.

It's up to you. There is certainly no performance hit by making it too large, but there is no benefit either.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[MCubitt]

Santa,

Thank you for explaining the difference. I so easily get confuddled!

Now.. to find where log buffer is set!

Thanks




Applications Support
UK

 

[SantaMufasa]

Martin,

Size of log buffer is in your init<SID>.ora or spfile (depending upon which method you use for parameter definition). In the parameter file, you set it as:

LOG_BUFFER=<some number in bytes>

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[MCubitt]

Thanks, i found it!

There's the little fella:
log_buffer = 1572864

So that is 1.5Mb. I have changed it ot 1Mb and will see how that goes.

Thanks,

Martin




Applications Support
UK

 

[SantaMufasa]

Not that there is anything wrong with 1MB, but that large system in San Francisco that I told you about in your other thread ("Too Many Datafiles: 13 tablespaces, 34 datafiles. 31Gb, 91 users, 141 concurrent sessions) uses LOG_BUFFER=163840, only 15% of your 1MB, with no problem.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.