5 Replies Latest reply: Feb 14, 2013 9:40 AM by ji li RSS

    how to SGA_MAX_SIZE to 0 in 11gR2

    user13427480
      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
          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
            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
              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
                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
                  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='*';