Oracle7 Server Tuning
A checkpoint is an operation that Oracle performs automatically. Checkpoints can momentarily reduce performance. This section tells you
- how checkpoints affect performance
- how to choose the frequency of checkpoints
- how to reduce the performance impact of a checkpoint
How Checkpoints Affect Performance
Checkpoints affect
- recovery time performance
Recovery Time Performance
Frequent checkpoints can reduce recovery time in the event of an instance failure. If checkpoints are relatively frequent, then relatively few changes to the database are made between checkpoints. In this case, relatively few changes must be rolled forward for recovery.
Runtime Performance
A checkpoint can momentarily reduce runtime performance for these reasons:
- If CKPT is not enabled, checkpoints cause LGWR to update data files and may momentarily prevent LGWR from writing redo entries.
The overhead associated with a checkpoint is usually small and only affects performance while Oracle performs the checkpoint.
Choosing Checkpoint Frequency
You should choose a checkpoint frequency based on your performance concerns. If you are more concerned with efficient runtime performance than recovery time, choose a lower checkpoint frequency. If you are more concerned with fast recovery time than runtime performance, choose a higher checkpoint frequency.
Because checkpoints on log switches are necessary for redo log maintenance, you cannot eliminate checkpoints entirely. However, you can reduce checkpoint frequency to a minimum by setting these parameters:
Such settings eliminate all checkpoints except those that occur on log switches.
You can further reduce checkpoints by reducing the frequency of log switches. To reduce log switches, increase the size of your redo log files so that the files do not fill as quickly.
Reducing the Performance Impact of a Checkpoint
You may notice a momentary drop in performance as a checkpoint completes. This drop may be due to an accumulation of redo entries in the redo log buffer. LGWR may be too busy updating data file headers to write these entries to a redo log file. In this case, you can reduce the performance impact of a checkpoint by enabling the Checkpoint process (CKPT).
CKPT updates datafile headers when a checkpoint occurs, leaving LGWR free to write redo entries. To enable CKPT, set the value of the initialization parameter CHECKPOINT_PROCESS to TRUE. To disable CKPT, set this value to FALSE. CKPT is disabled by default. Before enabling CKPT, be sure that your operating system can support an additional process. You may also need to increase the value of the initialization parameter PROCESSES.