Avoid Frequent Checkpoints

A frequent checkpoints degraded the perform of the database, but what can we do to solve it?

• Check the size of the redo log file size and increase the size if it is small.
• Set the FAST_START_MTTR_TARGET parameter as per the advice given by the MTTR Advisor.

Increase the size of the redo log:

The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes.

Generally, larger redo log files provide better performance, however it must balanced out with the expected recovery time. Undersized log files increase checkpoint activity and increase CPU usage.

MTTR Advisor:

SELECT * FROM V$MTTR_TARGET_ADVICE;

The Estimated MTTR Oracle metric is the current estimated mean time to recover (MTTR) in the number of seconds based on the number of dirty buffers and log blocks.

The MTTR was developed to measure the amount of downtime that will be incurred should there be a catastrophic failure of some component within the computer system. Of course, for continuously available systems the MTTR should be equal to zero.

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

Enabling MTTR Advisory 

Enabling MTTR Advisory involves setting two parameters:

alter system SET STATISTICS_LEVEL=TYPICAL SCOPE=BOTH;
alter system SET FAST_START_MTTR_TARGET > 0 SCOPE=BOTH;