http://www.adp-gmbh.ch/ora/concepts/cache.html
The cache consists actually of three buffer pools for different purposes.
Keep pool
The keep pool's purpose is to take small objects that should always be cached, for example Look Up Tables.
See db_keep_cache_size.
Recycle pool
The recycle pool is for larger objects.
Default pool
The default pool is for everything else.
See also x$kcbwbpd
http://www.adp-gmbh.ch/ora/admin/init_params/sga.html#db_keep_cache_size
DB_KEEP_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_RECYCLE_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
Manual SGA parameters
- db_keep_cache_size
- db_recycle_cache_size
- db_NNk_cache_size
NN being one of 2, 4, 8, 16, 32 - log_buffer
- streams_pool_size
http://www.remote-dba.net/oracle_10g_tuning/t_oracle_keep_pool.htm
Oracle KEEP Pool
A DBA can easily write a script that automatically identifies candidates for the KEEP pool and generates the syntax to move the tables into the pool.
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;
The following is sample of the output from this script.
alter TABLE BOM.BOM_DELETE_SUB_ENTITIES storage (buffer_pool keep);
alter TABLE BOM.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);
alter INDEX BOM.CST_ITEM_COSTS_U1 storage (buffer_pool keep);
alter TABLE APPLSYS.FND_CONCURRENT_PROGRAMS storage (buffer_pool keep);
alter TABLE APPLSYS.FND_CONCURRENT_REQUESTS storage (buffer_pool keep);
alter TABLE GL.GL_JE_BATCHES storage (buffer_pool keep);
alter INDEX GL.GL_JE_BATCHES_U2 storage (buffer_pool keep);
alter TABLE GL.GL_JE_HEADERS storage (buffer_pool keep);
alter TABLE INV.MTL_DEMAND_INTERFACE storage (buffer_pool keep);
alter INDEX INV.MTL_DEMAND_INTERFACE_N10 storage (buffer_pool keep);
alter TABLE INV.MTL_ITEM_CATEGORIES storage (buffer_pool keep);
alter TABLE INV.MTL_ONHAND_QUANTITIES storage (buffer_pool keep);
alter TABLE INV.MTL_SUPPLY_DEMAND_TEMP storage (buffer_pool keep);
alter TABLE PO.PO_REQUISITION_LINES_ALL storage (buffer_pool keep);
alter TABLE AR.RA_CUSTOMER_TRX_ALL storage (buffer_pool keep);
alter TABLE AR.RA_CUSTOMER_TRX_LINES_ALL storage (buffer_pool keep);
alter INDEX WIP.WIP_REQUIREMENT_OPERATIONS_N3 storage (buffer_pool keep);
Using the Oracle KEEP pool
According to Oracle documentation, “A good candidate for a segment to put into the KEEP pool is a segment that is smaller than 10% of the size of the DEFAULT buffer pool and has incurred at least 1% of the total I/Os in the system”. More concisely, a small table that is in high demand is a good candidate for KEEP caching.
Tuning the Keep and Recycle Pools
Note that ASSM does not automatically tune the keep and recycle buffer pools. You will still need to manually determine the size of the keep and recycle buffer pools with the db_keep_cache_size and db_recycle_cache_size parameters, respectively. And it should be noted that the if you have set the keep and recycle buffer pools, the memory for these pools is deducted from the overall sga_target that ASSM uses.
The best place to start tuning the buffer pools is with the v$db_cache_advice view (available in Oracle 9i and 10g). Before using this view, you must set the db_cache_advice initialization parameter to ON. If this parameter is already set to ON, and you set it to ON again, it will reset the information in the v$db_cache_advice view.
Oracle8 introduced the RECYCLE pool as a reusable data buffer for transient data blocks. Transient data blocks are blocks that are read as parts of large-table full-table scans and are not likely to be needed again soon. The goal is to use the RECYCLE pool for segregating large tables involved in frequent full-table scans.
No comments:
Post a Comment