Re: Data Buffer Cache

Richard Foote wrote:
>
> 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >

Certainly the checkpoint time at shutdown can be significant. I always use 'alter system checkpoint / shutdown abort" to shutdown, and I've seen some checkpoints in the minutes range.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."