RelationalDBDesign RelationalDBDesign

Database Architecture   «Prev 

Oracle Checkpointing

Every change made to an Oracle database is assigned a unique number. This number is included in the information written to the redo log files. As Oracle writes data blocks back to the datafiles, it keeps track of the highest change number that has been written to disk. Periodically, usually every 3 seconds, this number is recorded in the database control file. This creates a checkpoint.
Redo log records for changes prior to the checkpoint are no longer needed for recovery. When a log switch occurs, and Oracle prepares to write to the next redo log group, it first compares the checkpoint in the control file to the change numbers in the redo log file.
As long as the checkpoint is greater than or equal to the highest change number contained in the log file, the log file can be safely reused.
If the checkpoint was lower than the change number, that would imply that the checkpoint process had not finished flushing out changes from the buffer cache to the datafiles. Oracle would wait until the checkpoint finished before allowing any other changes to be made. Oracle would also place a “checkpoint not complete” message into the database's alert log file. This has an impact on performance.

Checkpointing Described

checkpointing: To understand how online redo logs are used, you will need to know something about checkpointing, how the database buffer cache works, and what a process called Database Block Writer (DBWn) does. The database buffer cache and DBWn are covered in more detail a later on, but we will skip ahead a little anyway and touch on them now.
  1. Lots of users modifying the same blocks: Here you might want large redo log files. Since everyone is modifying the same blocks, you would like to update them as many times as possible before writing them out to disk. Each log switch will fire a checkpoint, so you would like to switch logs infrequently. This may, however, affect your recovery time.
  2. Mean time to recover: If you must ensure that a recovery takes as little time as possible, you may be swayed toward smaller redo log files, even if the previous point is true. It will take less time to process one or two small redo log files than a gargantuan one upon recovery. The overall system will run slower than it absolutely could day to day perhaps (due to excessive checkpointing), but the amount of time spent in recovery will be shorter. There are other database parameters that may also be used to reduce this recovery time, as an alternative to the use of small redo log files.