Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter