Re: Keep Pool Question

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Mon, 07 Mar 2005 22:02:41 +0100
Message-ID: <ovfp21hbg63u19hbc21ufjeneulmof4okc@4ax.com>

On 7 Mar 2005 12:43:00 -0800, "Brian" <brian_at_databaseknowledge.com> wrote:

>Many people suggest that the keep pool is ideal for small tables which
>are accessed frequently and the keep pool should be sized large enough
>to hold all of the objects designed for the pool. I've never
>actually used the keep pool.
>
>I am working with a 2 node 9iR2 RAC db 130G in datafiles, each node has
>8G of ram. At the moment the buffer cache is 3.3G. The db is running
>fine, no complaints from users, buffer cache hit ratio is always > 97%.
> The db queried like an OLTP system and data is loaded 24x7 in an
>non-bulk fashion.
>
>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.
>
>There are 3 tables which are 630M, 336M and 1152M which are constantly
>being queried/insered/upated/deteted. And there are a bunch of smaller
>tables which are accessed a lot.
>
>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?
>
>Brian

Basically, a buffer cache of 3.3 G is clearly outrageous. It results in

- excessive paging on the server
-more cache buffer chain latches
- less effective database writer.

Oracle recommends the *SGA* shouldn't be any bigger than one third of physical RAM
Finally, apart from Donald Burleson and Richard Niemic, no one any longer believes the main indicator of the performance of the system is the BCHR.

So before you start throwing more memory at the problem, you probably should

- tune the application
- tune the application
- tune the application
- and (did I say tune the application) look the buffer pool advice

available in Oracle 9i what reducing it would cost you.

You should definitely NOT increase the buffer cache any further, but you should redistribute the 3.3 G buffer pool over the keep, recycle and default caches.

--
Sybrand Bakker, Senior Oracle DBA

Received on Mon Mar 07 2005 - 15:02:41 CST