Data required by oracle user process is loaded into buffer cache, if it is not already present in cache. Proper memory tuning is required to avoid repeated disk access for the same data. This means that there should be enough space in buffer cache to hold required data for long time. If same data is required in very short intervals then such data should be permanently pinned into memory. Oracle allows us to use multiple buffers. Using multiple buffers we can control that how long objects should be kept in memory.
Note that in Oracle 10g, the terms are changed to the keep cache and the recycle cache. However, many people still refer to these two as pools. Some of the Oracle documentation still uses the term pool when referring to these caches.
Keep Buffer Pool
Data which is frequently accessed should be kept in Keep buffer pool. Keep buffer pool retains data in the memory. So that next request for same data can be entertained from memory. This avoids disk read and increases performance. Usually small objects should be kept in Keep buffer. DB_KEEP_CACHE_SIZE initialization parameter is used to create Keep buffer Pool. If DB_KEEP_CACHE_SIZE is not used then no Keep buffer is created. Use following syntax to create a Keep buffer pool of 40 MB.
DB_KEEP_CACHE_SIZE=40M
To pin an object in Keep buffer pool use DBMS_SHARED_POOL.KEEP method.
Recycle Buffer Pool
Blocks loaded in Recycle Buffer pool are immediate removed when they are not being used. It is useful for those objects which are accessed rarely. As there is no more need of these blocks so memory occupied by such blocks is made available for others data. For example if ASM is enabled then available memory can be assigned to other SGA components . Use following syntax to create a Recycle Buffer Pool
DB_RECYCLE_CACHE_SIZE=20M
Default Pool
If an object is not assigned a specific buffer pool then its blocks are loaded in default pool DB_CACHE_SIZE initialization parameter is used to create Default Pool. For more information on Default Pool visit following link,
BUFFER_POOL value in storage clause of schema objects lets you to assign an object to a specific Buffer pool. Value of BUFFER_POOL can be KEEP,RECYCLE or DEFAULT.
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999
set lines 92
spool keep_syn.lst
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;
Source / Reference Links: