Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: cache vs keep buffer pool

Re: cache vs keep buffer pool

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Tue, 27 Aug 2002 23:05:17 +1000
Message-ID: <3d6b7b08$0$29910$afc38c87@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.

> > > 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.

> > >
> > > 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.

> > >
> > > 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.

> > >
> > > 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.

> > > 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.

> > >
> > > 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.

> > 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?

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam
Received on Tue Aug 27 2002 - 08:05:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US