Re: Data Buffer Cache

Hi Howard,

Comments embedded.

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3d88452f_at_dnews.tpgi.com.au...
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:sNVh9.35181$g9.99519_at_newsfeeds.bigpond.com...
> > 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.
>
> I don't want to be awkward, but I don't disagree with any of this -but
it's
> a separate issue.

That's cool. We can agree to disagree ...

Log sizes should be based on the "raw" rate of
> checkpointing you want to achieve. You want to start introducing extra
> checkpointing -or even continuous checkpointing- (a) you must have an SLA
or
> (b) you want your head tested.

You've been talking to my shrink ...

>
> >
> > 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,
>
> Now now... I said, either don't set them at all or set them sky high so
they
> have no effect. It depends on whether there's an Oracle default which
would
> apply in the case of total omission. In either case, I mean: make sure
they
> don't apply.

In that case tell your student to set these parameters to 0. Any other value and continuous checkpointing in some form takes place. Note the largest possible value is the size of the buffer cache or 1 hour depending on which one you are referring to.

>
> > 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.
> >
>
> Er, run that one past me again? You mean that MMTR_TARGET or
> FAST_START_IO_TARGET have a bearing on a shutdown immediate??????? Or the
> rate of Log switching has a bearing on them??????????????????????????????
>
> You'll have to explain that one, since in my book, a shutdown immediate
> means "rollback anything not committed" -and whether you've been
> checkpointing continuously, a thousand times or once has no bearing
> whatsoever on whether a transaction's been committed.

I agree that rollbacking uncommitted transactions is potentially he biggest overhead. However, think about it. What else does Oracle do with a clean shutdown. It triggers a checkpoint and writes all dirty blocks to disk. Now if you have heaps of blocks to be written to disk it will take longer than having fewer blocks to read down to disk (because continuous checkpointing has been cleaning them out as you go). Therefore potentially, final checkpoints at shutdown won't take as long. That's all I was trying to say. I'm not suggesting this is a reason for have continuous checkpoints.

>
> None of this discussion has a bearing on 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.
>
> This is getting (yet again) a tad tedious. You think I go in to the
> classroom and say, "Oh to hell with it, set them to anything you like, so
> long as its a balanced, rational decision"????

I'm not forcing you to write anything ...

>
> "Balance": shmalance.
>
> Principles, deary. Principles.
>
> In *principle* a checkpoint is bad news. Avoid them where you can. Where
you
> can't, fair enough. But be aware of the costs. Then, whatever price you're
> prepared to pay is a fair one, and reasonable. But be smart and
> intelligent: that's what DBAs get paid for. So if circumstances dictate,
> bend the practices. Do what you have to do. But the principles remain,
> whatever you do.
>
> You decide to implement continuous checkpointing? Fair enough: I'm sure
you
> had good reasons. But the *science* underlying Oracle means X, Y and Z as
> side effects.
>
> Whether you like the side effects or not is up to you and your "real
World"
> situation, but the science doesn't change one way or another.

But the effects of checkpointing a bit all the time could very well be such that there is *no* noticeable impact on users. It's potentially unnoticeable as it's only dribs and drabs being written down. What might be noticeable is the fact that the slow down in performance when the "hourly" mega checkpoint was previously applied has gone. The system appears to run smoothly *all the time* !!
>
> >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.

Incorrect. If that's the case, you haven't tuned these parameters correctly or you are forced into this due to excessive recovery demands. In your example (which admittedly you didn't mean) by having the fast_start_io_target set to the max, the performance impact could very well be zip. That's right zip. Because the blocks that Oracle is posting DBWR to write could very well have been aged out. DBWR is performing only a trivial amount of extra work, most of these blocks have already been written to disk. *BUT* no expensive checkpoint every log switch as all *currently* dirtied blocks must be written to disk in your case.

The only time Oracle "catches" up with a continuous checkpoint is when the database is shutdown (hence my previous point) or another checkpoint condition arises.

Understand ?

>
> >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.

I hopefully make it sound like a tuneable, controllable, measurable aspect of tuning the database.

Having these parameters set doesn't necessarily mean crap performance. Such generalisations are simplistic and potentially incorrect. These parameters give you much more *control* than previously possible.

>
> And then you have to ask: how often do you expect instance failures? You
> tune for the rare occurance

You're missing a very important point. It's not just in relation to instance recovery times. If it were, then I might be more agreeable. In your database environment, you have periodic, expensive, performance limiting checkpoints. You've suggested nothing to address this other than delay as much as possible. In my database environment, I have a tuned sequence of little checkpoints, many of which may actually perform no real work, that produces an unnoticeable effect on performance. Period.

To be continued ...

Richard ;)

>
>
> 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 - 05:25:22 CDT