 Oracle
Tips by Burleson
Oracle10g Automated Checkpoint Tuning
Check-pointing is an
important Oracle activity which records the highest system
change number (SCN,) so that all data blocks less than or
equal to the SCN are known to be written out to the data
files. If there is a failure and then subsequent cache
recovery, only the redo records containing changes at SCN(s)
higher than the checkpoint need to be applied during
recovery.
As we are aware, instance
and crash recovery occur in two steps - cache recovery
followed by transaction recovery. During the cache recovery
phase, also known as the rolling forward stage, Oracle
applies all committed and uncommitted changes in the redo
log files to the affected data blocks. The work required for
cache recovery processing is proportional to the rate of
change to the database and the time between checkpoints.
Fast-start recovery can
greatly reduce the mean time to recover (MTTR), with minimal
effects on online application performance. Oracle
continuously estimates the recovery time and automatically
adjusts the check-pointing rate to meet the target recovery
time.
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
Because these initialization
parameters either override fast_start_mttr_target or
potentially drive checkpoints more aggressively than
fast_start_mttr_target does, they can interfere with the
simulation.
|
