This discussion is archived
9 Replies Latest reply: Nov 20, 2008 3:21 PM by jgarry RSS

sga_max_size vs sga_target

SID3 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for the clarification.
  • 7. Re: sga_max_size vs sga_target
    251126 Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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