Re: Checkpoint not complete

On Mon, 4 Jan 1999, Thomas Kyte wrote:
>
> "Pedro Tavares" <pedrotavares_at_cmvm.pt> wrote:
>
> >My database has lots of activity, and if I monitor de alert log, I'm
> >constantly receiving the message - checkpoint not complete. I believe that
> >this is affecting the overall performance. What can I do to avoid this? I
> >have 4 redo log files of 1M each.
> >Thanks in advance.
>
> Add more redo. This message means that by the time we've gone through 4 1m redo
> log files, the checkpoint that was started by switching from log 1 to log 2 has
> not compleleted yet. We need to STOP and finish the checkpoint before we can do
> anymore work.

It bears mentioning that in many cases, where the redo is constantly generated at a high rate, and the I/O subsystem performs poorly, adding more redo will only postpone the inevitable "checkpoint not complete," and make the buffer cache stay very dirty all the time. Remember that when a log switch occurs during a checkpoint, it starts checkpointing again. This means that in a situation where you constantly fail to complete checkpoints, some portion of the DBWR write batch is being constantly usurped for the purposes of a checkpoint, reducing overall performance by making people wait on "busy buffer waits" and "write complete waits." Not to mention the enqueue being constantly held on the controlfile.

An alternative approach is to keep the amount of redo that you think is reasonable, and tune the aggressiveness of the checkpoint using db_block_checkpoint_batch. This will allow the checkpoint to use more of the DBWR write batch when it has to, in order for it to complete in a timely fashion.

I wrote a long-winded posting on this topic a while back. Here's the dejanews URL:

http://www.dejanews.com/getdoc.xp?AN=403789068

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Wed Jan 06 1999 - 09:48:30 CST