Question: How do you estimate the
appropriate value for the sga_target parameter?
Answer: When using Automatic
Memory Management (AMM), the Oracle 10g sga_target
parameter is obsolete, replaced by the Oracle 11g
memory_target parameter.
Oracle guru Mark D Powell shared this script to estimate
the sga_target value, based on the existing pool
usages in v$sga:
col EST_SGA_TARGET
format 999,999,999,999
select
case when current_size = 0 then 'RESULT
= V$SGA'
else
'ESTIMATE '
end as ESTvsSGA
,((select
sum(value)
from
v$sga
) -
current_size
) as EST_SGA_TARGET
from
v$sga_dynamic_free_memory
;
Using AMM via the sga_target and
sga_max_size parameter changes how the traditional pool
parameters work.
When using AMM, the values for the "traditional" pool
parameters (db_cache_size, shared_pool_size, &c)
are not ignored. Rather, they will specify the minimum size
that Oracle will always maintain for each sub-area in the
SGA.
