According to Oracle documentation. "A good candidate for a segment to put into the KEEP pool {XE "KEEP pool"} is a segment that is smaller than 10 percent of the size o( the DEEAULT buffer pool and has incurred at least 1 percent 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. How can we locate small tables that are subject to full-table scans? The best method is to search the SQL that is currently in the library cache. Oracle can then generate a report that lists all full-table scans in the database at that time. The script below examines the execution plans of plan9i.sql and reports on the frequency of full-table scans.
展开▼
