Hi Tom,
Can you give us an idea as to how to estimate size of SGA? An experienced dba
told me that the best way to estimate size for SGA is to set aside 40% of memory
for sga i.e for 2gb memory 800mb is sufficient. Is this correct?
If yes, then if i add another 1gb ram how can i justify this to customer saying
ONLY 40% will be used by oracle.
Thanks a lot for all your help
－－－－－－－－－－－－
so, that is the way to estimate the SGA size. Interesting.
I always thought it is a function of what you need to do, not how much ram just
happened to be installed.
But basically you need to understand how Oracle uses memory (my book Expert one
on one Oracle goes into that). There is the SGA and PGA memory.
Your DBA's rule of thumb (ROT) here is "you want to use 40/50% of RAM for the
SGA leaving the other 50% for the dedicated servers (processes -- they allocate
PGA) and 10% or so for the OS and related processes"
That is, their ROT is how to maximize the use of memory for a typical dedicated
server configuration.
That ROT is totally wrong if you are using shared server (there the SGA might be
80-90% of RAM)
That ROT is totally wrong if you are not the only game in town (not the only
thing on the server).
That ROT does not mean you are using only 40% of the additional gig of ram you
are putting in, it means "40% of it will go towards the SGA, the remaining will
be available for dedicated servers to do sorts and hashes and stuff"
but the major considerations here are:
a) how much do you want to assign to your buffer cache for maximum performance
b) how big is your shared/java pool (a function of how much sql/plsql/java you
run in your database, no magical number for all to use)
c) do you run in shared server (than the large pool is used and will be large --
that is part of the sga) or in dedicated server -- then you need to leave OS
memory for dynamic allocations
d) what else is going on in the machine.
--------