Re: cache vs keep buffer pool

"Daud" <daud11_at_hotmail.com> wrote in message news:f0bf3cc3.0208260711.5490ca76_at_posting.google.com...
> 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?
>
> 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

As far as DBWR loading is concerned, consider the following setup:

2 buffer pools, DEFAULT and KEEP
3 LRU latches assigned as follows: 2 to DEFAULT, 1 to KEEP 2 DBWR processes

Latches are assigned SEQUENTIALLY to pools and ROUND ROBIN to DBWRs:

LRU1 => DEFAULT => DBWR1
LRU2 => DEFAULT => DBWR2
LRU3 => KEEP        => DBWR1

Assume each working set within a pool presents essentially identical workload, so the two DBWR processes do the same amount of work from the DEFAULT pool. However, DBWR1 additionally is handling all the work from the KEEP pool, hence the imbalance.

Avoid the potential for this imbalance by always assigning a number of LRU latches to each pool that is some multiple of the number of DBWRs.

HTH ps - In Oracle 9i don't assign latches explicitly and allow the system to default based on the number of DBWRs. Received on Mon Aug 26 2002 - 16:43:13 CDT