ORACLE Maximizing the Keep Pool and Other Buffer Pools.

By Edward Stoever

Most DBA's understand how to set up a keep pool, a recycle pool, and the 2k, 4k, 8k, 16k and 32k block size default pools. I assume that you know how to do this and what each pool is for. This article will show you how to really determine whether you are maximizing the use of these pools.

With Oracle, it is easy to determine if you are getting the hits you want from each pool. Here is a simple view you can create that will help you to do just that:

CREATE OR REPLACE FORCE VIEW SYSTEM.BUFFER_POOL_HITS
(NAME, BLOCK_SIZE, HIT_RATIO)
AS
SELECT
 NAME, TO_CHAR (block_size / 1024) || 'k' AS "BLOCK_SIZE",
       1 - (physical_reads / (db_block_gets + consistent_gets)) AS "HIT_RATIO"
FROM
 v$buffer_pool_statistics
WHERE
 db_block_gets + consistent_gets > 0;

CREATE PUBLIC SYNONYM BUFFER_POOL_HITS FOR SYSTEM.BUFFER_POOL_HITS;

select sum(blocks) from dba_segments where buffer_pool='KEEP';

CREATE TABLE temp1 TABLESPACE tools AS
SELECT
/* ©2004 by Edward Stoever, edward@database-expert.com */
         o.owner owner,
         o.object_name object_name,
         o.subobject_name subobject_name,
         o.object_type object_type,
         COUNT(DISTINCT FILE# || BLOCK#) num_blocks,
         s.tablespace_name tablespace_name,
         s.buffer_pool buffer_pool
    FROM DBA_SEGMENTS s, DBA_OBJECTS o, v$bh bh
   WHERE o.data_object_id = bh.objd
     AND s.segment_name = o.object_name
     AND o.owner NOT IN ('SYS', 'SYSTEM')
     AND bh.status != 'free'
GROUP BY o.owner,
         o.object_name,
         o.subobject_name,
         o.object_type,
         s.tablespace_name,
         s.BUFFER_POOL
ORDER BY COUNT (DISTINCT FILE# || BLOCK#) DESC;

A sampling of the first few rows of this table is here:

select * from temp1;

This table is telling us how many blocks are in memory for each object that is in memory. It also tells us what tablespace and buffer pool each object is assigned to, necessary information to determine the free space available in any given pool. To determine the free buffers of any pool, we can run the following query developed and copyright protected by me.  The query will need to be edited for proper use on different databases depending on default block size and non-default block sizes:

SELECT
  'KEEP POOL MEGABYTES IN USE: ' AS DESCRIPTION,
      (SUM (num_blocks) * 8 ) / 1024 AS MEGABYTES
  FROM temp1
 WHERE BUFFER_POOL = 'KEEP'
UNION ALL
SELECT 'KEEP POOL MEGABYTES CONFIGURED: ', TO_NUMBER (VALUE) / 1024 / 1024
  FROM v$parameter
 WHERE NAME = 'db_keep_cache_size'
UNION ALL
SELECT 'KEEP POOL MAXIMUM USABLE AREA: ',(set_msize * block_size) / 1024 / 1024
  FROM v$buffer_pool_statistics
 WHERE NAME = 'KEEP'
UNION ALL
SELECT 'RECYCLE POOL MEGABYTES IN USE: ',
       (SUM (num_blocks) * 8 ) / 1024
  FROM temp1
 WHERE BUFFER_POOL = 'RECYCLE'
UNION ALL
SELECT 'RECYCLE POOL MEGABYTES CONFIGURED: ', TO_NUMBER (VALUE) / 1024 / 1024
  FROM v$parameter
 WHERE NAME = 'db_recycle_cache_size'
 UNION ALL
 SELECT 'RECYCLE POOL MAXIMUM USABLE AREA: ',(set_msize * block_size) / 1024 / 1024
  FROM v$buffer_pool_statistics
 WHERE NAME = 'RECYCLE'
 UNION ALL
SELECT '8k DEFAULT POOL MEGABYTES IN USE: ',
       (SUM (num_blocks) * 8 ) / 1024
  FROM temp1
 WHERE BUFFER_POOL = 'DEFAULT' AND tablespace_name NOT LIKE '%32%'
UNION ALL
SELECT '8k DEFAULT POOL MEGABYTES CONFIGURED: ', TO_NUMBER (VALUE) / 1024 / 1024
  FROM v$parameter
 WHERE NAME = 'db_cache_size'
 UNION ALL
 SELECT '8K DEFAULT POOL MAXIMUM USABLE AREA: ',(set_msize * block_size) / 1024 / 1024
  FROM v$buffer_pool_statistics
 WHERE NAME = 'DEFAULT' AND block_size = 8192
 UNION ALL
SELECT '32k DEFAULT POOL MEGABYTES IN USE: ',
       (SUM (num_blocks) * 32 ) / 1024
  FROM temp1
 WHERE BUFFER_POOL = 'DEFAULT' AND tablespace_name LIKE '%32%'
UNION ALL
SELECT '32k DEFAULT POOL MEGABYTES CONFIGURED: ',
       TO_NUMBER (VALUE) / 1024 / 1024
  FROM v$parameter
 WHERE NAME = 'db_32k_cache_size'
 UNION ALL
 SELECT '32K DEFAULT POOL MAXIMUM USABLE AREA: ',(set_msize * block_size) / 1024 / 1024
  FROM v$buffer_pool_statistics
 WHERE NAME = 'DEFAULT' AND block_size = 32768;

The results of that query might look something like this:

Now, lets compare those results to the more traditional method of sizing the Keep Pool:

SELECT (SUM (blocks) * 8) / 1024 AS "MEGABYTES of KEEP POOL OBJECTS"
  FROM DBA_SEGMENTS
 WHERE BUFFER_POOL = 'KEEP';

MEGABYTES of KEEP POOL OBJECTS
56.25

Wow! It looks like I am cramming 56 megabytes of objects into a keep pool that is only 32 megs and I am still getting a long term sustained hit ratio of 99.99%! Remember, this is because the size of an object on disk includes unused blocks that will never get loaded into the Keep pool!

What to do...

I have presented you with an excellent method to determine how many blocks are being used in each of your pools. The best way to make use of this information is to run through this excercise at different times during the day. You will learn what objects are in use, and when. You will learn that the keep pool seldom changes, which is the way it should be. Don't place objects into the keep pool that you expect to grow over time. The keep pool is the pool for frequently accessed tables that are small and static. Running through this excercise shortly after starting up the database will produce results that are not valid.

As a final note, if you need more RAM to enlarge one of your pools, try the following query:

SELECT pool, name, bytes/1024/1024 "Size in MB"
FROM v$sgastat
WHERE name='free memory';

which might return results like this:

You may find that you can decrease the use of memory in one or more areas of the SGA in order to make use of it in one of the buffer pools. I do not suggest making major changes to the SGA or the Buffer Pools while the database is open, even if Oracle says it can be done. Shutdown the database and open it with

startup nomount pfile='/<path to the init.ora file>/init<SID>.ora'

which will allow you to try out parameters before opening up the database. Shutting down from here is quick and painless. You can then edit the pfile and try it again and again until you are satisfied that each pool is the proper size. You may be surprised to learn that Oracle may assign chunks of RAM to a buffer pool that are differnet from what you configure (for example you may ask for 14 megs for db_recycle_cache_size in the pfile but Oracle makes it 16 megs). Query the v$parameter table after starting up to see if your changes are being implimented by Oracle in the way you wish. The v$parameter view is available even if the database is not mounted or open.