Re: Interaction between databse cache, DBWR and datafiles

"Richard Foote" <richard.foote_at_bigpond.com> wrote in news:SPu_9.37485$jM5.95718_at_newsfeeds.bigpond.com and I quote:

> keep re-reading them from disk. By reading them in, it means something
> else has to go, which has to be re-read and so on and the whole point of
> the keep pool is severely diminished. Therefore, it should really be
> sized to accommodate *all* segments you wish to place here.

Sure. But let me just add a few points here:

Workloads change during the day. There is no point in sizing KEEP for, say, *all* your 1000 read-only lookup tables if only 200 or so of them are ever used at any time. This is something I've seen in a number of places that have tried KEEP: they size it for the TOTALITY of tables in those conditions. Instead of the working set size that best matches the workload.

Another thing to watch out for: not all rows in those tables need to be cached all the time. Again, only a working set of blocks.

I've found that looking at table names and their block counts in buffers and analyzing these over a period of time helps greatly in keeping KEEP (!) sizes under control. Another good guideline is to keep an eye on the hit rate there using bpstats: aiming at a miss rate of less than 0.0001 means a darn effective use of this memory.

Note that many times we are not trying to make sure *all* stuff in KEEP is *always* in memory. What we are trying to do is stop OTHER tables and indexes from flushing highly read blocks off memory. There is nothing wrong either with moving some indexes to KEEP, for example.

> sit there like stunned mullets wasting memory. Therefore we would
> generally size this pool to be relatively small so that objects age out
> quickly, but not too small such that they aged out before perhaps the
> transaction itself has a chance to re-access the block as necessary. You
> don't want to induce additional physical I/Os by making it too small,
> but you don't want to make it too big and waste memory either.

Precisely. There seems to be a trend to size RECYCLE too small. It doesn't need to be. It needs to be able to cope with temp stuff for each transaction, at the very least. I mostly keep temp tables and tables that get major insert/delete cycles in here. AND their indexes as well. Makes a world of difference.

>
> That's where rolling our sleeves and tuning these babies comes into play

Oh yeah! Ain't it fun!!! :D

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam