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 the Assignment of Oracle KEEP Pool Contents

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.

  • Use the KEEP pool if the object consumes more than 10% of the total size of the data buffer.

  • Use the KEEP pool if the object consumes more than 50% of the object already resides in the data buffer (according to an x$bh query).

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 following script can be run every hour via dbms_job, 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.

See code depot for full scripts

spool keep_syn.lst


drop table t1;


create table t1 as

. . .


   dba_objects  o,

   v$bh         bh


. . .

order by

   count(distinct file# || block#) desc




. . .



   dba_segments s


. . .


   (sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80



spool off;

Here is a sample of the output from this script.

alter TABLE BOM.BOM_DELETE_SUB_ENTITIES storage (buffer_pool keep);
alter TABLE BOM.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);
alter INDEX BOM.CST_ITEM_COSTS_U1 storage (buffer_pool keep);
alter TABLE APPLSYS.FND_CONCURRENT_PROGRAMS storage (buffer_pool keep);
alter TABLE APPLSYS.FND_CONCURRENT_REQUESTS storage (buffer_pool keep);
alter TABLE GL.GL_JE_BATCHES storage (buffer_pool keep);
alter INDEX GL.GL_JE_BATCHES_U2 storage (buffer_pool keep);
alter TABLE GL.GL_JE_HEADERS storage (buffer_pool keep);
alter TABLE INV.MTL_DEMAND_INTERFACE storage (buffer_pool keep);
alter INDEX INV.MTL_DEMAND_INTERFACE_N10 storage (buffer_pool keep);
alter TABLE INV.MTL_ITEM_CATEGORIES storage (buffer_pool keep);
alter TABLE INV.MTL_ONHAND_QUANTITIES storage (buffer_pool keep);
alter TABLE INV.MTL_SUPPLY_DEMAND_TEMP storage (buffer_pool keep);
alter TABLE PO.PO_REQUISITION_LINES_ALL storage (buffer_pool keep);
alter TABLE AR.RA_CUSTOMER_TRX_ALL storage (buffer_pool keep);
alter TABLE AR.RA_CUSTOMER_TRX_LINES_ALL storage (buffer_pool keep);
alter INDEX WIP.WIP_REQUIREMENT_OPERATIONS_N3 storage (buffer_pool keep);

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. Next, we repeatedly examine the data buffer cache, seeing any objects that have more than 80% of their blocks in RAM. Next, let’s finish the job and see how to re-size the KEEP pool to accommodate your new objects.


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.