Tuning Recovery - Expert Oracle RAC Performance Diagnostics and Tuning - page 325

Redo Log Sizing Advisory
Earlier, we discussed the various factors that influence the recovery times. Among these various factors one item that
stands apart from the rest is the redo log file size. The DBWR and the LGWR activity depend on the redo log file sizes.
Whereas larger redo log files provide better performance, undersized redo log files increase checkpoint activity, thus
increasing workload contentions and higher CPU utilization. There should be an ideal value that will help maintain
performance while at the same time try to reduce the time required to perform instance/crash recovery.
Using the data provided in the OPTIMAL_LOGFILE_SIZE column in V$INSTANCE_RECOVERY view, the redo log file
sizes can be tuned. However, the column is not populated by default by the advisory; it requires setting the parameter
FAST_START_MTTR_TARGET . We now try to understand the effect of setting the right sizes and the suggestions by
the advisor.

1. Determine the current size of the redo log files using the following query:

SELECT group#,
thread#,
sequence#,
bytes,
blocksize,
members,
archived,
status
FROM v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------
1 1 393 52428800 512 2 NO CURRENT
2 1 392 52428800 512 2 NO INACTIVE
3 1 392 52428800 512 2 NO INACTIVE
4 2 355 52428800 512 2 NO CURRENT
5 2 354 52428800 512 2 NO INACTIVE
6 2 354 52428800 512 2 NO INACTIVE

From the output generated by executing the query, its understood that the current size of the redo log file is
50 MB.

2. As discussed earlier, to determine the optimal size of the redo log file size, the

FAST_START_MTTR_TARGET parameter should be set. We check the current value of
this parameter:

SQL> show parameter fast_start

NAME TYPE VALUE
------------------------------------ ----------- -------
fast_start_io_target integer 0
fast_start_mttr_target integer 70
fast_start_parallel_rollback string LOW