Automatically assign tables

http://www.dba-oracle.com/oracle_tips_benchmarks.htm
 
The placement criteria for tables and indexes into the KEEP buffer are straightforward:

• Small tables – Parameters may be adjusted in the script based upon needs.
   

• Small-table 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.
   

• High buffer 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 scans.

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:

select
   o.owner                         owner,
   o.object_name                   object_name,
   o.subobject_name                subobject_name,
   o.object_type                   object_type,
   count(distinct file# || block#) num_blocks
from
   dba_objects o,
   v$bh        bh
where
   o.data_object_id = bh.objd
and
   o.owner not in ('SYS','SYSTEM')
and
   bh.status != 'free'
group by
   o.owner,
   o.object_name,
   o.subobject_name,
   o.object_type
   order by
   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 this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm