Re: Data Buffer Cache

Hi Howard,

I totally agree that excessive writing of checkpoint blocks should be avoided unless you have SLA demanding small instance recovery times.

However, the advantage of continuous checkpointing is that is avoids the performance issues associated with the hourly mega checkpoints. Right in the middle of key processing time, we have DBWR burdened with having to flush all current dirty blocks to disk. By setting an appropriate fast_start_io/mttr_target, you don't have these periodic performance hits. The "cost" associated with checkpointing is relatively evenly spread throughout.

By selecting a smaller value for these parameters, you make DBWR work more aggressively (and yes, you hence potentially suffer the performance hit as a consequence), by increasing these values you make DBWR work less aggressively (as the likelihood of the blocks having naturally aged and been written down by DBWR anyway increases).

Note with your recommendation of setting these values sky high, you effectively set continuous checkpointing on. The only difference being that the fast_start_io/mttr_target is unlikely to be the most aggressive target used by Oracle and Oracle will instead use the 90% of smallest log file as the instance recovery target (unless your log_checkpoint_interval/timeout parameters state otherwise). This being the case, it means DBWR will be continually posted to check for checkpoint blocks but the likelihood of such blocks still being in memory is low.

And I agree this isn't necessarily a bad strategy. It means no major performance hits during a log switch but longish instance recovery and shutdown immediate times.

I personally recommend setting these parameters, but in a way such that instance recovery times and the potential performance implications are balanced according to the database requirements. You then have less issues with redo log sizes, less likelihood of checkpoint not completing issues, no periodic performance hits and predicable instance recovery times. The penalty, DBWR working a little harder all of the time, how 'little' or how 'much' controlled by the DBA with the tuning of these parameters.

Cheers

Richard

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3d87fd79_at_dnews.tpgi.com.au...
> This is indeed true, provided you're prepared to undergo continuous
> checkpointing. If you have an SLA demanding signed, sealed and delivered
> instance recovery times, fair enough. But otherwise, *anything* that
induces
> checkpointing when its not needed has got to be a dubious idea at best.
>
> I tend to suggest making sure these parameters are either not set, or set
to
> ridiculously high levels so they don't have any practical effect.
>
> Unless you need the guaranteed recovery times, of course.
>
> Regards
> HJR
>
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:QyPh9.35013$g9.98743_at_newsfeeds.bigpond.com...
> > Hi Howard and all,
> >
> > I think I'm missing something here or maybe it's somewhat out of the
scope
> > of this discussion (although I don't think it is).
> >
> > This concept of sizing redo logs in order to control the behaviour of
> > checkpointing. I have no problem with it pre 8i. However it's all
somewhat
> > irrelevant (or it should be) since the changes in behaviour of the
buffer
> > cache and the introduction of the fast_start_io_target parameter in 8i
> (and
> > fast_start_mttr_target in 9i).
> >
> > By setting these parameters appropriately, the sizing of redo logs in
> order
> > to control checkpoint behaviour is no longer an issue per se. Oracle
will
> > continually post the DBWR to ensure that dirty blocks preventing these
> > targets from being met are flushed to disk.
> >
> > The advantages of course being *predicable* instance recovery times
> > regardless of size of redo logs or when the last checkpoint may have
> > completed and an *even* load at all times, no longer there being spikes
of
> > activity as Oracle desperately tries to complete a checkpoint.
> >
> > I just think it's a point worth mentioning ...
> >
> > Cheers
> >
> > Richard
> > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > news:3d878a16_at_dnews.tpgi.com.au...
> > > I also tell them of the database in Queensland that produces a 500M
> > archive
> > > every 7 minutes.
> > >
> > > I flatter myself that people don't come out of the classroom until
> they've
> > > got as a complete a picture as it's possible to paint in the time
> allowed.
> > > If you have to switch that frequently, so be it. Just be aware of the
> > costs
> > > involved. And if you can avoid switching that frequently, it's
generally
> a
> > > good idea to do so, bearing in mind the further potential costs in
> > instance
> > > recovery scenarios.
> > >
> > > The bottom line I give them is: size your logs so that you end up
> > switching
> > > (and hence checkpointing) at a rate you are happy with.
> > >
> > > Regards
> > > HJR
> > >
> > > "Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
> > > news:t2oeouk7kk8kugc5m2b3c4vlij4jipr61a_at_4ax.com...
> > > > On Tue, 17 Sep 2002 19:12:01 +1000, "Howard J. Rogers"
> > > > <howardjr2000_at_yahoo.com.au> wrote:
> > > >
> > > > >I also tell them of the 'one switch per hour' school of DBAing, so
> they
> > > get
> > > > >both sides.
> > > >
> > > > So what if you often have 250M redolog in less than 30 minutes?
> > > > (I'm not joking)
> > > >
> > > >
> > > > Regards
> > > >
> > > >
> > > > Sybrand Bakker, Senior Oracle DBA
> > > >
> > > > To reply remove -verwijderdit from my e-mail address
> > >
> > >
> >
> >
>
>
Received on Wed Sep 18 2002 - 02:29:59 CDT