Re: cache vs keep buffer pool

Daud wrote:

> If I have a small table that is accessed very frequently what is the
> advantage of moving it over to the keep buffer pool as opposed to just
> 'cache' it using the cache storage parameter? If it is accessed very
> frequently the blocks should be cached in the buffer pool and chances
> of them getting aged out should be slim.
> So, what advantage would the keep buffer pool offer in this case?
>

The cache keyword is useless. Utterly, utterly useless. It was a nasty, cheap Oracle 7 way of trying to prevent small tables from being washed out of the buffer cache by large tablescans. So if you are still running Oracle 7, and can't upgrade, I retract the statement that it is useless... it's better than nothing.

Oracle's normal rule is that blocks read via a full scan are placed at the 'cold' end of the LRU list, so they don't flush useful blocks out. But small, useful tables will almost certainly be read via a full table scan. So they will end up in the wrong half of the LRU list, too. The cache keyword causes them to be read into the hot half of the LRU list, despite them being read by a full scan. The theory is, that this way, the nasty big full scans will leave these blocks alone.

But it's all touch and go, and your blocks are still vulnerable to being flushed.

The keep buffer pool, on the other hand, is an entirely separate area within the buffer cache. It has its own LRU list. Entry to the keep pool is by invitation only: so unless you declare a table can use it, it doesn't. Which means that nasty full scans on big tables cannot possibly flush your blocks from small, useful tables -provided you direct each sort of table to different pools. It's the cache keyword with steroids, and it has the immesurable advantage of working.

If you are on 8.0 or above, there is practically no sense in setting 'CACHE'. The keep pool does it better, more reliably, and more effectively.

Regards
HJR  
> Another question.
> With multiple db writers and db block lru latches, how do the db
> writers get assigned to do all the work? The documentation says that
> they are assigned to the lru latches (working sets) in a round robin
> fashion. If that is the case, how is it possible for the db writers to
> be NOT equally loaded?
> Maybe I am missing some points here. Am I?
>
> rgds
> Daud
Received on Mon Aug 26 2002 - 15:17:07 CDT