Using the Oracle KEEP pool Oracle Tips by Burleson Consulting December 11, 2003 Don Burleson

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 full-table scans.

The 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, “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, a small table that is in high demand is a good candidate for KEEP caching.

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.

Hence, 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 below.

---