Databases Reference
In-Depth Information
Tuning checkpoints
A checkpoint is used to ensure consistency in the database; during this operation, all data
files are synchronized with the data blocks in memory.
The process responsible for signaling a checkpoint is the
CKPT
process, which signals the
DBWn
processes to write the dirty (modified) buffers from database buffer cache in memory
to the data files.
During this operation data, file headers and control files are updated to store the last System
Change Number (SCN), to ensure data block consistency.
In this recipe, we will see how to tune checkpoints in an Oracle database, to optimize all these
write operations involved in checkpoints, balancing the trade-off between the redo log size
and recovery time, in case of instance failure.
How to do it...
The following steps will demonstrate checkpoints in an Oracle database:
1.
Connect to the database as
SYSDBA
:
CONNECT / AS SYSDBA
2.
Verify the value for the
LOG_CHECKPOINTS_TO_ALERT
parameter:
SHOW PARAMETER LOG_CHECKPOINTS_TO_ALERT
3.
Alter the
LOG_CHECKPOINTS_TO_ALERT
parameter to trace checkpoints to
the alert log:
ALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT=TRUE SCOPE=SPFILE;
4. Switch the log file to force a checkpoint to occur:
ALTER SYSTEM SWITCH LOGFILE;
5.
Verify the checkpoint event has been traced in the alert log:
!tail /u01/app/diag/rdbms/testdb/TESTDB/trace/alert_TESTDB.log
6.
Query the
V$SYSSTAT
dynamic performance view to monitor checkpoint
process activity:
SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME LIKE 'background check%';
SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME LIKE 'DBWR check%';
