my company using Oracle 11g 126.96.36.199.0 - Production database, my os is RHEL 5.5 ,my server's physical memory is 30.9 GB ( cat /proc/meminfo ) ie the SGA size [ (30.9*40/100)=12.5 GB ] so i use 12.5GB SGA size for one instance, we need to cache some data for our application performance how much data can cache in SGA based on 12.5 GB or how to calculate how much data can cache in SGA without any performance degradation.
12.5GB is not available completely for caching the data. Its just the buffer cache which can store it for you. I would ask you that what's the test that you have done which has ensured you that the caching would be a good idea for performance?
the keep pool, alter table cache, etc - I would not be doing ANY of these without a good reason.
Often times, people think that forcing a table to stay in the cache is a good idea. It rarely is. If you want things in the cache, use them. If they are used, they will naturally have their blocks cached. If you don't use them, they can get flushed out. But if you don't use them, why do you want them using memory that could better be used by other things?
My advice: don't try to second-guess Oracle's memory management and caching strategy. If you think you have a reason to, post it here and we can help you (or debunk your reason). Based on the fact that you've apparently calculated the SGA using that silly, meaningless "rule" that says give it 40% of your total RAM, I'd guess that you are looking around for a magic bullet, and you don't actually have a problem to solve.
I AM GOING TO SHOUT BECAUSE YOU ARE NOT LISTENING.
USE AUTOMATIC MEMORY MANAGEMENT. IF YOU DON'T KNOW WHAT THAT IS, READ THE DOCUMENTATION: http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm
ORACLE TAKES CARE OF CACHING THINGS AUTOMATICALLY. YOU DO NOT NEED TO DO ANYTHING.
IF YOU WANT TO CACHE THE ****RESULT**** (HARD TO EMPHASIZE WHEN I AM ALREADY SHOUTING), YOU CAN LOOK INTO THE RESULT SET CACHE WHICH YOU CAN ALSO READ ABOUT IN EITHER THE DOCUMENTATION OR THE MANY POSTS THAT GOOGLE WOULD FIND FOR YOU.
HOPE YOU CAN HEAR, I THINK THE LINE IS BAD OR SOMETHING