Checkpoints Due to MTTR - Automatic CKPT Tuning

How do we implement below findings from ADDM ?

 

Finding : Checkpoints Due to MTTR

Impact is .75 active sessions, 38.53% of total activity.

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

Buffer cache writes due to setting of the obsolete parameters "fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout" were consuming significant database time.

   Recommendation : Database Configuration    Estimated benefit is .75 active sessions, 40.53% of total activity.

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

   Action:

      Oracle's recommended solution is to control MTTR setting using the "fast_start_mttr_target" parameter instead of the "fast_start_io_target", "log_checkpoint_interval" and

      "log_checkpoint_timeout" parameters.

   Symptoms That Led to the Finding:

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

      The throughput of the I/O subsystem was significantly lower than expected.

      Impact is .26 active sessions, 24.52% of total activity. Wait class "User I/O" was consuming significant database time. Impact is .75 active sessions, 48.53% of total activity.

                

Determining the time to recover from an instance failure is a necessary component for reaching required service levelsagreements. For example, if service levels dictate that when a node fails, instance recovery time can be no more than 3 minutes, FAST_START_MTTR_TARGET should be set to 180.

 

Fast-start checkpointing refers to the periodic writes by the database writer (DBWn) processes for the purpose of writing changed data blocks from the Oracle buffer cache to disk and advancing the thread-checkpoint. Setting the database parameter FAST_START_MTTR_TARGET to a value greater than zero enables the fast-start checkpointing feature.

 

 

Since the observation in the ADDM states that "Buffer cache writes due to setting of the obsolete parameters "fast_start_io_target", "log_checkpoint_interval" and "log_checkpoint_timeout" were consuming significant database time. ", need to set the MTTR for the database writer (DBWn) processes for the purpose of writing changed data blocks from the Oracle buffer cache to disk and advancing the thread-checkpoint. .

 

Hence suggested to set the parameter "fast_start_mttr_target" as follows.

 

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.

 

Enable MTTR advisory  :

Enabling MTTR Advisory Enabling MTTR Advisory involves setting two parameters:

 

STATISTICS_LEVEL = TYPICAL

FAST_START_MTTR_TARGET > 0

 

Estimate the value for FAST_START_MTTR_TARGET as follows:

 

SELECT      TARGET_MTTR,      ESTIMATED_MTTR,      CKPT_BLOCK_WRITES

  FROM     V$INSTANCE_RECOVERY;

 

TARGET_MTTR         ESTIMATED_MTTR          CKPT_BLOCK_WRITES

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

        214                                12                                      269880

 

FAST_START_MTTR_TARGET = 214;

 

Whenever you set FAST_START_MTTR_TARGET to a nonzero value, then set the following parameters to 0.

LOG_CHECKPOINT_TIMEOUT = 0

LOG_CHECKPOINT_INTERVAL = 0

FAST_START_IO_TARGET = 0

 

Disable MTTR advisory  :

~~~~~~~~~~~~~~~~~~~

 

FAST_START_MTTR_TARGET = 0

LOG_CHECKPOINT_INTERVAL = 200000

Comments