Re: Interaction between databse cache, DBWR and datafiles

"Noons" <nsouto_at_optusnet.com.au.nospam> wrote in message news:Xns9315F2928815mineminemine_at_210.49.20.254...
> "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.
>

Hi Nuno,

I remember having this discussion with you at another time ;)

OK, I can't say my experience matches with yours and I don't quite agree with how you set up a KEEP pool, *however*, if you have it all well tuned and it works for you, who am I to argue !!

Cheers

Richard Received on Sun Feb 02 2003 - 01:42:44 CST