14 Replies Latest reply: Nov 22, 2012 12:13 AM by Girish Sharma RSS

    What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?

    CSM.DBA
      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??

      What if i set MEMORY_MAX_TARGET= SGA_MAX_SIZE = 1G ??

      Thanks,
      CSM
        • 1. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
          Osama_Mustafa
          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.

          Read
          http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams133.htm

          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.

          Check
          https://maxwellmiranda.wordpress.com/2009/09/17/sga_max_size-sga_targe/
          • 2. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
            CSM.DBA
            I have an idea of what MEMORY_MAX_TARGET is and what SGA_MAX_SIZE is but I'm asking what if we set both? How Oracle behaves in that case?

            CSM
            • 3. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
              Osama_Mustafa
              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.
              • 4. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                Balazs Papp
                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.
                • 5. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                  vlethakula
                  oracle is permitting setting both SGA_MAX_SIZE and MEMORY_MAX_SIZE, not sure about consequences
                  • 6. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                    Girish Sharma
                    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

                    Regards
                    Girish Sharma
                    • 7. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                      CSM.DBA
                      Balazs,

                      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)


                      Girish,

                      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?

                      CSM
                      • 8. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                        Girish Sharma
                        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

                        http://docs.oracle.com/cd/B28359_01/readmes.111/b28280/toc.htm

                        Regards
                        Girish Sharma

                        Edited by: Girish Sharma on Nov 15, 2012 9:20 PM
                        In addition to above please see this link :
                        http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516
                        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.
                        • 9. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                          jgarry
                          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.
                          • 10. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                            CSM.DBA
                            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?

                            Regards,
                            CSM
                            • 11. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                              Girish Sharma
                              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.
                              4.memory_target=(sga_target+pga_aggregate_target)

                              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;

                              SGA_MAX_SIZE
                              SGA_TARGET
                              MEMORY_MAX_TARGET
                              MEMORY_TARGET
                              PGA_AGGREGATE_TARGET
                              LOCK_SGA

                              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 :
                              http://www.datasoftech.com/library/DSI_11g_AMM.pdf

                              Regards
                              Girish Sharma
                              • 12. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                                12cdb
                                if you are still reading,

                                http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm#ADMIN11011
                                • 13. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                                  CSM.DBA
                                  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.

                                  Regards,
                                  CSM
                                  • 14. Re: What if I set MEMORY_MAX_TARGET and SGA_MAX_SIZE both?
                                    Girish Sharma
                                    CSM.DBA wrote:
                                    Sorry for the delayed response Girish.
                                    No problem; not an issue.
                                    Thanks for your reply.
                                    Your welcome.
                                    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.

                                    Regards
                                    Girish Sharma