Monday, April 14, 2008

About Check points and related params


Till Oracle 7.3 we had to set checkpoint_process = true to boost performance during checkpoint processing

From Oracle8i, there is always a checkpoint process. You no longer have the option of not running the checkpoint process and so this parameter is obsolete.



LOG_CHECKPOINT_TIMEOUT specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.

Specifying a value of 0 for the timeout disables time-based checkpoints.

Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.

Oracle Database 10g supports automatic checkpoint tuning.

By default, this feature is not enabled as

It is an advancement over the MTTR related parameter introduced in
earlier versions. The idea is to use the periods of low I/O usage
to advance checkpoints and therefore improve availability.

To enable automatic checkpoint tuning, unset FAST_START_MTTR_TARGET
or set it to a nonzero value. If you set this parameter to zero this feature
will be disabled.
It can be done statically using the initialization files or dynamically using
SQL> alter system set FAST_START_MTTR_TARGET=10;

Case 1: FAST_START_MTTR_TARGET = low value :
Fast-start checkpointing is more aggressive.
The average number of writes per transaction that DBWn issues
is higher in order to keep the thread checkpoint sufficiently
advanced to meet the requested MTTR.
Case 2: FAST_START_MTTR_TARGET =high value:
Fast-start checkpointing in less aggressive, and the average
number of writes per transaction that DBWn issues is lower.
In both cases automatic checkpoint is enabled.
Case 3: FAST_START_MTTR_TARGET is unset:
automatic checkpoint tuning is in effect. Average number of
writes per transaction is reduced but at the same time MTTR
is highest.

No comments:

Post a Comment