Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Tuning Redo Log file size and buffer size !!

SOLVED
Go to solution
Chris Fung
Frequent Advisor

Tuning Redo Log file size and buffer size !!

Hi all,

I think I am currently hitting a performance issue for the redo log !!

We are running a data warehouse (Oracle 8.1.7) with a long running night batch job. I found there are quit a lot log switch in the alert log file......and I am thinking to increase the size of the redo log files as well as the redo log buffer.

Any suggestion on the starting point for these values ? I am thinking to resize the redo log file from 20M to at least 100M. While change to redo log buffer to around 2M or even bigger. (We have 2GB physical memory).

Any comments ?

Many thanks,

Chris
3 REPLIES
Massimo Bianchi
Honored Contributor
Solution

Re: Tuning Redo Log file size and buffer size !!

hi
a log switch every two minutes is bad form performance.

Rule of thumb is a switch every 15/20 minute, so i would go to a 150/200M redolog size.

If you do not want to size them so big in just one time, begin with 100M and after go on.

For the log buffer it may impact less.
I would no go over 4M, because of speed in writing the buffer to disk.
Try with 2M as a start. You 2G of memory is not an issue, is just 2 to 4 Mb of RAM plus current config.

Remember that, if your instance is in archivelog mode, you may have to enlarge the archive log destination.


If you have doubt on the procedure for the redo:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xbc7f3f9a15add711abdc0090277a778c,00.html

HTH,
Massimo
twang
Honored Contributor

Re: Tuning Redo Log file size and buffer size !!

Take a look at the very good paper from oracle.
Yogeeraj_1
Honored Contributor

Re: Tuning Redo Log file size and buffer size !!

hi,

In general, if you are trying to get your redo logs to switch less frequently (although a steady state at 20 minutes is perfection), you would have to either:

o reduce the number of transactions you execute or change the amount of data they affect
o increase the size of your online redo log.


I prefer to start with 3 groups of 2 members of 25meg a piece. Then work up from there.

You should add more groups or make the members larger until you have enough online redo log to get through at least 15-20 minutes of work without switching.

As mentioned above you should choose you time between 15-20 minutes.


Since you have already determinded your redo generation rate and use that to size it.

I would add logs until it goes away though not scientific but real effecient.

Concerning redo log buffers,I prefer to see them in the 1/4-1/2 MB range to keep a steadier flow of data being written to the online redo log (over buffering data is just as bad as not caching at all in many cases).

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)