Oracle Database Checkpoint Tuning

Checkpoint Tuning

We know that when checkpoint fires data, It will write data from memory to disk and also checkpoint helps / performs instance recovery.

Enabled MTTR Advisory On database

SQL> alter system set fast_start_mttr_target=1800 scope=both;

System altered.

SQL> show parameter statistics

NAME     TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level     string TYPICAL

SQL> show parameter fast_start_mttr_target

NAME                              TYPE                 VALUE

------------------------------------ ----------- ------------------------------

fast_start_mttr_target     integer              1800

fast_start_mttr_target: Number of seconds database takes to perform crash recovery. i.e The number of seconds DBWR wait to write data from memory

SQL> select TARGET_MTTR, ESTIMATED_MTTR from v$instance_recovery;

TARGET_MTTR    ESTIMATED_MTTR

----------- ----------------------------------------

        13                                      0 

i.e Here TARGET_MTTR < FAST_START_MTTR_TARGET 

SQL> show parameter log_checkpoint

NAME                               TYPE              VALUE

------------------------------------ ----------- ------------------------------

log_checkpoint_interval      integer              0

log_checkpoint_timeout     integer             1800

log_checkpoints_to_alert     boolean          FALSE

SQL> show parameter fast

NAME                                TYPE      VALUE

------------------------------------ ----------- ------------------------------

fast_start_io_target             integer                0

fast_start_mttr_target          integer          1800

fast_start_parallel_rollback     string          LOW

SQL> select MTTR_TARGET_FOR_ESTIMATE,ADVICE_STATUS from v$MTTR_TARGET_ADVICE;

MTTR_TARGET_FOR_ESTIMATE / ADVICE_STATUS

------------------------------------------------------------

      6    ON

      13  ON

      8    ON

      10  ON

      4    ON

NOTE:

we are setting "fast_start_mttr_target"  to set limit instance recovery time 

"log_checkpoint_timeout" should be greater than "fast_start_mttr_target"
"log_checkpoint_timeout" is the maximum amount of time that dirty buffer can stay in memory 
Don't forget that longer and longer checkpoint time takes longer time to perform instance recover and ViceVersa Faster checkpoints gives overhead to database performance. While tuning, make set Optimal time for " log_checkpoint_timeout" on database
But Oracle Recommend us to set "log_checkpoint_interval" than parameter " log_checkpoint_timeout"

--Nikhil Tatineni--

--Performance Tuning--