Tune Checkpoint

Checkpoint

A Checkpoint is a database event which synchronizes the modified data blocks in memory with the datafiles on disk

Oracle writes the dirty buffers to disk only on certain conditions

-A shadow process must scan more than one-quarter of the db_block_buffer parameter.
-Every three seconds.
-When a checkpoint is produced.

A checkpoint is realized when
-Redo switches
-when (LOG_CHECKPOINT_INTERVAL* size of IO OS blocks) is written to redo logfile
-LOG_CHECKPOINT_TIMEOUT is reached
-ALTER SYSTEM CHECKPOINT command


A checkpoint performs the following three operations:
-Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.
It's the DBWR that writes all modified databaseblocks back to the datafiles.
-The latest SCN is written (updated) into the datafile header.
-The latest SCN is also written to the controlfiles.

Tuning checkpoints involves four key initialization parameters

- FAST_START_MTTR_TARGET
- LOG_CHECKPOINT_INTERVAL
- LOG_CHECKPOINT_TIMEOUT
- LOG_CHECKPOINTS_TO_ALERT



SELECT SUBSTR(NAME,1,30) nme , SUBSTR(VALUE,1,50) value
from v$parameter
where name in ('log_checkpoint_interval','log_checkpoint_timeout','fast_start_io_target','fast_start_mttr_target');





FAST_START_MTTR_TARGET :Enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. Based on internal statistics, incremental checkpoint automatically adjusts the checkpoint target to meet the requirement of FAST_START_MTTR_TARGET.
You can select V$INSTANCE_RECOVERY the status of the estimated and target MTTR

select ESTIMATED_MTTR,TARGET_MTTR from V$INSTANCE_RECOVERY;

from above query we can decide the initial fast_start_mttr_target value

alter system set log_checkpoint_interval=0 scope=both;
alter system set log_checkpoint_timeout=0 scope=both;
alter system set fast_start_io_target=0 scope=both;
alter system set fast_start_mttr_target=30 scope=both; #30 is example
alter system set log_checkpoints_to_alert = true;

Then query the v$MTTR_TARGET_ADVICE in order to find optimal fast_start_mttr_target


Note:When you enable fast-start checkpointing, remove or disable (set to 0)
the following initialization parameters:
- LOG_CHECKPOINT_INTERVAL
- LOG_CHECKPOINT_TIMEOUT
- FAST_START_IO_TARGET


LOG_CHECKPOINT_TIMEOUT:LOG_CHECKPOINT_TIMEOUT specifies the amount of time, in seconds, that has passed since the incremental checkpoint at the position where the last write to the redo log occurred.

LOG_CHECKPOINT_INTERVAL :specifies the maximum number of redo blocks the incremental checkpoint target should lag the current log tail.
If FAST_START_MTTR_TARGET is specified, LOG_CHECKPOINT_INTERVAL should not be set or set to 0.
On most Unix systems the operating system block size is 512 bytes. This means that setting LOG_CHECKPOINT_INTERVAL to a value of 10,000 would
mean the incremental checkpoint target should not lag the current log tail by more than 5,120,000 (5M) bytes. . If the size of your redo log is 20M, you are taking 4 checkpoints for each log.

LOG_CHECKPOINTS_TO_ALERT
LOG_CHECKPOINTS_TO_ALERT lets you log your checkpoints to the alert file. Doing so is useful for determining whether checkpoints are occurring at the desired frequency.

REDO LOG NUMBER AND SIZE
A checkpoint occurs at every log switch. If a previous checkpoint is already in progress, the checkpoint forced by the log switch will override the current checkpoint
so look alert.log or guery v$log_history the occurance of log switch
If redo logs switch every 3 minutes, you will see performance degradation. This indicates the redo logs are not sized large enough to efficiently handle the transaction load.

CHECK ERROR MESSAGES IN ALERT.LOG
Check “Cannot allocate new log” and “Checkpoint not complete” messages in alert.log .
This situation may be encountered if DBWR writes too slowly, or if a log switch happens before the log is completely full,
or if log file sizes are too small.
When the database waits on checkpoints,redo generation is stopped until the log switch is done.
tune the log number and size

SYSTEM WAITS
we can query the system waits from v_$system_event or from statspacks or from 10g awr reports if log switch errors happed

select
substr(e.event, 1, 40) event,
e.time_waited,
e.time_waited / decode(
e.event,
'latch free', e.total_waits,
decode(
e.total_waits - e.total_timeouts,
0, 1,
e.total_waits - e.total_timeouts
)
) average_wait
from
sys.v_$system_event e
where event like 'log file switch%';