With previous releases the SGA was tunable with SGA_MAX_SIZE & SGA_TARGET.
The PGA was not (Really )
Now, as of Oracle 11g, SGA and PGA are taken together under the hood of AMM.
Setting MEMORY_TARGET tells the database how large the combination of SGA + PGA can be.
On the Internet there is lots and lots of information about this: http://www.dbas-oracle.com/2011/04/relation-between-memorytarget-sgatarget.html
SGA% parameters are enabled when AMM is disable.
AMM enabled = SGA and PGA sizes are automatically tuned by oracle. We just set Memory_MAX_TARGET and MEMORY_TARGET params and oracle give enough memory to the SGA and PGA whatever they need. for ex: afternoon SGA has no empty memory space but PGA has a lot of them, oracle takes spaces from PGA and give it to the SGA.
while AMM is enbled sga_target and sga_max_target are not considered (except lower bound limit, if you set sga_target like 10G, then oracle can not takes too much space and cause to SGA has less then 10G).
if you disable AMM, this means Memory_MAX_TARGET and MEMORY_TARGET are 0 any more and oracle will not transfer memory spaced between SGA and PGA! from now, you need SGA_TARGET and SGA_MAX_TARGET params to be set. so oracle will know how much space SGA will use and also components of SGA can be auto-tuned (buffer cache, large pool etc).
For 10g, the SGA size is shown by sga_target parameter and PGA size is shown by pga_aggregate_target parameter.
Prior to Oracle 11g, the DBA set the sga_target and sga_max_size parameters, allowing Oracle to reallocate RAM
within the SGA.
The PGA was independent, as governed by the pga_aggregate_target parameter.
For 11g, the parameter memory_target sets them both.
Now in Oracle 11g we see the memory_max_target parameter which governs the total maximum RAM for both the PGA
and SGA regions
and the new memory_target parameter which governs the existing sizes.
This allows RAM to be de-allocated from the SGA and transferred to the PGA.
Linux SGA size increasing automatically.
Automatic Memory Management(AMM) on 11g [ID 443746.1]
PS:Please dont forget to change thread status to answered if it possible when u belive your thread has been answered, it pretend to lose time of other forums user while they are searching open question which is not answered,thanks for understanding
Thank you Guys,
Anyway, neither of you answered my question. Maybe I did not made myself clear about my question.
If I set those parameter in my database:
# Enable AMM
# Disable ASMM
And restarted my db, why Oracle automatically set a value for SGA_MAX_SIZE?
fjfranken, you told that there are lots and lots of info about that. It is not correct. There are a lot of info about AMM / ASMM (many of them very confuse and imprecise, I would say). Go back to my question and just point me on Oracle Docs why SGA_MAX_SIZE has been set using around 60% of MEMORY_TARGET.
Some of you guys are saying that SGA_TARGET and SGA_MAX_SIZE are being used different on 11g. It is not true. Go back to the Oracle Docs and take a look in there.
Again, I am not asking for you guys to tell me what is AMM / ASMM. Forget about the concepts. I am not asking about the concepts. I am asking why Oracle suggested a default value for my SGA_MAX_SIZE parameter (hard one by the way) if I implicitly set SGA_MAX_SIZE=0 on my spfile?
please go through the link
because SGA must know how much it can grow also if you enable AMM you can nor disable ASMM. you say that you know ASMM, and as you know, ASMM means components in SGA can grow dynamiclly when necessary so if you enable AMM this means oracle can give memory from PGA to SGA (and vice versa) if you can disable ASMM and enable AMM this has no meaning. oracle will take memory space from PGA and will give it to SGA but to where ? you disable ASMM. so free space will be increase and it will be useless...
because of that, if you enable AMM, automatically enable ASMM too. if ASMM is enable this means at least SGA_MAX_SIZE must be set. because oracle set it back.
By setting MEMORY_TARGET you are asking Oracle to consider the optimal value for sga_target, sga_max_size or pga_aggregate_target. Now if you try to set some value for sga_max_size manually you are defeating the entire purpose of Automatic Memory Management. You may well disable MEMORY_TARGET and control them by setting values for these parameters separately.
Please look at a similar discussion here
Its a normal behavior. Please see,
*Automatic Memory Management (AMM) on 11g [ID 443746.1]*
Thank you. Appreciate your help here, but neither of you answered my question.
I am going to open a case with Oracle Support and will update this post with the right resolution.
It is very old post, but like to add answer to your question if that helps. Oracle document mentions following for sga_max_size
SGA_MAX_SIZEspecifies the maximum size of the SGA for the lifetime of the instance.
On 64-bit platforms and non-Windows 32-bit platforms, when either
MEMORY_MAX_TARGETis specified, the default value of
SGA_MAX_SIZEis set to the larger of the two parameters. This causes more address space to be reserved for expansion of the SGA.
On Windows 32-bit platforms, the default value of
SGA_MAX_SIZEis the largest of the following values:
- 60% of
MEMORY_TARGET, if specified
- 60% of
MEMORY_MAX_TARGET, if specified
- 25% of the total available virtual address space
Since you are running Linux (believe 32bit), it is defaulting to 60% of Memory_Target value.
On my HP-IA 64 bit system, I set MEMORY_TARGET=1G and MEMORY_MAX_TARGET=2G, but SGA_MAX_SIZE defaults to 2G which is larger of two parameters. This is correct as per oracle docs.
- 60% of