| |
 |
|
sga_max_size (Oracle 10g ONLY)
Oracle Tips by Burleson Consulting
May 7, 2008
|
Wasting expensive RAM resources is a bad thing and the goal
of the Oracle DBA is to maximize the SGA region with intelligent SGA sizing
techniques in relation to sga_max_size.
Beware of AMM:
Note that larger shops many forgo
AMM and manually size their shared_pool_size and
db_cache_size.
A human DBA can frequently do a better job in sizing the RAM pools,
especially when using
predictive tuning techniques, which anticipate changes in workload and
adjust the pools before a shortage occurs.
If you only have Oracle on the server, start by reserving
10% of RAM for Linux or 20% or RAM for Windows. With whatever RAM is
left-over you allocate to SGA and PGA:
-
sga_max_size
-- This 10g parameter sets the hard limit up to which sga_target can dynamically
adjust sizes. At database start time, Oracle will allocate sga_max_size
in RAM (or set sga_max_size to the sum of the existing pool sizes), so in order
not to waste RAM it may be a good idea to have sga_max_size and sga_target
at the
same value, but there may be times when you want to have the capability to
adjust for peak loads. By setting this parameter higher than sga_target,
you allow dynamic adjustment of the sga_target parameter. For
related notes, see MOSC Notes 295626.1, 396940.1, 270065.1 and 256913.1.
-
sga_target -- This parameter is for Oracle
Database 10g ONLY and reflects the total size of memory footprint a SGA can consume.
It includes in its boundaries the fixed SGA and other internal allocations, the
(redo) log buffers, the shared pool, Java pool, streams pool, buffer cache,
keep/recycle caches, and if they are specified, the non-standard block size
caches.
AIX Note for sga_target:
There are reported cases where sga_target cannot
exceed two gigabytes. This is because of the need to edit edit the
/etc/security/limits file and look for Soft DATA segment, it should
have the value of -1 (Unlimited).
What should be my sga_max_size?
The default that oracle suggests at the DBCA is
40%, however,
the appropriate size for this parameter depends
on the space requirements of your actual SGA. Your application requirements will
determine this, things like the amount of concurrent users and the activities
they are really performing. These types of things can only be monitored when
running processes. There is no formula
for an exact value of these parameters.
The best thing you can do is to tune based the advisors suggestions.
Memory consumption should be checked when your real workload is in progress, and
sga_max_size tuning should be performed using
Enterprise
Manager to tune based on realistic metrics.
You can see the Oracle memory segments with the ipcs
-pmb command.
|
|
|
