Masood's Blog

Redo logs and Checkpoint
A checkpoint occurs at every log switch. If a previous checkpoint is already in progress, the checkpoint forced by the log switch will override the current checkpoint.
This necessiates well-sized redo logs to avoid unnecessary checkpoints as a result of frequent log switches.

1. Experts say u must change redologs every 20 minutes.
2. We have a well tuned redolog if we change once per hour.

If we increase the redo files then we can decrease performance by 2 ways:

• Bigger files are slower
• Copying bigger files to archivelog take more time.

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.

A checkpoint is the act of flushing modified, cached database blocks to disk. Normally, when we make a change to a block -- the modifications of that block are made to a memory copy of the block. When we commit -- the block is not written (but the REDO LOG is -- that makes it so we can "replay" the transaction in the event of a failure). Eventually, the system will checkpoint the modified blocks to disk.

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.

                 (DBWR)

BUFFER CACHE-----------------------------> DATAFILES

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

                 (LGWR)

Record SCN Number -------------------------> CONTROLFILE & DATAFILES

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

Checkpoint occurs:

• At redo log switch.
• LOG_CHECKPOINT_TIMEOUT has expired.
• LOG_CHECKPOINT_INTERVAL has reached.
• Forcefully by DBA.

Log_checkpoint_interval is defined in terms of OS Block size.
Suppose the following setting is defined in 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 checkpoints will be raised per redo log file . One checkpoint will be raised when (10000*512) bytes are to be written by the LGWR from cache to redo log files.

If we will define log_checkpoint_interval = 0 (zero), it means we 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;

The error "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.
We can also 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 :

• Add more redo log groups or increase the size of redo logs.
• Reduce the frequency of checkpoints by increasing LOG_CHECKPOINT_INTERVAL.
  

# posted by Masood @ 9:47 PM