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

Donald K. Burleson

Oracle Tips

Pinning data rows in Oracle buffer

You definitely want to keep frequently accessed data in the buffer/data cache, so that queries needing that data have the fastest possible access to it.  How can you accomplish this? 

If you are on Oracle version 7, you can utilize the cache property of a table, which specifies that any blocks retrieved from the table during a table scan be placed at the most recently used end of the LRU list in the cache.  This does not mean that they will remain in the cache forever, but at least you are giving the blocks a fighting chance to stay in the cache over other data blocks.  An example of enabling the cache property for a table named ERADMIN.ADMISSION would be:

alter table eradmin.admission cache;

With Oracle version 8 and above, there is a better option.  You can enable the keep pool, a specialized area carved out of the overall buffer/data cache that allows you to retain needed data blocks on a more permanent basis.  The buffer_pool_keep parameter controls the size of this cache in Oracle8, while the db_keep_cache_size parameter handles this cache allotment in Oracle9i and above.  While you do not have to do any special cache sizing to enable the cache property of a table, using the keep pool entails some planning with respect to how much data you want to pin in memory. 

An example of placing a table named ERADMIN.ADMISSION into the keep pool would be:

alter table eradmin.admission storage(buffer_pool keep);

As you might have already surmised, small objects and lookup tables are the best candidates for placement in the keep buffer pool or for enabling their cache property.

However, there are other caches you can use to enhance the performance of your database as well. 

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:





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.