Re: cache vs keep buffer pool

Hi Howard

Thanks for your advice. You certainly have been helpful and I am also learning a lot from the tech documents that you have written. I have another question here. When you do a full table scan of a big table (the number of blocks > db_block_buffers) that is going to flush out everything in the buffer cache and put blocks from that big table in there. Am I right?
Or is it just limited to db_file_multiblock_read_count at the cold end of the lru? And if I have multiple working sets by specifying db_block_lru_latches > 1 how is all that going to work? I guess I am still not exactly clear what happens in the buffer cache when a FTS on a very big table occurs.

rgds
Daud

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3d6a8c7c_at_dnews.tpgi.com.au>...
> 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 Tue Aug 27 2002 - 00:36:33 CDT