Malesh Gummula

Introduction :

I noticed mostly DBAs has a confusion regarding redo log switch and checkpoint. With this articles i tried to explain the concept of checkpoint.

· What is the Role of checkpoint ?

· When checkpoint occurs ?

· What should be frequency of checkpoint ?

· How do we set the value of LOG_CHECKPOINT_INTERVAL ?

· What does the LOG_CHECKPOINT_TIMEOUT value means ?

· What is the significance of LOG_CHECKPOINT_TO_ALERT ?

· "checkpoint not complete ".

What is the Role of checkpoint ?

Checkpoint synchronizes the datablock in memory with the datafiles on disk. When a checkpoint occurs , DBWR writes all modified database blocks from the buffer cache to datafiles.

(BUFFER CACHE)

DBWR -----------------------------------> DATAFILES

[ Modified database blocks ]

LGWR also updates both the controlfile and datafiles to indicate last SCN (checkpoint)

(Record Last SCN no)

LGWR -----------------------------------> CONTROLFILE & DATAFILES

CKPT process performs the operation of LGWR if it is enabled. CKPT is enable as default in version 8.0

When checkpoint occurs ?

Checkpoint occurs

1) At redo log switch

2) LOG_CHECKPOINT_TIMEOUT has expired

3) LOG_CHECKPOINT_INTERVAL has reached.

4) Forcefully by DBA

What should be frequency of checkpoint ?

Depending on the number of datafiles in a database, a checkpoint can be highly resource intensive. More number of checkpoint reduce recovery time at the time of crash since less redo need to be reapplied , but causes an impact on performance because of system overhead.

In short if downtime is very vary critical , checkpoint should be raised frequently otherwise your goal should be to reduce its frequency to enhance the better performance.

How do we set the value of LOG_CHECKPOINT_INTERVAL ? :

Log_checkpoint_interval is defined in terms of OS Block size.

Suppose the following setting is defined in your database environment :-

LOG_CHECKPOINT_INTERVAL = 10000

OS block size = 512 bytes

Redo log file Size = 20M

Calculation of checkpoint = (20 * 1024*1024)/(512*10000) = 4

Four checkpoint will be raised per redo log file . One checkpoint will be raised when (10000*512) bytes has to be written by the LGWR from cache to redo log files.

If you will define log_checkpoint_interval = 0 (zero) means you are setting this value to infinity. and causes the parameter to be ignored.

If the value of the (log_checkpoint_interval * OS BLOCKSIZE) > redolog file size , checkpoint will occur at the redolog switch

You can set this parameter dynamically by alter system as

SQL> ALTER SYSTEM SET LOG_CHECKPOINT_INTERVAL=100000;

What does the LOG_CHECKPOINT_TIMEOUT value means ?

Log_checkpoint_timeout value is define in terms of seconds in initsid.ora file.

For example LOG_CHECKPOINT_TIMEOUT = 1800 # 1800 sec default value in 8i and 9i enterprise edition #

  In Oracle 9i ,the checkpoint raised from the position where the last write to the  redo log was   1800 seconds (defined as above)  ago

. It also points that no  buffer will remain dirty for more than 1800 seconds.

In Oracle 8i or earlier versions , checkpoint raises based upon the number of seconds that have passed since the last checkpoint.

For example if you define LOG_CHECKPOINT_TIMEOUT = 180 causes checkpoint to be raised after every 180 seconds.

Setting this value to 0 disable the parameter.

You can set this parameter dynamically by alter system as

SQL> ALTER SYSTEM SET LOG_CHECKPOINT_TIMEOUT = 2400;

What is the significance of LOG_CHECKPOINT_TO_ALERT ?

Log_checkpoint_to_alert parameter is boolean type and its default value is FALSE. By setting this parameter to TRUE allows you to log checkpoint start and stop times in the alert log to determine the checkpoints activity.

Somtimes there is an error in an alert log file "checkpoint not complete ".What does it indicate ?

It means Oracle is ready to recycle the redo logs but it can not because the checkpoint in the previous log is still in progress

Also you can query the v$sysstat system view to determine the value of
background_checkpoint_completed and background_checkpoint_started as

SQL > select name,value from sys.v$sysstate where name like 'background checkpoints%';

if the difference of these two values are grater than 1 you must do

1) Add more redo log groups or increase the size of redo logs.

2) Reduce the frequency of checkpoints by increasing LOG_CHECKPOINT_INTERVAL.

Conclusion :

Checkpoint plays a very important role in database activity. So DBAs must have to monitor its efficiency during peak database activities. Log switches cause a checkpoint, but checkpoint does not cause a log switch

Reference:

Oracle Server Reference manual

Oracle Concept

Matalink DOC ID 1036517.6

Thanks to you for reading this article . Please feel free to send me your comments or feedback at g[email protected]

Malesh Gummula

References

Oracle Server Reference

Oracle Concept

Metalink DOC ID 1036517.6

Good Luck By

Malesh Gummula