Hi Everyone,

I have few question's related to Keep Pool,

1) If I cache a object in KEEP pool, does Oracle prefers FULL table scans on that table?
2) I wrote the following query to Identify the objects in Buffer Cache. Is it the perfect way to get the list of candidates for KEEP Pool.

Code:
select a.owner, a.object_name, b.sub_object, a.object_type,
	b.num_rows, b.blocks obj_blks,
	count(a.object_name) buff_blks, trunc(avg(a.touches)) touches
from
(
select b.indx, b.hladdr,b.ts# tblspace, b.file# fileid, b.dbablk blockid,
	b.obj objid, u.name owner, o.name object_name, o.subname subobject_name,
       decode (o.type#, 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 
                19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 
                34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 
                39, 'LOB PARTITION', 40, 'LOB SUBPARTITION', 'UNDEFINED' 
              ) object_type, b.tch touches
from x$bh b, obj$ o, user$ u 
where b.obj = o.dataobj# 
and   o.owner# = u.user# 
)a,
(
select owner object_owner, table_name object, null sub_object, buffer_pool, 
	num_rows, blocks 
from dba_tables 
where buffer_pool is not null 
union 
select table_owner object_owner, table_name object, partition_name sub_object, buffer_pool, 
	num_rows, blocks 
from dba_tab_partitions 
where buffer_pool is not null 
union 
select table_owner object_owner, table_name object, subpartition_name sub_object, buffer_pool, 
	num_rows, blocks 
from dba_tab_subpartitions 
where buffer_pool is not null 
union 
select owner object_owner, index_name object, null sub_object, buffer_pool, 
	num_rows, 1+LEAF_BLOCKS blocks 
from dba_indexes 
where buffer_pool is not null 
union 
select index_owner object_owner, index_name object, partition_name sub_object, buffer_pool, 
	num_rows, 1+LEAF_BLOCKS blocks 
from dba_ind_partitions 
where buffer_pool is not null 
union 
select index_owner object_owner, index_name object, subpartition_name sub_object, buffer_pool, 
num_rows, 1+LEAF_BLOCKS blocks 
from dba_ind_subpartitions 
where buffer_pool is not null 
)b 
where owner not in ( 'SYS', 'SYSTEM', 'PERFSTAT')
and a.object_name = b.OBJECT AND a.owner = b.object_owner
and nvl(a.subobject_name, 'NULL') = nvl(b.sub_object
, 'NULL')
group by b.buffer_pool, a.owner, b.sub_object, a.object_type, a.object_name, b.num_rows, b.blocks
order by buff_blks desc, touches desc, obj_blks asc, num_rows desc;