8 Replies Latest reply: Jan 22, 2013 8:06 AM by 951290 RSS

    difference between SGA_MAX_SIZE and SGA_TARGET

    DB
      Hi all,

      difference between SGA_MAX_SIZE and SGA_TARGET .i know about these parameters.
      1.when and where it works?
      (like startup or processing)

      Thanks in advance

      DB
        • 1. Re: difference between SGA_MAX_SIZE and SGA_TARGET
          Aman....
          DB wrote:
          Hi all,

          difference between SGA_MAX_SIZE and SGA_TARGET .i know about these parameters.
          1.when and where it works?
          (like startup or processing)

          Thanks in advance

          DB
          It would be better if you read about them from the documentation,
          http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory004.htm#ADMIN11203

          Aman....
          • 2. Re: difference between SGA_MAX_SIZE and SGA_TARGET
            Antonio Navarro
            Maybe You must to look for AMM (Automatic Memory Management) too.

            HTH
            Antonio NAVARRO
            • 3. Re: difference between SGA_MAX_SIZE and SGA_TARGET
              Antonio Navarro
              Maybe You must to look for AMM (Automatic Memory Management) too.

              HTH
              Antonio NAVARRO
              • 4. Re: difference between SGA_MAX_SIZE and SGA_TARGET
                Girish Sharma
                I just searched in the forum with "difference between SGA_MAX_SIZE and SGA_TARGET" words, and found plenty of good threads :

                SGA_MAX_SIZE is the size of shared memory that will be allocated at startup of the instance.

                If the operating system supports virtual shared memory, not all of the shared memory comes from physical memory, but the operating system believes it is all allocated.

                SGA_MAX_SIZE can be set at any time, but does not take effect until next instance restart.
                SGA_TARGET is the maximum shared memory that will be allocated by the Memory Manager, especially in management of the automatically adjusted pools.

                The maximum value for SGA_TARGET is SGA_MAX_SIZE.

                SGA_TARGET can be reset at any time, and takes effect immediately if SCOPE=CURRENT or BOTH. However, it may take some time for the target to be reached especially if the new target is lower and th system has used area 'above' the new target. That area needs to be flushed, and that flush can only occur after all transactions are finished using the area.

                [url https://forums.oracle.com/forums/thread.jspa?messageID=1988441&#1988441]Hans Forbrich

                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.
                [url https://forums.oracle.com/forums/thread.jspa?messageID=3134260&#3134260]HJR

                Another great discussion on the topic :
                SGA_MAX_SIZE != SGA_TARGET when?

                So, just explore these thread and make a good note from experts replies along with doc link which Aman have already posted.

                Regards
                Girish Sharma
                • 5. Re: difference between SGA_MAX_SIZE and SGA_TARGET
                  DB
                  hi Mr.Girish,

                  your explanation from previous thread was more helpful.
                  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.
                   
                  i don't know how to use transportable tablespace..
                  will you please give in detail.how it is used?

                  Thank you,
                  DB.
                  • 6. Re: difference between SGA_MAX_SIZE and SGA_TARGET
                    Aman....
                    DB wrote:
                    hi Mr.Girish,

                    your explanation from previous thread was more helpful.
                    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.
                    i don't know how to use transportable tablespace..
                    will you please give in detail.how it is used?
                    Let Oracle docs be your friend.
                    http://docs.oracle.com/cd/E11882_01/backup.112/e10642/glossary.htm#CHDHIGHH

                    Aman....
                    • 7. Re: difference between SGA_MAX_SIZE and SGA_TARGET
                      DB
                      Thanks for all
                      • 8. Re: difference between SGA_MAX_SIZE and SGA_TARGET
                        951290
                        Hi,

                        http://oracleinquisitive.blogspot.in/2013/01/automatic-shared-memory-management-in.html


                        Hope this will helps you..