Friday 1 April 2016

What causes Checkpoint?


i) The most common reason is redo log switch.
You can switch logfile manually to check this in the alert log.

SQL> alter system switch logfile;
System altered.

ii) Checkpoints can also be forced with the ALTER SYSTEM CHECKPOINT; command. We generally do checkpoint before taking backups. At some point in time, the data that is currently in the buffer cache would be placed on disk. We can force that to happen right now with a user invoked checkpoint.

iii) There are incremental checkpoints controlled by parameters such as FAST_START_MTTR_TARGET and other triggers that cause dirty blocks to be flushed to disk.

Frequent Checkpoints usually means redo log file size is small (and it also means a slow system). But if you increase your redo log files size very high, it will also increase the mean time to recover. So a DBA should determine log file size on the basis of various factors like database type (DSS/OLTP etc), Transactions volume, database behavior as shown in alert log error messages etc.


CKPT actually took one of the earlier responsibility of LGWR. 
LGWR was responsible for updating the data file headers before database release 8.0. But with increasing database size and number of data files this job was given to CKPT process.

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. thanks for sharing your knowledge.
    your post very use full for me .

    I am expecting from you patch and upgradation step by step process.

    Thanks

    ReplyDelete
    Replies
    1. Thanks for your comment Subbarao.
      I will soon post a doc for Upgradation too :)

      Keep Visiting

      Delete