checkpoint completed

A session is waiting for checkpoint to complete. For example, this could happen during a close database or a local checkpoint. This is invoked from dbwr ,which is called to check that all dirty buffers have been written to disk. If a block is found which is still dirty, then we wait up to 5 seconds and then rescan the buffer cache. We should be posted when the block has been cleared. The following statistics from V$sysstat can be used to checkpoint related statistics. background checkpoints completed

The number of checkpoints completed by the background process. This statistic is incremented when the background process successfully advances the thread checkpoint.

background checkpoints started

The number of checkpoints started by the background process. This statistic can be larger than "background checkpoints completed" if a new checkpoint overrides an incomplete checkpoint or if a checkpoint is currently under way. This statistic includes only checkpoints of the redo thread. It does not include: Individual file checkpoints for operations such as offline or begin backup Foreground (user-requested) checkpoints (for example, performed by ALTER SYSTEM CHECKPOINT LOCAL statements).

Wait Time:

5 Seconds

If this happens frequently, you may want to increase the time between checkpoints by checking the init.ora parameter db_block_checkpoint_batch:

select name, value, isdefault
  from v$parameter
 where name = 'db_block_checkpoint_batch'

The value should be large enough to take advantage of parallel writes. The DBWR uses a write batch that is calculated like this:

('db_files' * 'db_file_simultaneous_writes')/2

The write_batch is also limited by two other factors:

• a port specific limit on the numbers of I/Os (compile time constant).
• 1/4 of the number of buffers in the SGA.

The db_block_checkpoint_batch is always smaller or equal to the _db_block_write_batch.

In normal circumstances user processes will not wait under the "checkpoint completed" event. Rather they will wait for log_file_space_switc wait event. However during shutdown and user checkpoint, this event is used to wait for block(s) to be written to disk.

It would seem reasonable to make the blocker DBWR, since we are really waiting for DBWR to write out the dirty buffers. However, if DBWR is waiting on rdbms_ipc_message wait event, then we make ourselves dependent on LGWR.

Sometimes the database seems to be hanging when trying to issue an 'ALTER SYSTEM CHECKPOINT' and never returns to the prompt. In the alert.log various 'checkpoint not completed' messages are visible. 'ALTER SYSTEM SWITCH LOGFILE' also just hangs and never returns to the prompt.

If you are using multiple I/O slaves, switch to multiple database writers by editing the following init<sid>.ora parameters. Replace the init<sid>.ora file parameter dbwr_io_slaves with db_writer_processes.

From the Alert.log file it can be seen that the O/S cannot keep-up handling the writes to disk - 'checkpoint not completed'. dbwr_io_slaves specifies the number of I/O server processes used by the DBW0 process. The DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.In some scenarios it's better to allow Oracle to handle the writing to disk. This can be achieved by setting DB_WRITER_PROCESSES.