How can I control log switches and checkpoint frequencies?
What are the differences between LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT? I need a clear picture of volume based intervals and time based interval. What are the relations among LOG_CHECKPOINT_TIMEOUT,LOG_CHECKPOINT_INTERVAL and FAST_START_IO_TARGET?
Answer
A checkpoint is when the database synchronizes the dirty blocks in the buffer cache with the datafiles. That is, it writes changed data to disk. The two LOG_CHECKPOINT parameters you mention govern how often this activity occurs.
The heart of the matter is: if the checkpoint occurs infrequently it will take longer to recover the database in the event of a crash, because it has to apply lots of data from the redo logs. On the other hand, if the checkpoint occurs too often the database can be tied up as various background processes become a bottleneck.
The difference between the two is that the INTERVAL specifies the maximum amount of redo blocks which can exist between checkpoints and the TIMEOUT specifies the maximum number of seconds between checkpoints. We need to set both parameters to cater for spikes of heavy activity. Note that LOG_CHECKPOINT_INTERVAL is measured in OS blocks not database blocks.
FAST_START_IO_TARGET is a different proposition. It specifies a target for the number of I/Os required to recover the database. The database then manages its checkpoints intelligently to achieve this target. Again, this is a trade-off between recovery times and the amount of background activity, although the impact on normal processing should be less than badly set LOG_CHECKPOINT paremeters. This parameter is only available withe the Enterprise Edition. It was deprecated in 9i in favour of FAST_START_MTTR_TARGET, and Oracle removed it in 10g. There is a view V$MTTR_TARGET_ADVICE which, er, provides advice on setting the FAST_START_MTTR_TARGET.
We should set either the FAST_START%TARGET or the LOG_CHECKPOINT_% parameters but not both. Setting the LOG_CHECKPOINT_INTERVAL will override the setting of FAST_START_MTTR_TARGET.