BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE pool parameters
deprecated_in Oracle 12c. Also see my related
keep_pool caching and
Automatic KEEP pool assignment.
There are alternatives to KEEP pool caching, including
SSD and in-memory column store.
To minimize disk I/O and maximize performance, we need to
look at caching (using the Oracle KEEP pool) all of your
small tables that experience frequent full-table scans.
Oracle SQL Cost-based optimizer will commonly perform a
full-table scan on small tables when it recognizes that a
multi-block read is faster than using an index.
According to Oracle documentation, these rules may be used
to identify KEEP pool candidates:
"A good candidate for a segment
to put into the KEEP pool is a segment that is smaller than
10% of the size of the DEFAULT buffer pool and has incurred
at least 1% of the total I/Os in the system.".
More concisely, any small table that is in high demand is
a good candidate for KEEP caching.
Depending on your database, caching important tables and
indexes can increase performance by up to 50x;
Note: Using parallel query on full-table scans will not
cache the data blocks in the KEEP pool.
But what about large objects? Respected Oracle guru
Karl Reitschuster has some notes on the benefits of
caching large objects in the KEEP pool with a 5x response
"We faced a problem on our company table querying
specific company types a full table scan could not
prevented. . . The company table is about 1.7G
large with about 6.5M of rows.
On our test database server two concurrent queries on
the company table resulted in > 60sec instead of 25sec.
And even 25 sec are too long. . .
Once loaded the table into the KEEP buffer cache the
result of the query response time was < 5 sec even with
Internally, it is
critical to cache small-table full-table scans because the
Oracle data buffer does not increase the touch count when
blocks from full-table scans are referenced.
small-table full-table scan blocks will age-out of the data
buffers very quickly, causing unnecessary disk I/O. I
believe that this is causing you additional disk I/O that
could be easily avoided.
Fortunately, it is
relatively easy to locate the small-table full-table scans
and cache them in the KEEP pool. You can query the
v$sql_plan view to see small-table full-table scans, and
automatically cache them in the KEEP pool using the
utilities from my book
Oracle_Tuning: The definitive_reference.
Finding candidates for the
Finding small-table, full-table scans is as
easy as running this
plan.sql script. It will give you a report showing
the table sizes and the number of full scans experienced by
Another method for identifying tables and indexes for the
KEEP pool examines the current blocks in the data buffer.
For this query, the rules are simple. Any object that
has more than 80% of its data blocks in the data buffer
could probably be fully-cached using the KEEP pool. Of
course, the standard LRU algorithm should keep
freuqently-referenced data blocks in memory.
It is highly unlikely that an undeserving table of index
would meet this criterion. Of course, you would need
to run this script at numerous times during the day because
the buffer contents change very rapidly.
The following script can be
run every hour via dbms_scheduler and automate the
monitoring of KEEP pool
candidates. Every time it finds a candidate, the DBA
will execute the syntax and adjust the total KEEP pool size
to accommodate the new object.
set pages 999
set lines 92
drop table t1;
create table t1 as
count(distinct file# || block#)
o.owner not in
bh.status != 'free'
count(distinct file# ||
In sum, there are two ways to
identify tables and indexes for full caching in the KEEP
pool. We start by explaining all of the SQL in the
databases looking for small-table, full-table scans.
Sizing the KEEP Pool
Once the tables and indexes have been loaded into the
KEEP buffer pool, the db_keep_cache_size parameter must be
increased by the total number of blocks in the migrated
The following script will total the number
of blocks that the KEEP pool requires, insuring 100 percent
data caching. The script doubles the total to allow for
growth in the cached objects and multiple block versions
within the data buffer. The DBA should run this script
frequently to make sure the KEEP pool always has a DBHR of
Here is a handy shell script to compute the
optimal size of the KEEP pool, or you could simply use the
SQL inside a dbms_scheduler job.
# First, we must set the
environment . . . .
^$ORACLE_SID:|cut -f2 -d':'`
' alter system set
db_keep_cache_size = '||trunc(sum(s.bytes)*1.5)||'
s.buffer_pool = 'KEEP';
Once the shell script is created, this crontab can be
used to daily re-size the KEEP pool for all objects within
the KEEP pool:
$ crontab -l
# Run the daily script to compute
the optimal KEEP Pool size
00 06 * * *
/xxxx/size_keep_pool.ksh > /tmp/size_keep_pool.lst
Here is the resulting command as it is executed.
alter system set
db_keep_cache_size =1048765 scope=both;
This script outputs the Oracle parameter that resizes the
KEEP pool for the next restart of the Oracle instance. The
parameter is placed in the init.ora file. Oracle10g
deprecates buffer_pool_keep is deprecated in favor of the
Pool Candidate Identification
The KEEP pool is an excellent storage location for
small-table, full-table scans. It can also be a good place
to store data blocks from frequently used segments that
consume a lot of block space in the data buffers. These
blocks are usually found within small reference tables that
are accessed through an index and do not appear in the
full-table scan report.
The x$bh internal view is
the only window into the internals of the Oracle database
buffers. The view contains much detailed information about
the internal operations of the data buffer pools. Both the
number of objects in a specific type and the number of
touches for that object type can be counted in the x$bh
table. It can even be used to create a snapshot of all the
data blocks in the buffer.
The hot_buffers.sql query
shown below utilizes the x$bh view to identify all the
objects that reside in blocks averaging over five touches
and occupying over twenty blocks in the cache. It finds
tables and indexes that are referenced frequently and are
good candidates for inclusion in the KEEP pool.
MUST connect as SYS to run this script
set lines 80;
set pages 999;
column myname heading 'Name'
column mytype heading 'Type'
b.owner not in
AVG(tch) > 5
COUNT(1) > 20;
script will only run on Oracle8i and subsequent versions.
This is because the tch column was not added until Oracle
The output from the hot_buffers.sql script is
shown next. It identifies the active objects within the data
buffers based on the number of data blocks and the number of
BLOCKS BUFFERS AVG_TOUCHES
---------- -------- -----------
TABLE SEC_BROWSER_PROPERTIES 80
The DBA must now decide whether the hot
objects are to be segregated into the KEEP pool. In general,
there should be enough RAM available to store the entire
table or index. Using the example, if consideration is given
to adding the page table to the KEEP pool, 104 blocks would
have to be added to the Oracle db_keep_cache_size parameter.
The results from this script will differ every time
it is executed because the data buffers are dynamic, and
data storage is transient. Some DBAs schedule this script as
often as every minute, if they need to see exactly what is
occurring inside the data buffers.
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.