PROMON -> R&D -> 3. Other Displays -> 4. CheckpointsThe following type of Checkpoint information is reported:
Ckpt ---------- Database Writes ----------------
No. Time Len Dirty CPT Q Scan APW Q Flushes
518 17:29:12 0 1564 1539 40 10 0
517 17:16:18 774 1614 1598 567 44 0
516 17:07:18 540 2897 2873 305 0 0
The
"Len" or length column in the example above refers to elapsed seconds. It indicates how much time has passed from when a checkpoint began, until it ended.
The column labelled
"Dirty", indicates how many dirty database buffers were on the checkpoint queue at the start of the checkpoint, left over from the previous checkpoint. At the end of this checkpoint these must all have been written to disk, otherwise known as the "Fuzzy".
The zeros in the
"flushed" column, is considered optimal. If any of these database buffers were not written to disk at this time, they'll all be written at the end and eventually counted in the "flushed" column.
The snap shot example above presents a system that has been optimally tuned with respect to checkpoints. The checkpoints are about 5 to 10 minutes apart and all the dirty buffers are being written by the page writers (APW's) in the background as they should be. When they are not, this leads to database performance issues during checkpoints. The following Articles outlines such an example:
In later releases this information has been broken down to account for the contribution of the various housekeeping activities involved with checkpoints.
Specific to this Article are "Len Freq" Columns:
- Len — The length of time required to complete the checkpoint. The checkpoint is not considered completed until all dirty buffers on the checkpoint queue have been written out. This is either done over time by the APWs between checkpoints or is forced at the next checkpoint (DB Writes).
- Freq — The length of time between checkpoint starts
In other words (Freq - Len) is effectively the total 'pause time' while the database and memory are synchronized.
The information on the new Checkpoint Columns is provided in the following Documentation:
