There is no thumb rule and the setting differs one database to another as it depends on application usage and demand.
Usually, you can keep small (static) tables (lookup tables) where frequently has FTS.

Jaffar

Well, Jaffar is correct, that there is no hard and fast rule. As with everything in Oracle, it depends. However, here's a script, and some thoughts, after using this feature for quite some time...

Here's the query I call "high_io_default_pool_segments.sql", that I use:
set lines 150
select * from(
with segstat as
(
select object_type,
object_name,
owner,
subobject_name,
max(case statistic_name when 'physical reads' then value end) pr,
max(case statistic_name when 'physical reads direct' then value end) prd
from v$segment_statistics vss
where statistic_name in ('physical reads', 'physical reads direct')
group by object_type, object_name, owner, subobject_name
)
select st.owner,
st.object_name,
st.subobject_name,
(st.pr-st.prd) cached_physical_reads,
ds.bytes/power(1024,2) mb
from dba_segments ds, segstat st
where ds.owner=st.owner
and ds.segment_name=st.object_name
and ds.segment_type=st.object_type
and nvl(ds.partition_name, ds.segment_name)=nvl(st.subobject_name, st.object_name)
and ds.buffer_pool='DEFAULT'
order by st.pr-st.prd desc
)
where rownum < 21;

This query will identify the top 20 segments, currently in the default buffer pool, that have the highest physical reads. Now, it will also print the size of the object.

So, first, determine how much of your system's memory you can afford to allocate to the recycle and keep pools. Size the two new pools accordingly. Second, look at the output of the above script, and, for each segment, look at the size. Is is small enough to be cached entirely? If so, assign it to the keep pool. If it's way too large to cache entirely, assign it to the recycle pool.

You'll certainly overallocate the recycle pool, in terms of total object size vs. size of recycle pool. That's sort of the point for the recycle pool, damage limitation.

In the case of the keep pool, depending on the sizes of the objects, try to keep within the bounds of the keep pool size. You may overallocate slightly, but try not to overallocate grossly, like you're bound to do with the recycle pool. The idea here is to isolate small, heavily used objects that can be cached entirely, and eliminate any further reads on them.

As I said, no hard and fast rules, cause every system is different, but, with the above script, the ideas here, and some idea of how your application works, you should be able to make a lot of progress.

-Mark

Thanks Mark for your script and explaination.
I will run this query and let you know.

Thanks once again!

I am still confused to make proper determination to create KEEP or RECYCLE buffer pool? Could someone guide me? Whatever I have mentioned query in my first question and Mark has suggested - query, which one is the best measurement?
I just need to confirm which parameter needs to be considered after running those queries?

Thanks and appreciated your response!

Thank you very much Jonathan.
I really appreciate your detail explanation.
So you are telling me that i should run only with my DEFAULT pool as it is and donot create any KEEP POOL or RECYCLE pool?
Did you see the queries i am running in my first email? Then which field/value i should really consider?
I am getting hit ratio for DEFAULT is negative in production (like -27.43, -102.63, -88.63, 8.07, -11.36, -38.4, -265.04, -147) but in staging I have around 91, 93.
running following query:
SELECT NAME, 100 - round ((PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))*100,2) HitRatio
FROM V$BUFFER_POOL_STATISTICS;
Thanks,

Hi Mark,
Once again Thanks for your kind response. Now My question is when I ran this query, I got following results. How can I know that which objects will be candidates for KEEP and Recycle? I am puzzling myself for KEEP and Recycle candidates?

OWNER OBJECT_NAME CACHED_PHYSICAL_READS MB
ora C_U_ATTR 2648793092 58.875
ora C_SURV_RESP 2569957476 216
ora HRT_APPL 1255885098 320.375
ora HR_VW_NOTE 1217831147 86
ora B2B_ITM_LST 251173927 44.625
ora EMP_INFO 140302432 22.125
ora C_RCV_INFO 127806535 735.125
ora DPS_FOLD 110500120 19.5
ora HRT_POST 105302622 10.5
ora SRV_RESP 100484931 36
ora C_U_GRP_ATTR 91382000 47.25
ora DC_MED_TXT 91158559 19
ora CUST_SALESEMP 84850852 66.75
ora D_ROL 84500634 141.875
ora D_USR 72096368 64
ora VR_PO_ITM 47788125 9094
ora CL_PRD_REG 47732518 34.875
ora PG_ATTR 41482518 38.875
ora CUST_DIV 28137692 25

Thanks,

pdshah

The first three queries are joining on the wrong column, and omit analysis of subpartitions. One of them tests for status = 'free' , which is a predicate to use against v$bh - the relevant column in x$bh is called state and is a numeric.

None of the queries considers the possibility of multiple CR copies of a single block in the buffer cache, which is probably an important consideration for trying to set the best sizes for both the KEEP and RECYCLE (see my reply to Mark Bobak for the dependency).

I don't think I've ever bothered to write a query to calculate a cache hit ratio - but the fact that you've managed to get negative values from a piece of code that is probably supposed to return values between 0 and 100 shows that the code is wrong: or at least inappropriate for your version of Oracle. (I note, for example, that it doesn't consider direct path reads - and I am aware that v$buffer_pool_statistics was far from consistent with v$sysstat in some versions of Oracle anyway).

Forget the silly scripts to check buffer usage and hit ratios. The accumulation in v$segstat (as shown by Mark Bobak) is far more important. The only interesting test is: how much I/O is a specific segment subject to, and how much would that (probably) change if you used a different cache for that object.

Even then you can be fooled because a read that is reported as a physical read may be coming out of a filesystem or disk cache somewhere and operate at a speed that makes it pointless to sacrifice cache that could be used to greater benefit by another object.

The difficulty is that setting aside memory for specific caches reduces the size of the default cache - which changes the caching strategy for all other objects; and the side-effects may outweigh the direct benefits. Hence my bias in favour of RECYCLE pools which tend to be "small" and against KEEP pools which tend to be "large" (except for the ones used to address the tablescan bug).

The measure of I/O from v$segstat is a guideline to help you find objects that are worth considering - but ultimately you need to understand the application to be confident that it's worth making the change.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk