Home > Performance, Performance Analysis > Oracle KEEP and Recycle Cache

Oracle KEEP and Recycle Cache

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,

http://exploreoracle.com/2009/03/31/database-buffer-cache/
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.

buf_keep_pool.sql

-- *************************************************
-- 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:

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: