Oracle KEEP Pool

The placement criteria for tables and indexes into the KEEP buffer are straightforward,High buffer residency Any table that has more than 80% of its blocks in the data buffer should be cached in the KEEP pool.The approach to identifying tables for the KEEP pool is simple. All objects that have more than 80% of their data blocks in the buffer should be assigned to the KEEP pool.

- Use the KEEP pool if the object consumes more than 10% of the total size of the data buffer.
- Use the KEEP pool if more than 50% of the objects blocks already resides in the data buffer.

The following script can be run every hour via dbms_job and will automate the monitoring of KEEP pool candidates. Every time it finds a candidate, the DBA will execute the syntax and adjust the total KEEP pool size to accommodate the new object.

Spool buffer_keep_pool

drop table t1
create table t1 as
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
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;
Spool off