Re: cache vs keep buffer pool

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

"Nuno Souto" <nsouto_at_optushome.com.au> wrote in message news:dd5cc559.0208262034.3d570450_at_posting.google.com...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:<3d6a8c7c_at_dnews.tpgi.com.au>...
> >
> > The cache keyword is useless. Utterly, utterly useless.
>
> And, I believe, now "deprecated"?
>
> > Which means that nasty full scans on big tables cannot possibly flush
your
> > blocks from small, useful tables -provided you direct each sort of table
to
> > different pools.
>
> Yup. In fact for large, seldom scanned tables I actually prefer to put
> them in RECYCLE. That way the scan will happen without shirting up
> KEEP or DEFAULT.
>
> > If you are on 8.0 or above, there is practically no sense in setting
> > 'CACHE'. The keep pool does it better, more reliably, and more
effectively.
> >
>
> Unless you are in the buggy point release of 8.0 that needs the CACHE
> thing to be specified to send stuff to KEEP. DAMHIKT...
>
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au
Received on Tue Aug 27 2002 - 01:21:25 CDT