7 Replies Latest reply: Aug 2, 2007 8:33 AM by Hans Forbrich RSS

    sga_max_size & sga_target

    user588120
      hi
      what is the difference between sga_max_size & sga_target
        • 1. Re: sga_max_size & sga_target
          577207
          Oracle Database start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter.

          If the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file (SPFILE) is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE.

          The SGA_TARGET initialization parameter reflects the total size of the SGA and includes memory for the following components:

          Fixed SGA and other internal allocations needed by the Oracle Database instance

          The log buffer

          The shared pool

          The Java pool

          The buffer cache

          The keep and recycle buffer caches (if specified)

          Nonstandard block size buffer caches (if specified)

          The Streams pool

          It is significant that SGA_TARGET includes the entire memory for the SGA, in contrast to earlier releases in which memory for the internal and fixed SGA was added to the sum of the configured SGA memory parameters. Thus, SGA_TARGET gives you precise control over the size of the shared memory region allocated by the database

          Please visit here
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm#sthref1244

          Additionally you can visit here:
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref390
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams192.htm#REFRN10256
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams191.htm#REFRN10198

          Adith
          • 2. Re: sga_max_size & sga_target
            Oviwan
            Hey

            read this thread about this subject:
            SGA_MAX_SIZE != SGA_TARGET when?

            Regards
            • 3. Re: sga_max_size & sga_target
              user588120
              hi adith,
              Is it possible to alter the value of sga_max_size
              • 4. Re: sga_max_size & sga_target
                12cdb
                it is possible and if you are using spfile then
                ALTER SYSTEM SET sga_max_size=<value> SCOPE=SPFILE|MEMORY|BOTH;
                • 5. Re: sga_max_size & sga_target
                  user588120
                  hi pgeol
                  What about sga_target?
                  can i give the command like this?
                  alter system set sg_target=<value > .
                  • 6. Re: sga_max_size & sga_target
                    577207
                    SGA_TARGET is Modifiable with ALTER SYSTEM.

                    http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams192.htm#REFRN10256
                    SQL> alter system set sga_target=100M;

                    System altered.
                    Adith
                    • 7. Re: sga_max_size & sga_target
                      Hans Forbrich
                      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.