Re: how to place a table in memory

"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.

A classic example of this is the Peoplesoft process scheduler table. It queries that table every 15 seconds and on most databases, by that time it's been flushed from the cache and needs to be reloaded every time. Adding it to the keep pool keeps it in memory. Received on Thu Feb 10 2005 - 10:04:40 CST