 |
|
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.
Get the complete Oracle10g story:
To get the code instantly, click
here:
http://www.rampant-books.com/book_2003_2_oracle10g.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
