oracle database expert performance tuning buffer pools keep recycle sqlplus query TOAD system trigger plsql programming
Edward Stoever is your
DATABASE-EXPERT
Computer solutions for businesses of all sizes!
(818) 528-4111
 Home  Résumé  Articles  Portfolio  Contact  YouTube 
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;
But, suppose that querying BUFFER_POOL_HITS shows your Keep pool is maximized at 99.99% hits. Does that mean that you have filled it up with blocks? Can you cram any more blocks into it without hurting performance?

The best use of the keep pool is to load it up with the maximum number of blocks. Once an object is loaded in, it remains there until the database is shut down.

On my production database, I have placed many tables into the keep pool, especially tables that are queried constantly and which have just a few rows (or no rows at all!). We have dozens of tables that meet this criteria. They are constantly being accessed because they contain the basic business rules for the way things are done. A secondary reason to keep these tables in the Keep pool is to get them out of the Default pool. That's because tiny tables that never get aged out tend to cause fragmentation of the Default pool.

Most DBA's determine the size of their keep pool by looking at the total blocks in the DBA_SEGMENTS view for objects that they have assigned to the keep pool by using a query like this:
select sum(blocks) from dba_segments where buffer_pool='KEEP';
Suppose you get back a total of 7200 blocks from this query and that your default block size is 8k. (7200 * 8k = 56.25 megabytes of RAM needed for your Keep Pool.)

You might think something like this... OK, just size the keep pool at 64 megs. Done. That attitude is fine if you have lots and lots of RAM to work with. - Generally speaking, we do not. Every megabyte is precious.

But wait! Remember those tables that have just a few rows, or no rows at all? How many blocks are assigned to each of those tables when they are created? (In most cases, the answer is one extent, or 8 blocks!) Are all of those blocks loaded into memory when the table is queried? No. Oracle only loads the table definition, and the few rows that make up the table, likely just one block. In fact, for any table on the database, there are almost certainly some blocks in the most recently allocated extent that contain no data and will never be loaded into RAM.

So, the proper way to determine how many blocks are needed for the keep pool is to ask this question.... How many blocks in my keep pool are NOT being used?

That question is a little bit harder to answer. Oracle provides us with the V$BH view which tells us what each buffer is doing at any given moment. But it does not tell us if each buffer is in the Default, Keep, Recycle or one of the non-default-sized-Default pools. For example, if Block# 5120 is free, we cannot tell what pool it is assigned to!

Fortunately, if a buffer is in use, we can determine what pool it is assigned to. Here, we will see a query I wrote that will do just that. Because this query can take a while to finish, I prefer to create a static table from this query so that the expense of the query is run just one time:
CREATE TABLE temp1 TABLESPACE tools AS
SELECT   
/* ©2004 by Edward Stoever,  */ 
         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;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE NUM_BLOCKS TABLESPACE_NAME BUFFER_POOL
FIMSMGR FABINVH   TABLE 3898 DEVELOPMENT DEFAULT
FIMSMGR FARINVC   TABLE 2331 DEVELOPMENT DEFAULT
TAISMGR TBRACCD   TABLE 942 DEVELOPMENT DEFAULT
FIMSMGR PK_FARINVC   INDEX 916 INDX DEFAULT
FIMSMGR FGBTRNI   TABLE 619 DEVELOPMENT KEEP
SATURN SFRSTCR   TABLE 599 DEVELOPMENT DEFAULT
FAISMGR RPRAWRD   TABLE 515 DEVELOPMENT DEFAULT
SATURN SARADAP   TABLE 505 DEVELOPMENT KEEP
SATURN SPRIDEN_KEY_INDEX   INDEX 391 INDX32 DEFAULT
GENERAL GJBPRUN   TABLE 379 DEVELOPMENT KEEP
FIMSMGR PK_FGBBAVL   INDEX 342 INDX DEFAULT

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:
SELECT
     /* ©2004 by Edward Stoever,   
     This query will need to be edited for proper use on 
     different databases, depending on default block size 
     and non-default block sizes */ 
 '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:

DESCRIPTION MEGABYTES
KEEP POOL MEGABYTES IN USE: 30.234375
KEEP POOL MEGABYTES CONFIGURED: 32
KEEP POOL MAXIMUM USABLE AREA: 30.9375
RECYCLE POOL MEGABYTES IN USE: 15.1640625
RECYCLE POOL MEGABYTES CONFIGURED: 16
RECYCLE POOL MAXIMUM USABLE AREA: 15.46875
8k DEFAULT POOL MEGABYTES IN USE: 141.96875
8k DEFAULT POOL MEGABYTES CONFIGURED: 160
8K DEFAULT POOL MAXIMUM USABLE AREA: 154.6875
32k DEFAULT POOL MEGABYTES IN USE: 31.65625
32k DEFAULT POOL MEGABYTES CONFIGURED: 32
32K DEFAULT POOL MAXIMUM USABLE AREA: 31.6875

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:

POOL NAME Size in MB
shared pool free memory  24.5974426269531
large pool free memory  16
java pool free memory  10.4453125


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.
©2017 Edward Stoever