Re: cache vs keep buffer pool

Hi Daud,

No, a large FTS does not flush out the entire contents of the buffer cache (thankfully).

Tables which are "CACHE"d or tables with are so small that Oracle decides to cache them anyway get placed in the MRU portion of the LRU list. Large tables get loaded into the LRU end of the LRU list and so get overwritten (quite possibly by other blocks in the FTS). Something approaching 2*the number of parallel processes executing the FTS* the DB_FILE_MULTIBLOCK_READ_COUNT number of blocks are cached at a time (although there are a number of ifs and buts to this figure).

But not the whole cache for obvious reasons.

Cheers

Richard
"Daud" <daud11_at_hotmail.com> wrote in message news:f0bf3cc3.0208262136.4ad56f19_at_posting.google.com...
> 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 - 01:53:02 CDT