10 Replies Latest reply on Oct 2, 2013 1:51 AM by vsrao181835

    MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS

    CESARDBAIBM
      Hi all,

      I was just wondering how is the relationship between MEMORY_TARGET and SGA_MAX_SIZE parameters, if exists any at all.

      I set my memory parameters as:
      # Enable AMM
      MEMORY_MAX_SIZE=10G
      MEMORY_TARGET=4G
      # Disable ASMM
      SGA_TARGET=0
      SGA_MAX_SIZE=0

      So I bounce my instances and when I checked the parameter SGA_MAX_SIZE, Oracle automatically set a value of 2456M to it:
      sga_max_size big integer 2456M
      sga_target big integer 0

      Reading MOS ID 443746.1, there is no clear mention about this relationship. It seems that Oracle automatically set SGA_MAX_SIZE as 60% of MEMORY_TARGET.

      In am running Oracle 11gR2 (11.2.0.2.3) under Red Hat Linux. Appreciate any input on that.

      Thank you in advance,
      Cesar
        • 1. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
          fjfranken
          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
          • 2. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
            802709
            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).
            • 3. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
              Helios-GunesEROL
              Hi;

              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.

              Refer:
              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

              Regard
              Helios
              • 4. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
                CESARDBAIBM
                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
                MEMORY_MAX_SIZE=10G
                MEMORY_TARGET=4G
                # Disable ASMM
                SGA_TARGET=0
                SGA_MAX_SIZE=0

                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?

                Best regards,
                Cesar
                • 5. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
                  CloudDB
                  please go through the link
                  http://www.dba-oracle.com/oracle11g/oracle_11g_memory_target_parameter.htm
                  • 6. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
                    802709
                    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.
                    • 7. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
                      RajivIyer
                      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
                      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516
                      • 8. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
                        Aman....
                        Its a normal behavior. Please see,
                        *Automatic Memory Management (AMM) on 11g [ID 443746.1]*

                        Aman....
                        • 9. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
                          CESARDBAIBM
                          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.

                          Best regards.
                          • 10. Re: MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS
                            vsrao181835

                            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_SIZE

                            SGA_MAX_SIZE specifies 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_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is 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_SIZE is 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.

                             

                            Thanks

                            Rao