Re: SGA_MAX_SIZE vs. SGA_TARGET
Date: Sat, 05 Sep 2015 20:24:27 -0600
Message-ID: <55EBA3DB.9040902_at_gmail.com>
On 05/09/2015 11:50 AM, Dba DBA wrote:
> What is the point to having two parameters?
Many systems run two or more Oracle instances.
IF (and that is a big IF) the operating system supports de-allocation of shared memory (in other words, it fully supports ISM) then the following scenario is possible:
Machine has 64GB RAM and 3 Oracle instances.
Under ideal situation, each Oracle instance wants 36GB SGA for the peak load work. These workloads do not coincide.
The DBA may therefore set the SGA_MAX to 36GB for all 3 instances. Note that this 'normally' means the 64GB RAM would serve up 108GB in SHARED MEMORY alone, never mind PGA and regular requirements, which would on a 'regular' OS force the system into swapping as the least of ills.
However, since this is a compliant OS, the DBA would be able to set each instance to a regular running 8GB SGA_TARGET for normal operations. Only 24GB work be allocated at that time for the 3 instances.
When any of the instances approached people load period, the DBA could - without shutting down any instance since the internal structures are already in place to handle _MAX - dial the one that needs extra oomph up to 36GB for the duration (for a total SGA requirement of 52GB), and then bring it back to the nominal 8GB SGA when the peak is completed. No down time to adjust SGAs.
The above is the desired situation and the most probable reason for the two parameters.
Unfortunately most OSs do not support ISM and therefore will not play nicely with this scenario. In those cases, SGA_MAX and SGA_TARGET are basically the same thing.
However, at least one OS does support it. And the above scenario works fairly nicely.
None of this scenario begins to look at whether multiple instances on a box is a good idea, especially from a CPU or I/O point of view. It just helps with the reality that we often end up with multiples.
/Hans
The opinions are my own and do not necessarily represent those of Oracle
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Sep 06 2015 - 04:24:27 CEST