Every DBA knows that the size of their redo logs
is very important. Too small, and the frequent log switches tie-up
the LGWR, ARCH and DBWR background processes. To large, and you
risk loosing data during an instance crash.
Oracle 10g has introduced a new advisory
utility that allows you to specify your optimal mean time to
recovery (MTTR) recovery interval and uses this to suggest the
optimal redo log size. In Oracle 10g the fast_start_mttr_target
parameter is used.
Oracle recommends using the
fast_start_mttr_target
initialization parameter to
control the duration of startup after instance failure. With 10g,
the Oracle database can now self-tune check-pointing to achieve good
recovery times with low impact on normal throughput. You no longer
have to set any checkpoint-related parameters.
This method reduces the time required
for cache recovery and makes the recovery bounded and predictable by
limiting the number of dirty buffers and the number of redo records
generated between the most recent redo record and the last
checkpoint. Administrators specify a target (bounded) time to
complete the cache recovery phase of recovery with the
fast_start_mttr_target
initialization parameter, and
Oracle automatically varies the incremental checkpoint writes to
meet that target.
The target_mttr
field of v$instance_recovery contains the MTTR target in effect. The estimated_mttr field of v$instance_recovery contains the estimated MTTR
should a crash happen right away.
For example,
SQL> SELECT TARGET_MTTR,
ESTIMATED_MTTR, CKPT_BLOCK_WRITES FROM V$INSTANCE_RECOVERY;
TARGET_MTTR
ESTIMATED_MTTR CKPT_BLOCK_WRITES
-----------
-------------- -----------------
37 22 209187
Whenever you set
fast_start_mttr_target to a nonzero value, and while MTTR
advisory is ON, Oracle Corporation recommends that you disable (set
to 0) the following parameters:
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINT_INTERVAL
FAST_START_IO_TARGET
Querying the advisor
In addition to the MTTR information in
v$instance_recovery we also
have an important column called optimal_logfile_size, and we
can query for this value at any time. The value for
optimal_logfile_size is expressed in megabytes and it changes
frequently, based on the DML load on your database. For example,
SQL> SELECT
OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;
OPTIMAL_LOGFILE_SIZE
--------------------
256
If your database is relatively
stable, then you can use this suggested size and rebuild your online
redo log files to match the value. We would expect that a future
version of Oracle will automate this and allow for dynamic re-sizing
of online redo log files, but this is an issues because many Oracle
systems expect the archived redo log files to always be the same
size.
References:
The Oracle
10g docs note on redo log sizing:
"The size of the redo log files can
influence performance, because the behavior of the database
writer and archiver processes depend on the redo log sizes.
Generally, larger redo log files provide better performance.
Undersized log files increase checkpoint activity and reduce
performance.
Although the size of the redo log files
does not affect LGWR performance, it can affect DBWR and
checkpoint behavior. Checkpoint frequency is affected by
several factors, including log file size and the setting of
the FAST_START_MTTR_TARGET initialization
parameter. If the FAST_START_MTTR_TARGET
parameter is set to limit the instance recovery time, Oracle
automatically tries to checkpoint as frequently as
necessary. Under this condition, the size of the log files
should be large enough to avoid additional checkpointing due
to under sized log files. The optimal size can be obtained
by querying the OPTIMAL_LOGFILE_SIZE column
from the V$INSTANCE_RECOVERY view. You can also
obtain sizing advice on the Redo
Log
Groups page of Oracle Enterprise Manager Database
Control.
It may not always be possible to provide a
specific size recommendation for redo log files, but redo
log files in the range of a hundred megabytes to a few
gigabytes are considered reasonable. Size your online redo
log files according to the amount of redo your system
generates. A rough guide is to switch logs at most once
every twenty minutes."
