Re: Cache a table

"Chuck" <chuckh_at_softhome.net> wrote in message news:Xns941B5E141AD77chuckhsofthomenet_at_130.133.1.4...
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in
> news:3f95066c$0$10411$afc38c87_at_news.optusnet.com.au:
>
> > Kalle wrote:
> >
> >> Hi all,
> >>
> >> when you would cache a table. what are the general rules if the
> >> amount of memory isn't a problem...
> >>
> >> tia
> >> Kalle
> >
> > The general rule is don't bother.
> >
> > Well, OK... what I mean is that if you are talking about the 'CACHE'
> > clause of the alter table statement, or create table, statement, don't
> > bother. It is a pretty poor way of achieving better performance, and
> > has long since been superceded by the multiple buffer pools feature
> > (first introduced in Oracle 8.0).
> >
> > And the rules of thumb for those multiple buffer pools are:
> >
> > tables smaller than 10% of your default pool are candidates for
> > housing in the keep pool
> >
> > Tables bigger than 200% of your keep pool are candidates for housing
> > in the recycle pool.
> >
> > But those are only rules of thumb.
> >
> > Regards
> > HJR
>
> And I would add that for the keep pool, you should only include tables
> that are frequently scanned. No point in wasting memory on a table that
> isn't, even though it meets the ROT for size.
>
> I can think of one case where using the pre-8.0 style of caching might
> apply. That would be a small table that is only used in a long running
> batch job that does frequent scans of that table. It would end up being
> cached in memory for the duration of the batch job, but eventually
> dropped out of memory when the job finishes.

If a table is frequently scanned (i.e. very hot), it is going to remain in the buffer pool no matter what. Its no good use putting it in the KEEP pool.

The tables which are medium-high in scans are better candidates for the KEEP pool.

Anurag Received on Wed Oct 22 2003 - 01:06:28 CDT