Re: Cache a table

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3f9660e8$0$9554$afc38c87_at_news.optusnet.com.au...
> Anurag Varma wrote:
>
> >
> > 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.
>
> Disagree. The issue is not whether it would tend to want to stay in the
> buffer cache or not, but whether it is at risk of being dislodged by a
> rogue, huge, tablescan. By putting even a frequently-accessed table into
> the keep pool, you ensure it can't be dislodged by scans against large
> tables (assuming you haven't been daft enough to ask for them to go into
> the keep pool as well, of course!!).
>
> Such cache 'partitioning' has got to be a good thing in its own right.
>
> Regards
> HJR
> --
> --------------------------------------------
> See my brand new website, soon to be full of
> new articles: www.dizwell.com.
> Nothing much there yet, but give it time!!
> --------------------------------------------
>

Howard,

In your argument I would ask why that big table is not put in the recycle pool. If a segment is extremely hot, then random access to large segments should not displace its cached buffer. After all a buffer block earns its place by touch count.

However, warm-hot segments can be in danger of their blocks being displaced by a large segment read.

Now in a datawarehouse, pretty much most of the dimension tables and especially their indexes should probably be in the Keep Pool. While the fact tables probably should be kept in the recycle pool.

Although, I would agree that if the database shows unpredictable activity of ad-hoc users making large/medium table scans on a regular basis, then the advice of putting extremely hot segments in the keep pool makes more sense.

Anurag

Anurag Received on Wed Oct 22 2003 - 08:03:14 CDT