Re: Oracle Keep Pool

From: Charles Hooper <>
Date: Wed, 25 Nov 2009 12:55:19 -0800 (PST)
Message-ID: <>

On Nov 25, 12:41 pm, joel garry <> wrote:
> On Nov 25, 4:47 am, Charles Hooper <> wrote:
> > The KEEP pool, RECYCLE pool, and DEFAULT pool utilize different goals
> > (algorithms) for keeping blocks in memory.  Any table or index block
> Do you have a  reference for this?  I was under the impression the
> only difference is whether you can use the cache keyword, the naming
> was purely arbitrary.  I may be out of date, of course.  But see the
> ask tom you quoted... the goal is just the desire of the DBA to keep
> segments with different access patterns segregated to  avoid pushing
> out segments that should be kept.
> jg

Joel, thanks for asking for confirmation - sure beats letting someone repeat the same incorrect information multiple times.

Well, I might be remembering things wrong. It seems that there are differing opinions. It can definitely be said that there is a definite difference when the SGA_TARGET parameter is set, as the DEFAULT buffer pool may shrink as a result of hard parsing a large number of non-reusable SQL statements while the KEEP and RECYCLE pools remain the same size. But, that is not good enough.

Some quotes from the documentation: "KEEP: Specify KEEP to put blocks from the segment into the KEEP buffer pool. Maintaining an appropriately sized KEEP buffer pool lets Oracle retain the schema object in memory to avoid I/O operations. KEEP takes precedence over any NOCACHE clause you specify for a table, cluster, materialized view, or materialized view log.

RECYCLE: Specify RECYCLE to put blocks from the segment into the RECYCLE pool. An appropriately sized RECYCLE pool reduces the number of objects whose default pool is the RECYCLE pool from taking up unnecessary cache space.

DEFAULT: Specify DEFAULT to indicate the default buffer pool. This is the default for objects not assigned to KEEP or RECYCLE."

OK, so setting an object to use the KEEP buffer cache automatically
over-rides the NOCACHE clause.  OK, so where I am going with this?
"Buffers and Full Table Scans:
When buffers must be read from disk, the database inserts the buffers
into the middle of the LRU list. In this way, hot blocks can remain in
the cache so that they do not need to be read from disk again.

A problem is posed by a full table scan, which sequentially reads all
rows under the table high water mark (see "Segment Space and the High
Water Mark"). Suppose that the total size of the blocks in a table
segment is greater than the size of the buffer cache. A full scan of
this table could clean out the buffer cache, preventing the database
from maintaining a cache of frequently accessed blocks.

Blocks read into the database cache as the result of a full scan of a
large table are treated differently from other types of reads. The
blocks are immediately available for reuse to prevent the scan from
effectively cleaning out the buffer cache.

In the rare case where the default behavior is not desired, you can
change the CACHE attribute of the table. In this case, the database
does not force or pin the blocks in the buffer cache, but ages them
out of the cache in the same way as any other block. Use care when
exercising this option because a full scan of a large table may clean
most of the other blocks out of the cache."

OK, so if a large table is read into the DEFAULT buffer cache, the
blocks are brought into the cold end of the LRU, unless the CACHE
attribute is set.

This is an old thread, so it might not apply:


This thread starts out describing memory limits on 32 bit Windows and
eventually switches to a discussion of the DEFAULT, KEEP, and RECYCLE
pool - including a test case I provided a couple years ago (might be
interesting to re-run the test case again to see if there are faults
in the test case).


In this thread, after prompting by Jonathan Lewis, I noticed that the
number of blocks cached by the test table and index gradually
decreased (when checked every 60 seconds) once the query completed
(the database instance was otherwise idle) - might be interesting to
see if the same behavior is present when the objects are located in
the KEEP pool:

If there are counter-points or counter test cases to the above I would
like to see those test cases - I could certainly be wrong.

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Nov 25 2009 - 14:55:19 CST

Original text of this message