Re: cache vs keep buffer pool

"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message news:3d6b7b08$0$29910$afc38c87_at_news.optusnet.com.au...
> In article <XeKa9.16441$g9.51171_at_newsfeeds.bigpond.com>, you said (and I
> quote):
>
> Yikes! Can't you folks

snip ;)

>
> > > > Firstly, cache is not depreciated, it's very much alive in 9i.
>
> I'm quite sure I read somewhere recently that it is.

Based on the 9.2 doco set, CACHE is still a supported clause with no 'do not use' notes associated with it.

>
> > > > 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
>
> Exactly the same applies to KEEP. It has various undocumented
> parameters. They also limit the damage.

True enough, but I guess I was implying the other issues below.

>
> > > >
> > > > 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.
>
> Hmmmm, the cynical would argue memory is cheap. I won't, though. Yes,
> too big is wasteful. So is any other buffer cache that is configured too
> big. I've got a funny feeling in most cases it won't be.

True again. I'm just saying that memory that is allocated but never use is about as wasteful as you can get. But an oversized Default pool is also wasteful and has a detrimental effect on performance.

>
> > > >
> > > > 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
>
> Nope. Do not agree. If KEEP is not large enough, it will behave exactly
> (or as near as possible) like CACHE with DEFAULT. With one *big*
> difference: it will do its reloads *contained* to the KEEP buffers. Ie,
> without affecting the DEFAULT and RECYCLE cache one single bit.
>
> THAT is exactly what I want. A way to handle frequently used tables with
> occasional scans that does NOT go around upsetting the entire cache
> memory. If it has to temporarily upset something, then I want it
> contained.
>

All pools effectively behave the same but the issue here is this. The KEEP pool is meant to contain frequently accessed objects that ideally once loaded into memory, don't have to be re-read from disk. Also, less latches are generally allocated here as again if things are well configured, there shouldn't be much need for them. However, if the pool is too small, then obviously things need to be aged out. And what do we age out, things that are commonly accessed, by definition. When they get re-loaded, what do they age out, things that are commonly accessed. This is an ugly pattern. So what I'm suggesting is that aging out of objects is "worse" in this pool because by definition we are aging out stuff we *want* to put right back in again. So the problem here is much magnified than in the default pool where generally, we age out stuff we *don't* want anymore. Add to that the possible fewer latches issues and we have a real problem with performance.

> > > >
> > > > Therefore sizing the KEEP pool is hugely important. it needs to be
sized
> > > > 'right' (not just 'there abouts sort of').
>
> Not hugely important, IMHO. But sure: of some importance.

OK not hugely. Very Very important ;)

>
> > > > 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.
>
> Oh no they don't! You get tables aged out of KEEP like any other cache.
> Different algorithm, different load characteristics. But you most
> definitely get ageing.

If the pool is too big and is greater than the sum of all KEEP objects, what ages out cached objects? Nothing, because nothing else can go into the KEEP pool !! So, if we have a table cached in the KEEP pool that is used once a week, that table effectively wastes storage all week because nothing else can age it out (unless the pool is too small, back to the problem above) That's why it's wasteful when compared to the default pool where if cached, an infrequently used object would be aged put by all the other objects in the default pool.

>
> > > >
> > > > 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.
>
> Narh! Those are the OCPs. They couldn't care less if it doesn't show in
> Enterprise Manager or similar as a flashing bar.

Point taken ;) But it would be interesting to know how many DBAs use the CACHE clause vs the KEEP pool...

>
> > > alternative. You start throwing the CACHE keyword around
inappropriately,
> > > and things are just as likely to go wrong. More positively, if you
start
>
> Exactly.
>
> > >
> > > 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!
>
> Er..... RMAN anyone?
> <g, d&r>
>
> > pools when tuning the one pool is difficult enough. With Oracle's drift
> > towards self tuning, this will soon be another non issue ... maybe
...
> >
>
> Let's hope not. The "self tuning" so far is only causing problems where
> I'm using it with 9ir2...
> Back to good old hints. Unruly hash joins once again! Will Oracle never
> learn?

I agree. But the self tuning stuff is still young and will improve as releases progress. I think our jobs are safe for a tad longer yet.

Cheers

Richard

>
>
> --
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam
Received on Tue Aug 27 2002 - 08:59:38 CDT