Candidates for Keep POOL.
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;
