Optimum Size of The Online Redo Log Files
The Oracle Database Performance Tuning Guide recommends switching logs at most once every twenty minutes. If our online redo logs switch once every 10 minus during peak hours, our redo logs should be increased to 2 times larger then their current size.
Here’s a simple query for calculating recommended size of redo log files:
|
1 2 3 4 5 6 7 8 9 |
SELECT (SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)", ROUND((20 / AVERAGE_PERIOD) * (SELECT AVG(BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)" FROM (SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE - 3 AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN &START_OF_PEAK_HOURS AND &END_OF_PEAK_HOURS); |