This content has been marked as final. Show 14 replies
MEMORY_MAX_TARGET specifies the maximum value to which a DBA can set the MEMORY_TARGET.
MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.
SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.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.
first seems you are using 11g Database , For this check the following scenario :
-If you set SGA_TARGET, SGA_MAX_SIZE then oracle will manage SGA and PGA .
-If MEMORY_TARGET set to non zero then Oracle will manage SGA and PGA
-SGA_TARGET and MEMORY_MAX_TRAGET are set to non-zero values, then SGA_TARGET will be considered minimum values.
I have set MEMORY_MAX_TARGET to something like 1G and SGA_MAX_SIZE to 500M. How would Oracle treat this? the 500M is treated as minimum SGA and SGA could grow till 1G-PGA??SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
What if i set MEMORY_MAX_TARGET= SGA_MAX_SIZE = 1G ??You do realize its default behaviour?
When SGA_MAX_SIZE is not set, its value will be same as MEMORY_MAX_TARGET.
Sure you could set it to the same too, but its unnecessary.
what if we set both? How Oracle behaves in that case?Oracle will not consider SGA_MAX_SIZE value, because MEMORY_MAX_TARGET is available from Oracle version 11.1.
The memory_max_target parameter is much like the sga_max_size parameter available in Oracle 10g.
A good note and graph on : http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/AMM1/Default.aspx
If you want enable ASMM (in 11g it is called AMM, new feature of 11g) you should set the parameter sga_target. SGA_MAX_SIZE doesn't enable ASMM. Aman @ sga_max_size & memory_max_target
If SGA_MAX_SIZE will be equal to MEMORY_MAX_TARGET in case I didn't set SGA_MAX_SIZE, what about PGA?
MMT=SGA+PGA (If MMT=MT)
Thanks for the nice links...
No where in the discussion forum and in the diagram I see something about SGA_MAX_SIZE. For me also it looks like ignoring SGA_MAX_SIZE in case of MMT makes sense. Could you point me to any official documentation where it's mentioned?
Could you point me to any official documentation where it's mentioned?When either MEMORY_TARGET or MEMORY_MAX_TARGET is enabled, SGA_MAX_SIZE default value is set to the larger of the two specified parameters on non-Windows 32-bit operating system. On Windows 32-bit operating system, due to address space pressure, the default value of SGA_MAX_SIZE is calculated based on the total available virtual address space along with MEMORY_TARGET and MEMORY_MAX_TARGET.
To calculate the value on Windows 32-bit, SGA_MAX_SIZE larger of:
60% of MEMORY_TARGET if set and
60% of MEMORY_MAX_TARGET if set and
25% of total available virtual address space
Edited by: Girish Sharma on Nov 15, 2012 9:20 PM
In addition to above please see this link :
Ctrl+F "I want to understand the relation between memory_target and sga_max_size." (Without double quotes)
And read reply by Sir Thomas Kyte.
If you want to go directly to an asktom post, copy the bookmark link next to each post. If there is no bookmark link, you can examine the page source, looking for a # followed by a number, appended to the page url. So you get [url http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516#1141788700346919480]this.
According to Tom's reply, If we set SGA_TARGET/PGA_AGGRIGATE_TARGET along with MMT/MT they'll be helpful as minimum limits. For e.g. SGA_TARGET=500M and MMT=MT=1G, then 500M SGA should be allocated at least.
What's the role of SGA_MAX_SIZE here? It'll be considered/ignored? If considered, how Oracle deal with this?
Many if and buts are here, but I am focusing only two i.e. SGA_MAX_SIZE and MEMORY_MAX_TARGET :
1.If SGA_MAX_SIZE is not set then it will default to MEMORY_MAX_TARGET.
2.If MEMORY_MAX_TARGET is lower then SGA_MAX_SIZE then ORA-00843. So either remove SGA_MAX_SIZE parameter or Increase MEMORY_MAX_TARGET parameter value.
3.If MEMORY_MAX_TARGET is not set, then you can not use Oracle 11g Automatic memory management.
So, here a good exercise for you or other readers to play around below parameters on 10g and 11g on Windows and Linux machines : (Suppose it is not shared server configured though.) i.e. show parameter SGA and show parameter TARGET;
and see, how Oracle works by looking into alert log and query on :
v$MEMORY_TARGET_ADVICE and many other v$SGA% and PGA related views. Then only, I think we may conclude and understand that what is relationship of MEMORY_MAX_TARGET and SGA_MAX_SIZE and/or many other parameter(s). Between this PDF may give you good start to play around above parameters :
CSM.DBA wrote:No problem; not an issue.
Sorry for the delayed response Girish.
Thanks for your reply.
Let me have a 'play session' on these and come back to you with my observations.That's pretty good and this is what known practicing and learning by our own. Please close this thread by marking it and if you wish to open new thread, you may mention this thread's link, if whenever you feel.