Re: Keep Pool Question

Brian wrote:
>
> At the moment only the default pool is in use. I am considering
adding
> 1-2 gig of ram exclusively for the keep pool. I have a list of
> frequently accessed objects and I've been monitoring v$bh, so I know
> what's in the buffer cache most of the time. It would be pretty easy
> to find 1-2 gig of objects that will fit in nicely in the keep pool.

Brian, the keep pool does NOT need to be the same size as the objects "hinted" to go into it. Don't forget that the keep pool is part of the buffer cache, and a cache is by definition something that keeps only "the most accessed data pages" (or "data blocks", in Oracle parlance).

Blocks/pages. Not tables.

IOW, it's got nothing to do with the ENTIRE table: if you never read in all blocks of a table assigned to KEEP, you will NOT get all data blocks of that table in the KEEP pool. Not at least in the current versions of Oracle (10g excepted, never tried it there).

So, having more tables defined to KEEP than actually fit in the available keep pool size is not necessarily bad. What you have to look at is the WORKING SET of used blocks. Ie, at any given moment during the processing cycle (be that hourly, daily, weekly, whatever), how many table blocks of what tables are being used or accessed. That is the size you need for your cache pools in order to achieve maximum gain from the memory assigned to them.

> Any recommendations? Is 1-2gig of keep pool to much? Any problems
> with putting 1 or 2 of the large tables and a handful of the smaller
> tables in the keep pool?

I wouldn't (normally) put high update activity tables in a keep pool. Having said that, it's possible to do. But I've never tried it with very large and active tables.

Experiment? Try? (but NOT with production!!!!) Received on Wed Mar 09 2005 - 01:23:13 CST