Re: Usage of Multiple Buffer Pools

Sergey Adamenko wrote:
>
> Hi, Everyone!
>
> I'm reading "Oracle8i Designing and Tuning for Performance" guide.
> In chapter "Tuning Memory Allocation"\"Tuning the Buffer Cache"
> DBA is advised to consider the possibility of Tuning Multiple Buffer
> Pools.
>
> Now I want to quote an article from the guide:
>
> | A good candidate for a segment to put into the RECYCLE buffer pool is
> | a segment that is at least twice the size of the DEFAULT buffer pool and
> | has incurred at least a few percent of the total I/Os in the system. A good
> | candidate for a segment to put into the KEEP pool is a segment that is
> | smaller than 10% of the size of the DEFAULT buffer pool and has
> | incurred at least 1% of the total I/Os in the system.
> | The trouble with these rules is that it can sometimes be difficult to
> | determine the number of I/Os per segment if a tablespace has more than
> | one segment. One way to solve this problem is to sample the I/Os that
> | occur over a period of time by selecting from V$SESSION_WAIT to
> | determine a statistical distribution of I/Os per segment.
> | -----
> | Oracle8i Designing and Tuning for Performance
> | Release 2 (8.1.6)
> | Part Number A76992-01
>
> And concerning Tuning Multiple Buffer Pools I have the
> following questions:
>
> 1. How can I sample I/O per segment other than pinging
> V$SESSION_WAIT?
> In V$SESSION_WAIT I can capture only current waits and if I/O is
> taking place at the moment, and save FILE# and BLOCK#. But this
> means I should ping V$SESSION_WAIT very frequently.
>
> 2. A there some other principles for identifying candidate segments for
> RECYCLE and KEEP pool?
>
> 3. Are Multiple Buffer Pools used nowadays? I new about the
> possibility of using Multiple Buffer Pools at least for 2 year,
> but never used it or sew other DBA used it.
>
> Thank you!
>
> Sergey Adamenko.

I think multiple buffer pools can be useful as a means of spreading out the latching for blocks. The changes in 8i buffer cache management with touch counts (imho) renders the original idea of 'keep' and 'recycle' somewhat obsolete. I think they are better referred to as simply extra pools of memory (although you'd need to tweak a couple of parms to make them all work in the same way)

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"