Tuning Redo Log file size and buffer size !!

[Chris Fung]

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

[Massimo Bianchi · Accepted solution]

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]

Take a look at the very good paper from oracle.

[Yogeeraj_1]

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