Re: how to place a table in memory

"Chuck" <skilover_nospam_at_softhome.net> wrote in message news:Xns95F970AD58DAskiloversofthomenet_at_130.133.1.4...
> "David Aldridge" <slimdave_at_yahoo.com> wrote in
> news:1108048751.524193.292820_at_f14g2000cwb.googlegroups.com:
>
>> I'd be interested in knowing why you want to do this -- my own practice
>> has just been to let the cache take care of itself, and I'm suspicious
>> that trying to pin a table in memory might actually be worse for
>> overall system performance
>>
>
>
> If the table is small, and the optimizer determines it's faster to access
> it by a table scan, it will constantly be on the cold end of the LRU list
> be bumped from memory. Placing it on the keep pool will keep it in memory,
> reduce i/o, and improve performance of queries involving that table.
>

Hi Chuck,

If the table is actually "small" from Oracle's perspective (less than 2% of buffer cache or 20 odd blocks depending on which is greater), then the table is assigned the CACHE characteristic regardless, meaning the table is not constantly placed on the cold end of the LRU list (but joins the partly 1/2 way or so down the list)

Then if the table is actually accessed constantly, it should be have enough life left in the cache to be re-read from memory (although admittedly with 8i/9i at least, the touch count algorithm is prejudiced against cached tables)

Cheers

Richard Received on Fri Feb 11 2005 - 00:26:05 CST