Re: Data Buffer Cache

(Posted once before, but it seems not to have arrived, so here it is again, with a couple of minor changes).

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:kwYh9.35307$g9.100157_at_newsfeeds.bigpond.com...
>
>
> 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 * ...
>

Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:kwYh9.35307$g9.100157_at_newsfeeds.bigpond.com...
>
> 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 * ...
>

And one that's missed mine by a mile (which was that I wouldn't have any checkpoints at all, so performance *would* suffer in comparison when you induce additional checkpointing).

To try and cut through all the junk that this thread's thrown up, here's a summary of what I recommend:

No checkpoints at all if you don't need to worry about recovery times (ie, huge logs, and all the various extra-checkpoint-inducing parameters switched to non-effective settings), and performance is King. At midnight, schedule an 'alter system
checkpoint'. Performance during the day will be the best it can be.

If you do have to worry about instance recovery times, then of course incremental checkpointing is to be favoured over big-bang checkpointing -though the change in behaviour of log_checkpoint_interval in 8i over 8.0 means that you get that even without the use of the 8i/9i _TARGET parameters. Performance will not be as good as it would have been without any checkpointing at all, but it won't be periodically hammered to death, either. Strike a balance between performance and recovery time by setting the parameters carefully to limit the amount of checkpointing to that which is strictly necessary to achieve the required balance.

If you are mainly worried about instance recovery times, then set the fast_start_io_target parameter together with the log_checkpoint_interval parameter (or the MTTR_TARGET one in 9i, which sets both in one go). Performance will suffer as compared to suggestion #1, but that's fine, because you are constrained to worry about other things, and so its a trade-off you are willing to make.

I don't think that any of that is capable of being taken exception to. Where we may disagree is that I would usually favour suggestion #1 over any of the others, because I would prefer to tune for regular activity rather than concern myself with an event (instance failure) that hopefully only happens once in a blue moon.

Of course, if you're running on NT, that wouldn't apply to you, because blue moons in NT-land are potentially two-a-penny. :)

Now all your examples and counter-arguments are based on (for example) log switching once an hour. Where I think we've been at loggerheads is that I wouldn't even switch that frequently unless I had to (ridiculous amounts of redo being produced every 3 minutes, legal issues, SLAs etc). You are definitiely in Suggestion #2 & #3-land. Which is not where I want to be, given half a chance.

HJR Received on Thu Sep 19 2002 - 15:23:59 CDT