Re: Data Buffer Cache

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3d8846a2_at_dnews.tpgi.com.au...
> God knows what button I pressed, but I was in mid-flow, so I'll
continue...
>
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message >
> > >You then have less issues
> > > with redo log sizes,
> >
> > Principles dictate otherwise. You need to establish a "regular
heartbeat"
> > before you start monkeying with the rythym.
> >
> > >less likelihood of checkpoint not completing issues,
> >
> > That's a function of the number of logs in principle. It can be dealt
> with,
> > principally, without recourse to extra logs.
> >
> > >no
> > > periodic performance hits
> >
> > Agreed. Just crap performance all the time instead.
> >
> > >and predicable instance recovery times.
> >
> > You make it sound like a menu you can pick and choose. It isn't. It's a
> > trade-off. You want predictable instance recovery times? Fine: choose
bad
> > performance. You want performance? Fine: forget instance recovery times,
> > 'cos they're gonna be bad. Strike a balance: fine, but performance will
be
> > sub-optimal.
> >
> > And then you have to ask: how often do you expect instance failures? You
> > tune for the rare occurance
> >
>
> ...and that's fine if that's what you're legally required to do. But it
> strikes me as plain daft to worry about the once-a-blue-moon, and to
cripple
> daily performance accordingly.

That is an incorrect assumption. Performance can actually *improve* as the expensive checkpoints that did impact performance are gone, and an unaggressive recovery target can be applied that doesn't actually do much additional work as the requested blocks could very well be long gone from the buffer cache.

*This is a very key point * ...

> >
> > The
> > > penalty, DBWR working a little harder all of the time,
>
> You make it sound so trivial!
>
> And what of DBWR's 'little' flushes to disk?
>
> Well every damn jack of them conflicts with your user's attempts to read
> *off* disk, for a start.

Maybe, maybe not. For a start, it might not require a block write. Secondly, the write might not cause a conflict. It depends and so needs to be monitored and tuned accordingly.

>
> Checkpoints are *baaaaaaaaaaaad*. Avoid them where you can. Other things
> being equal, and lawyers not in attendance. Otherwise, fine: stuff up your
> database and make it behave like Access on a bad hair day. So long as you
> know the trade-offs, I trust most DBAs to be able to balance themselves
> somewhere along the continuum.

It depends as already discussed. The assuption that a continuous checkpoint will make the database run like a three legged dog is *potentially* incorrect and is a simplistic generalisation.

>
> But the bed-rock principle remains, whatever, that checkpoints are bad
news,
> and you'd be daft to introduce them where they're not needed.

This is not tedious, this is fun ;)

Cheers

Richard

>
> Regards
> HJR
>
>
> >>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 - 05:36:27 CDT