I will depend on oracle documentation to describe these parameter :
SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.
The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.
MEMORY_TARGET & MEMORY_MAX_TARGET
you can manage SGA and PGA together rather than managing them separately.
If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.
If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.
If MEMORY_TARGET is set to non zero value:
- SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
- SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.
- SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
- PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).
You can have some Description On http://support.oracle.com
Thank you
Osama Mustafa
Thank you Osama ;-).
LikeLike
Welcome
LikeLike
Excellent article, thanks for explanation, I have question on how to calculate value for MEMORY_TARGET, my S.O. is oracle linux with 64GB memory, only running is database.help me, pleaseregards[AZ]
LikeLike
There's Lot Of support.oracle.com Document mention this.Good Start From Here : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2054116400346220697and Herehttp://www.orafaq.com/wiki/Memory_target
LikeLike
Thank you Osama…Good Explanation on memory management..
LikeLike
you are welcome
LikeLike
Great stuff
LikeLike
Awesome article.
LikeLike
Thank you
LikeLike
What happens if Memory_target and SGA_target set to same value, specially where does PGA get memory from?
LikeLike
MEMORY_TARGET controls the total amount of memory to Oracle both for PGA and SGA You don't need to set SGA_TARGET if you already have MEMORY_TARGET set. Read Oracle documentation https://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm
LikeLike
WHAT HAPPEND SGA_TARGET=0 ? GIVE ME SIMPLE EXPLANATION?
LikeLike
Osama bhai, Very nice article 🙂
LikeLike
Osama bro great information. thanks alot
LikeLike
what happens if set both sga_target & memory_target to '0'?
LikeLike
Rames rams, MEMORY_TARGET – SGA_MAX_SIZE
LikeLike