This discussion is archived
8 Replies Latest reply: Jan 22, 2013 6:06 AM by 951290 RSS

difference between SGA_MAX_SIZE and SGA_TARGET

DB Newbie
Currently Being Moderated
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.... Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for all
  • 8. Re: difference between SGA_MAX_SIZE and SGA_TARGET
    951290 Newbie
    Currently Being Moderated
    Hi,

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


    Hope this will helps you..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points