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
Post a Comment