Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Automating Oracle KEEP Pool Assignment

As we noted earlier, the Oracle documentation states “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”.

It is easy to locate segments that are less than 10% of the size of their data buffer, but Oracle only has v$segstat to track I/O at the segment level. To get around this issue, some DBAs place each segment into an isolated tablespace so that the AWR can show the total I/O, but you can also use STATSPACK (with level 7 snapshots) and the AWR both capture and report segment level statistics. These statistics include the physical reads in 9i, and the number of segment scans in 10g.

Since the idea of the KEEP is to fully cache the object, we want to locate those objects that are small and experience a disproportional amount of I/O activity. Using this guideline, there are two approaches, and unlike the recommendation from the Oracle documentation, these approaches can be completely automated:

  • Cache tables & indexes where the table is small (<50 blocks) and the table experiences frequent full-table scans.

  • Cache any objects that consume more than 10% of the size of their data buffer.

To identify these objects, we start by explaining all of the SQL in the databases looking for small-table, full-table scans. Next, we can repeatedly examine the data buffer cache, locating all objects that have more than 80% of their blocks in the data buffer cache.

The first method that uses v$sql_plan to examine all execution plans, searching for small-table, full-table scans, is found in get_keep_pool.sql. This can automatically generate the KEEP syntax for any small table (you adjust the table size threshold) for tables that have many full-table scans.

See code depot for full scripts

-- Create KEEP Pool syntax for small,


spool keep_syntax.sql


-- ***********************************************************

-- First, get the table list

-- ***********************************************************


. . .

   dba_tables   t,

   dba_segments s,

   v$sqlarea    a,

   (select distinct

. . .


. . .


   s.blocks < 50

group by

   p.owner,, t.num_rows, s.blocks


-- ***********************************************************

-- Next, get the index names

-- ***********************************************************


. . .


   owner||'.'||table_name in





. . .




spool off;

By running this script, we can use the Oracle9i v$ views to generate our suggestions for the KEEP syntax, based on the number of blocks in the object.

alter index DING.PK_BOOK storage (buffer_pool keep);
alter table DING.BOOK storage (buffer_pool keep);
alter table DING.BOOK_AUTHOR storage (buffer_pool keep);
alter table DING.PUBLISHER storage (buffer_pool keep);
alter table DING.SALES storage (buffer_pool keep);

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 should probably be fully cached.

It is highly unlikely that an undeserving table or 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 above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.

Complete Oracle Script Collection Available

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 680 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.