what size shoul online redo logs be?

[GERPAT]

I have an Oracle 8i database running on Windows NT. Is there a formula I could use to work out the optimumn size of the online redo redo logs.

 

[jimbopalmer]

If you use hardware RAID, there might be. I use hardware RAID on my Solaris databases and it includes 24 meg of NVRAM cache. Therefore if I keep my redos under 24 meg I get instant response from the RAID box that my redo is saved. (since the RAM is NonVolatile, a power failure of under 2 years still allows my redo to be written to disk) Arranging your database so all writes happen at the speed of RAM not the speed of read/write heads is a big speed up in response time.

I tried to remain child-like, all I acheived was childish.

 

[SantaMufasa]

GerPat,

Oracle's typical rule of thumb is to size your redo logs so that they generally switch every 20 minutes. As we saw yesterday in a thread, it took 19 minutes for a relo log switch to complete archiving and checkpointing (those particular log files were 500MB). So, size is activity dependent.

If log switches occur much more frequently than 20 minutes, your performace begins to suffer.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:13 (03Dec03) GMT, 09:13 (03Dec03) Mountain Time)

 

[GERPAT]

thanks for all your responses. Mufasa, any ideas how I can check how frequent log switches are happening.

 

[carp]

Try this:

SELECT first_time
FROM v$log_history
ORDER BY 1;

This will show you the times of the first entries in each redo log file.

 

[SantaMufasa]

Carp's suggestion is excellent. You can also check log switches the old-fashioned way: Look in your alert<SID>.log.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:11 (03Dec03) GMT, 11:11 (03Dec03) Mountain Time)

 

[GERPAT]

ok thanks everyone!

 

[carp]

Here's a more useful query:

SQL> select avg(1440*(t2.first_time - t1.first_time)) avg_minutes
2 from v$log_history t1, v$log_history t2
3 where t1.recid = t2.recid - 1;

AVG_MINUTES
-----------
55.9727912

Of course, if your database is not very active, first_time may not necessarily be &quot;close&quot; to the time of the log switch. You might want to take Dave's advice and compare the first_time values to the times recorded in your alert log to see if they are about the same or not.