Re: cache vs keep buffer pool

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3d6b2d89_at_dnews.tpgi.com.au...
> Richard Foote wrote:
>
> > Hi Daud, Howard, Nuno and all,
> >
> > I generally agree with what's been said but would add a couple of
points.
> >
> > Firstly, cache is not depreciated, it's very much alive in 9i.
> >
> > From a simplistic point of view and I would suggest that the CACHE
option
> > has less 'potential for damaging performance' than the KEEP pool. What I
> > mean by that is if we pick the wrong table to cache (either it's 1- too
> > big to be suitable or it's 2- not used frequently enough to be
suitable),
> > the potential for damaging performance is limited. Firstly various
> > undocumented parameters limits the "damage" for cause 1 and the natural
> > aging process looks after the cache for cause 2. Also, if cached and if
> > frequently used, although as Howard suggests there's no guarantee that
the
> > blocks will remain cached, there is a reasonable probability all things
> > being equal that they do in fact stay cached. It's not so much large
table
> > scans that will cause cache tables to be aged out (as they get
overwritten
> > in the LRU portion of the LRU list) but high index activity or much
> > reading of other cached tables.
> >
> > The 'potential for damaging performance' when using the KEEP pool is
that
> > we now take over somewhat and if we don't use the keep pool
appropriately,
> > we have a fair chance of making things worse rather than better. Some of
> > the possible issues are:
> >
> > 1) The KEEP pool is too big. Now sizing any cache is obviously important
> > but here we have direct control over what goes where. If we size the
cache
> > too big (for the number of objects being allocated to the pool) we waste
> > memory. We could have a scenario where buffers are simply never used
> > because the pool size exceed the sum of objects allocated to the pool.
> > This 'absolute' wastage is arguably worse than that in the default pool,
> > in that at least buffers are being 'used' and have some chance of being
> > reused.
> >
> > 2) The KEEP pool is too small. Now we have objects being aged out anyway
> > because the pool is not able to store all the allocated objects. As
these
> > objects are (should be) frequently accessed, a poorly sized pool can
cause
> > excessive levels of reloads. Note also that just one poorly selected
table
> > can stuff things up totally and cause our otherwise well tuned pool to
> > behave awfully. Also tables could change in size over time and if so
this
> > needs to be taken into account (although generally these cached tables
> > would be more read only, lookup type tables, but then ...) A KEEP pool
> > that is sized too small can make matters a lot worse than if they be in
> > the default pool
> >
> > Therefore sizing the KEEP pool is hugely important. it needs to be sized
> > 'right' (not just 'there abouts sort of').
> >
> > 3) Effects of infrequently used table. A 'cached' table needs to be
> > smallish *AND* frequently used. In the default pool, an infrequently
used
> > cached table causes minimal damage in that it will age out eventually.
Not
> > so here. If we pick tables we think are going to be frequently used but
> > are not, they sit and remain cached causing memory to be wasted. In fact
> > if the KEEP pool doesn't account for a reasonable share of the I/Os then
> > memory is not being utilised efficiently which hurts performance.
> >
> > Therefore, it's crucial that objects assigned to the KEEP pool (and
indeed
> > the RECYCLE pool) must be appropriate.
> >
> > Now I agree that a well tuned and well configured KEEP pool is most
useful
> > and has advantages over simply caching a table in the default pool. But
> > the point I want to make is that it requires more "skill" in the DBA to
> > set things up right. You must select the KEEP tables carefully, you must
> > size the KEEP pool carefully and you must monitor the performance of the
> > KEEP pool carefully.
> >
> > Without being too general, some DBAs out there in real land world might
> > struggle to with all this for which the CACHE table option might provide
a
> > simpler, more appropriate and "less dangerous" option.
> >
> > My thoughts :)
> >
> > Richard
>
>
> Couldn't quite make out that last sentence. Wannanothergo?!
>
> I'll buy the argument that it takes more knowledge of your app., your
> database and your data to set the various buffer pools appropriately.

That's basically the point I'm making...

>
> But I won't then go on to say that 'CACHE' is a kiddie-friendly
> alternative. You start throwing the CACHE keyword around inappropriately,
> and things are just as likely to go wrong. More positively, if you start
> throwing the CACHE keyword around, it's a sign that you are already
> somewhat frustrated with Oracle's default cacheing mechanisms, and want to
> get in there and direct things for yourself a bit.
>
> So you might as well get in there and do the job properly, with something
> that produces real results, instead of just a warm glow of a job
> half-bakedly done.
>
> The day we start saying 'don't use this feature, it's too complicated for
> the likes of you' is the day I quit training people!

And that's why we train people and that's why people train themselves. None of this is rocket science else I most certainly wouldn't be able to do it :o) But *some* take the easy path and the easy path is not have multiple pools when tuning the one pool is difficult enough. With Oracle's drift towards self tuning, this will soon be another non issue ... maybe ...

Cheers

Richard

>
> Regards
> HJR
>
>
>
>
>
>
Received on Tue Aug 27 2002 - 07:36:30 CDT