9 Replies Latest reply: Nov 20, 2008 5:21 PM by jgarry RSS

    sga_max_size vs sga_target

    SID3
      Whats the difference between SGA_MAX_SIZE and SGA_TARGET? I am bit confused.

      As per Oracle® Database Reference 10g Release 1 (10.1)

      SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance

      Then why SGA_TARGET is used.

      I am missing something here. Kindly clarify.

      Thanks in Advance

      SID.
        • 1. Re: sga_max_size vs sga_target
          96843
          Here is the clarification :-

          SGA_MAX_SIZE is set so that you can dynamically change SGA_TARGET as per need, upto the SGA_MAX_SIZE. And SGA_TARGET is a dynamic parameter whereas SGA_MAX_SIZE is not.

          For more on this refer to - http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref376

          Hope this helps.

          Thanks
          Siba

          Edited by: pshiva on Nov 20, 2008 11:11 AM
          • 2. Re: sga_max_size vs sga_target
            Maran Viswarayar
            All you have to do is to read this thread

            SGA_MAX_SIZE != SGA_TARGET when?
            • 3. Re: sga_max_size vs sga_target
              581825
              Max_size is what the SGA can grow to if it has to.
              SGA_Target is what you'd like the SGA to be.

              Then why SGA_TARGET is used.

              Because you might want to dynamically grow your SGA, above the target. Suppose someone gives you a transportable tablespace and it comes from a 4K-blocksize database, and you're using 8K blocks. You have to 'alter system set db_4k_cache_size=something', but if you try that when your TARGET already equals your MAX_SIZE, you'll be told there's no room to grow the SGA, so the only way to achieve what you want is to shrink some other part of the SGA or to set the parameter in your init.ora and bounce. Either could be problematic on a production box. That's just one example: remember that 'SGA_TARGET' is indeed only a target: Oracle is allowed to exceed the target if thinks the workload makes it necessary to do so.

              So the difference between MAX_SIZE and TARGET is 'slack space': memory that's not in use, but could be if you (or Oracle) determined a need for it.

              On a lot of operating systems, this difference is paged out and thus doesn't mean you're wasting real, physical memory.

              But on others (and I think this is true of Windows and Linux), you would indeed be 'consuming' that memory difference -which would thus amount to wasted physical memory.
              • 4. Re: sga_max_size vs sga_target
                SID3
                Ok. Thanks for your clarification. Below are definitions from Oracle User Gudies

                SGA_MAX_SIZE : This is the maximum size of the SGA for an given instance.

                SGA_TARGET: This is the total size of SGA.

                Now if we consider your defintion does this means that SGA_TARGET should always be less than SGA_MAX_SIZE? What could be the adverse effect in the reverse scenario?

                Thanks in advance.
                • 5. Re: sga_max_size vs sga_target
                  Jeremy D Smith
                  SGA_MAX_SIZE is the largest amount of memory that will be available for the SGA in the instance and it will be allocated from memory. You do not have to use it all, but it will be potentially wasted if you set it too high and don't use it. It is not a dynamic parameter. Basically it gives you room for the Oracle instance to grow.

                  SGA_TARGET is actual memory in use by the current SGA. This parameter is dynamic and can be increased up to the value of SGA_MAX_SIZE.

                  Hope this helps
                  • 6. Re: sga_max_size vs sga_target
                    SID3
                    Thanks for the clarification.
                    • 7. Re: sga_max_size vs sga_target
                      251126
                      One thing to remember, your host system will allocate the amount of memory stated by sga_max_size, while your database instance will use what is in sga_target. So be careful setting sga_max_size more than a few hundred MB larger than your target as it is allocated space to the host whether used by Oracle or not.

                      Cheers,

                      Jay

                      http://www.grumpy-dba.com
                      • 8. Re: sga_max_size vs sga_target
                        247514
                        If you have time you can go over this thread, it should help you clear most doubts.

                        SGA_MAX_SIZE != SGA_TARGET when?

                        SGA_MAX_SIZE != SGA_TARGET when?
                        • 9. Re: sga_max_size vs sga_target
                          jgarry
                          SID3 wrote:
                          Ok. Thanks for your clarification. Below are definitions from Oracle User Gudies

                          SGA_MAX_SIZE : This is the maximum size of the SGA for an given instance.

                          SGA_TARGET: This is the total size of SGA.

                          Now if we consider your defintion does this means that SGA_TARGET should always be less than SGA_MAX_SIZE? What could be the adverse effect in the reverse scenario?

                          Thanks in advance.
                          Oracle won't let you set the reverse scenario.

                          $ oerr ora 823
                          00823, 00000, "Specified value of sga_target greater than sga_max_size"
                          // *Cause: The specified value of sga_target is greater than sga_max_size.
                          // *Action: Increase sga_max_size to match up with sga_target or decrease
                          // sga_target to match up with sga_maxsize.