Re: Keep (Recycle) Buffer Question

Hi Peter,

Good question.

Apart from having a decent idea on how Oracle works under your particular OS, a good DBA will also have a decent understanding of the "data" and "applications" they look after. It's very difficult to do many important things right (such as setting appropriate storage/tablespace parameters, load balancing, pctfree/pctused, capacity management, creating appropriate data structures, index structures, blah, blah, blah ...) unless you understand your data. This is actually one of the hardest things I'm personally finding working from site to site.

There is no easy way as such to determine which table should go in which pool (although analysis of execution plans, views such as v$cache can help). It's one of those decisions that is best made with an excellent understanding of both the data and the method by which applications access the data. A decision that could, maybe even should be made *before* the structures are even implemented in the database.

Small(ish), regularly accessed objects should go in the KEEP pool and ideally the KEEP pool should be sized large enough to store all such objects. The size of this pool and the percentage of overall logically reads used in this pool is very important.

Large(ish), randomly accessed (as apposed to just rarely accessed) objects with little chance of block reuse could go into a RECYCLE pool to reduce "wastage" of memory as such blocks take their merry old time to naturally aged out of a the DEFAULT pool.

My recommendation. Research the executions plans in your DB, learn and study the characteristics of your data structures and how they are commonly used by your applications and start picking the most likeliest candidates for these pools. Based on your selections, sized each pool appropriately and monitor, monitor, monitor until you are satisfied appropriate selections have been made.

Cheers

Richard

"Peter Bruhn" <lbruhn_at_miss.wu-wien.ac.at> wrote in message news:al1ofe$mku$1_at_bird.wu-wien.ac.at...
> Hi,
>
> I am not a DBA, but I have followed the long thread of the
 cach/keep-buffer
> discussion a bit. We have performance problems in our DB all the time. So
 I
> checked, if we use different buffers for different DB-objects. We do not.
 What
> I have understood from the above discussion that it is useful to put small
> lookup tables (+indexes) and some large tables (+indexes) that are heavily
> used into a seperate buffer that is large enough to hold all these
 objects.
> This buffer can be called keep-buffer. Then you put large tables (+indexes
> [?]) that are used very seldom into a third rather small buffer, call it
 the
> recycle buffer.
>
> Now we have a large database with many, many tables. How do I identify
 what
> tables to put in what buffer. Do I have to make these decisions "by hand".
 Or
> is there any tool (query) to help me make this decision? Can I find out,
 what
> tables often whipe out the current cache and are not reused, so I can put
 them
> to the recycle cache? Can I find out what tables are whiped out often and
 then
> reloaded soon, so I can put into the keep cache?
>
> Thanks for any hints, comments, flames,
> Peter
Received on Tue Sep 03 2002 - 09:31:16 CDT