This discussion is archived
7 Replies Latest reply: Aug 2, 2007 6:33 AM by Hans Forbrich RSS

sga_max_size & sga_target

user588120 Newbie
Currently Being Moderated
hi
what is the difference between sga_max_size & sga_target
  • 1. Re: sga_max_size & sga_target
    577207 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Hey

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

    Regards
  • 3. Re: sga_max_size & sga_target
    user588120 Newbie
    Currently Being Moderated
    hi adith,
    Is it possible to alter the value of sga_max_size
  • 4. Re: sga_max_size & sga_target
    user296828 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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.