6 Replies Latest reply: Nov 6, 2009 7:26 AM by 732596 RSS

    SGA_MAX_SIZE and shmmax values


      My environment is

      Oralce -
      Two node RAC environment
      Red hat Enterprise Linux 5 64 bit operating system
      RAM size - 64 GB
      shmmax - 4294967295 (one byte less than 4 GB)

      SGA_MAX_SIZE = 3808M
      SGA_TARGET = 3808M

      When I run the ADDM, it is telling that SGA was inadequately sized, increase the sga_target to 4760M. It says that 33% impact.
      In my case, I need to increase SGA_MAX_SIZE.
      I may be wrong, but from google, i assume that SGA_MAX_ZIZE can't be more than shmmax. Is it true?
      If I have to increase the shmax, is it fine to increase to 5 GB based on ADDM recommendataion. Are there any harm to increase the shmmax to 20GB are so for safer side?
      Any relation beween shmmax and RAM size?

      If I have to alter the SGA_MAX_SIZE in RAC, I need to bounce the database or i can do one instance at a time right. If I am going with one node at a time, since it is a production, what will happen to the connections in that node when i am restarting that particular instance? I have a TAF policy of "Basic".
      is it like all the select statements will be moved to survival node whereas insert, update and delete transactions will be broken?
      Awaiting your help..
        • 1. Re: SGA_MAX_SIZE and shmmax values
          Satishbabu Gunukula
          Always it is recommended to keep "shmmax" half the size of physical memory.

          First you need to increase shmmax and then increase the SGA_MAX_SIZE as per ADDM.

          See the below doc for all your answers answers.

          Hope this solves your issue.

          Satishbabu Gunukula
          Click here for solution - [ORA-19755 Error during recovery/DB startup|http://www.oracleracexpert.com/2009/09/ora-19755-could-not-open-change.html]
          Click here to learn [Block corruption and recovery|http://www.oracleracexpert.com/2009/08/block-corruption-and-recovery.html]
          Click here to learn [Transportable tablespace export and import |http://www.oracleracexpert.com/2009/09/transportable-tablespace-export-and.html]

          Edited by: Satishbabu Gunukula on Sep 23, 2009 2:43 PM
          • 2. Re: SGA_MAX_SIZE and shmmax values
            It is not necessary to increase shmmax to increase SGA_MAX_SIZE.

            shmmax is the maximum size of any one shared memory segment. So, if you set SGA_MAX_SIZE that's larger than shmmax, this just means Oracle will use two (or more) shared memory segments to make up the SGA.

            • 3. Re: SGA_MAX_SIZE and shmmax values
              so without increasing the shmmax, if I increase the SGA_MAX_SIZE, will there be any problem ?
              • 4. Re: SGA_MAX_SIZE and shmmax values
                user1368801 wrote:
                so without increasing the shmmax, if I increase the SGA_MAX_SIZE, will there be any problem ?
                Not generally, but there have been reports of certain platform configurations not handling multiple segments correctly. Kind of rare and obscure, go ahead and try it, either it will work or blow up. If it blows up, go back to old setting and ask here again, with error message.

                Be aware that sometimes the advisors just keep asking for more and more, they're useful, but can be fooled (and sometimes misleading with those % improvement predictions). Making everything worse can happen, but is somewhat unusual if not accompanied by gross obvious problems like swapping.
                • 5. Re: SGA_MAX_SIZE and shmmax values

                  I have raised the SR with Oracle whether SGA_MAX_SIZE size can be more than shmmax.
                  Oracle support strongly replied that SGA size should be lesser than shmmax, to increase SGA, first we need to increase shmmax then only we need to increase SGA.

                  Thanks to all for your suggestions.
                  • 6. Re: SGA_MAX_SIZE and shmmax values
                    Can you share the whole SR info either in this thread or email me to sd.purna@gmail.com