Question: Regarding incremental checkpoints,
the Oracle database concepts 11gr2 manual states that an
incremental checkpoint is used to prevent a large flush of the data
buffers are redo log switch time:
"An incremental checkpoint is a type of thread checkpoint partly
intended to avoid writing large numbers of blocks at online redo log
switches. DBWR checks at least every three seconds to determine
whether it has work to do. When DBWn writes dirty buffers, it
advances the checkpoint position, causing CKPT to write the
checkpoint position to the control file, but not to the data file
headers. . . .
During instance recovery, the database must apply
the changes that occur between the checkpoint position and the end
of the redo thread. Some changes may already have been written to
the data files. However, only changes with SCNs lower than the
checkpoint position are guaranteed to be on disk."
Can you explain incremental checkpoints in plain English?
An incremental checkpoint is sort of like when you are sitting on
the toilet taking a large dump and you flush multiple times to
prevent clogging the toilet.
The "fast start" recovery (and
the fast_start_mttr_target) is directly related to the incremental
checkpoint. By reducing the checkpoint time to be more
frequent than a log switch, Oracle will recover and re-start faster
in case of an instance crash.
The docs note that a DBWR writes buffers to disk in advance the
checkpoint position, writing the "oldest" blocks first to preserve
A "checkpoint" is the event that triggers writing of dirty blocks
to the disks and a "normal" checkpoint only occurs with every redo
log file switch.
In a nutshell, an "incremental" directs the CKPT process to
search for "dirty" blocks that need to be written by the DBWR
process. thereby advancing the SCN to the control file.
The DBWR wakes up every 3 seconds, seeking dirty blocks and
sleeps if he finds no blocks. This prevents a "burst" of
writing when a redo log switches.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.