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); |
With Oracle 10g, we can also use “Redo Logfile Size Advisor”. This advisory utility allows you to specify your optimal mean time to recovery (MTTR) interval and calculates the optimal redo log size. We can query V$INSTANCE_RECOVERY to see the optimal_logfile_size:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
ALTER SYSTEM SET FAST_START_MTTR_TARGET=100; SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY; OPTIMAL_LOGFILE_SIZE -------------------- 2285 ALTER SYSTEM SET FAST_START_MTTR_TARGET=400; SELECT OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY; OPTIMAL_LOGFILE_SIZE -------------------- 11230 |
caesar dutta
Gokhan Atil
narsimulu