In Oraclewe can write scripts that automatically identifies
candidates for the KEEP pool and generates the syntax to move the
tables into the pool. In fact, all of the 10g benchmarks
utilize table caching techniques to get super-fast performance:
The placement criteria for tables and indexes into the KEEP buffer are
? Parameters may be adjusted in the script based upon needs.
full-table scans ? Oracle designates the table as small and chooses
a full-table scan over an index access.
Frequently-accessed tables ? The threshold for access can be
adjusted in the script.
residency ? Any table that has more than 80% of its blocks in the
data buffer should be cached in the KEEP pool.
There are two
approaches to identifying tables for the KEEP pool.
1. Identify tables
(and associated indexes) that are small and have frequent full-table
2. identify objects (tables, indexes) that have more than 80% of their
data blocks in the buffer.
You can write
scripts to use the v$bh and v$sql_plan to extract this information and
automatically generate and execute the KEEP pool assignment. Here
is a sample from my book:
count(distinct file# || block#) num_blocks
o.data_object_id = bh.objd
o.owner not in ('SYS','SYSTEM')
bh.status != 'free'
count(distinct file# || block#) desc
If you want
pre-written scripts for KEEP pool assignment I have them in my latest book ?Oracle Tuning: The Definitive Reference? by Rampant TechPress. It?s only $41.95(I don?t think it
is right to charge a fortune for books!) and you can buy it right now at