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


 

HTML Text

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