Donald K. Burleson Oracle Tips

Secrets of the Oracle KEEP Pool

If you have properly cached your tables in Oracle7, you must change the caching option in Oracle8 to utilize the new KEEP pool.  The following query will determine the size for your keep pool based upon the cached table from your Oracle7 installation.  However, note that Oracle Technical Support states that a candidate for the KEEP pool is any table that is <10% the size of the default pool and comprises more than 1% of the total I/O.

You can ASSIGN which buffer cache a table uses using the BUFFER_POOL KEEP | RECYCLE | DEFAULT option of the STORAGE clause.  Once the RECYCLE and KEEP pools are enabled, you can assign table to pools with the ALTER TABLE COMMAND.  For example:

ALTER TABLE cached_table STORAGE (BUFFER_POOL KEEP);

NOTE:  You MUST re-assign all cached tables to the KEEP pool.  The CACHE column of DBA_TABLES is obsolete in Oracle8 and does nothing.

Initially you will want to size the KEEP pool according to the sum of blocks consumed by cached tables.  The following script will get you this sizing: (size_initial_keep_pool.sql)

set pages 9999;

set feedback off;

set heading off;

prompt The following will initially size your init.ora KEEP POOL,

prompt based on Oracle7 values

prompt

select

'BUFFER_POOL_KEEP = ('||sum(s.blocks)||',2)'

from

   dba_tables   t,

   dba_segments s

where

   t.table_name = s.segment_name

and

   t.cache like  '%Y%'

;

If you are already in Oracle8, and want to make sure that you have enough blocks in your KEEP pool to hold all cached objects, you can run the following script (size_keep_pool.sql).

prompt The following will size your init.ora KEEP POOL,

prompt based on Oracle8 KEEP Pool assignment values

prompt

select

'BUFFER_POOL_KEEP = ('||sum(s.blocks)||',2)'

from

   dba_segments s

where

   s.buffer_pool = 'KEEP';

;

-- The following will initially move cached objects

prompt The following will assign the cached tables to your new KEEP pool

select

'ALTER TABLE '||owner||'.'||table_name||' STORAGE (BUFFER_POOL KEEP);'

from

   dba_tables

where

cache like  '%Y%'

;

Sample output:

The following will size your init.ora KEEP POOL

BUFFER_POOL_KEEP = (171,2)

Determining if a table is in the KEEP pool

The following script is from Oracle support, but it does not work in Oracle 8.1.5 because of a dropped column:

select 'KEEP' POOL, o.name, count(buf#) BLOCKS
from obj$ o, x$bh x
where o.dataobj# = x.obj
a . .
group by 'DEFAULT',o.name
union all
select 'RECYCLE' POOL, o.name, count(buf#) BLOCKS
from obj$ o, x$bh x
where o.dataobj# = x.obj
. . .
group by 'RECYCLE',o.name;

The complete scripts for KEEP pool assignment are in the code deport for my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm