This discussion is archived
5 Replies Latest reply: Feb 14, 2013 7:40 AM by ji li RSS

how to SGA_MAX_SIZE to 0 in 11gR2

user13427480 Newbie
Currently Being Moderated
I am using AMM and i have define

Memory_target=2g
memory_max_target=2g
sga_target=0
pga_aggregate_target=0

tried setting sga_max_size =0

but when i start the database with spfile it get set up 60% of MT by itself
  • 1. Re: how to SGA_MAX_SIZE to 0 in 11gR2
    sb92075 Guru
    Currently Being Moderated
    user13427480 wrote:
    I am using AMM and i have define

    Memory_target=2g
    memory_max_target=2g
    sga_target=0
    pga_aggregate_target=0

    tried setting sga_max_size =0

    but when i start the database with spfile it get set up 60% of MT by itself
    What is "it"?
    what is "MT"?

    post SQL & results so we can see what you do & how Oracle responds.



    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: how to SGA_MAX_SIZE to 0 in 11gR2
    user13427480 Newbie
    Currently Being Moderated
    mt: memory_target
    and by "it" I was referring to SGA_MAX_SIZE (SMS)
  • 3. Re: how to SGA_MAX_SIZE to 0 in 11gR2
    ji li Pro
    Currently Being Moderated
    I'm going to assume that you are referring to sga_max_size.

    This will show up the same as your memory_target, or memory_max_target, whichever is greatest, even though you set sga_max_size to zero (or unset it).

    If you want to confirm it, create pfile='<some file name>' from memory;
    Then, you can look in that file and see that you do not have an sga_max_size setting.

    Alternatively, you can set it to a value less than or equal to your memory_max_target to 'restrict' the size of your sga.
    This is done sometimes to ensure enough memory is available for other pools (e.g., pga, shared_pool, etc.).

    HTH
  • 4. Re: how to SGA_MAX_SIZE to 0 in 11gR2
    user13427480 Newbie
    Currently Being Moderated
    alter system set sga_max_size = 0 scope=spfile;

    alter system set memory_max_target = 2G scope=spfile;

    alter system set memory_target = 2G scope=spfile;

    this is what i did and bounced the instance

    after :

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    memory_max_target big integer 2G
    memory_target big integer 2G
    pga_aggregate_target big integer 0
    sga_target big integer 0
    sga_max_size big integer 1228M

    1. If my sga_max_size is 1228M, does it mean the max size of sga will be 1228M by AMM ?
    2. How to make sga_max_size=0?
    3. what if I make sga_max_size =2G will be the best utilization of memory ?
  • 5. Re: how to SGA_MAX_SIZE to 0 in 11gR2
    ji li Pro
    Currently Being Moderated
    Hmmm... Do you have any other settings for anything like PGA_AGGREGATE_TARGET, or other pools / caches?
    If you do, then this would be causing those pools to a minimum size and making the maximum available to the SGA_MAX_SIZE whatever is left over.
    For example, shared_pool_reserved_size, etc...
    Bottom line is that you can ignore the sga_max_size. It is just telling you this is all that can be allocated based on other settings already set.
    As I said, you can dump your memory settings to a pfile and see what settings are hard set.
    I think you will find that sga_max_size is not hard set, but rather just a dynamic value you see at the time you check it.

    Also, instead of setting sga_max_size = 0, I would recommend to 'reset' it instead.

    This is done by running:
    SQL> alter system reset sga_max_size scope=spfile sid='*';

Legend

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