Tony’s Oracle Tips

September 23, 2008

Can an online redo log be too big?

Filed under: Uncategorized — tonyhasler @ 7:12 pm

I was asked this question recently in an interview and muffed the answer. Being somewhat flustered by the barrage of questions in my ear I stated that if an online redo log was too large then the cost of a checkpoint would rise. I delivered this answer with confidence but I was unsure if I was right. Now I am sure I was wrong!

Checkpointing is the process that Oracle uses to flush dirty buffers out of the buffer cache back to their “home” locations in the datafiles. This process avoids the need to “replay” the changes to these blocks should instance recovery be required. It must occur each time a log switch occurs but may happen more frequently.  I was thinking that the larger the redo log the longer the interval between checkpoints would be and the longer said checkpoints would therefore take.

Baloney!  Oracle 9i introduced a feature known as “Checkpoint Autotuning” which causes checkpoints to occur frequently enough to avoid lengthy recoveries even if you make your online redo logs huge.  The only way to force long checkpoints is to explicitly set the FAST_START_MTTR_TARGET initialisation parameter to zero.  According to this article ( on the OTN forums the default value of zero is different from the explicitly set value of zero!  Now I don’t feel so bad for messing up!


P.S. I got the job!


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: