| |
 |
|
Oracle Tips by Burleson |
Tuning Checkpoints
Checkpoints provide for rolling forward after a system crash. Data
is applied from the time of the last checkpoint forward from the
redo entries. Checkpoints also provide for reuse of redo logs. When
a redo log is filled, the LGWR process automatically switches to the
next available log. All data in the now-inactive log is written to
disk by an automatic checkpoint. This frees the log for reuse or for
archiving.
Checkpoints occur when a redo log is filled, when the INIT.ORA
parameter LOG_CHECKPOINT_INTERVAL ORACLE7 is reached (total bytes
written to a redo log), or the elapsed time has reached the INIT.ORA
parameter LOG_CHECKPOINT_TIMEOUT, expressed in seconds, or every
three seconds, or when an ALTER SYSTEM command is issued with the
CHECKPOINT option specified.
While frequent checkpoints will reduce recovery time, they will also
degrade performance. Infrequent checkpoints will increase
performance but also increase required recovery times. To reduce
checkpoints to occur only on log switches, set
LOG_CHECKPOINT_INTERVAL to larger than your redo log size, and set
LOG_CHECKPOINT_TIMEOUT to 0.
If checkpoints still cause performance problems, set the INIT.ORA
parameter CHECKPOINT_PROCESS to TRUE to start the CKPT process
running. This will free the DBWR from checkpoint duty and improve
performance. The INIT.ORA parameter PROCESSES may also have to be
increased. Note that on Oracle8 and later, the checkpoint process is
not optional and is started along with the other Oracle instance
processes.
Another option new with Oracle8i was the concept of fast-start
checkpointing. In order to configure fast-start checkpointing, you
set the initialization parameter FAST_START_IO_TARGET. The
FAST_START_IO_TARGET parameter sets the number of I/O operations
that Oracle will attempt to limit itself to before writing a
checkpoint. This feature is available only with Oracle Enterprise
Edition.
This is an excerpt by
Mike Ault’s book “Oracle
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
