10 Replies Latest reply: May 20, 2008 9:39 AM by 429475 RSS

    Increase SGA Size

    615452
      Hi,

      Mine is 10.2.0.1 database running on HP-UX 11.23.....having 16 GB Memory..... I allocated shmmax as 5 GB...

      Physical Memory = 16 GB
      shmmax kernel parameter = 5 GB
      SGA_MAX_SIZE= 4 GB
      SGA_TARGET = 4 GB

      No other application is running on this machine..only database is running....I am thinking that we are simply wasting physical memory....

      Can I increase my shmmax kernel parameter to for suppose 12 GB and increase SGA size to 12 GB.......

      As per my understaning, if we increase SGA size, we will get more performance...
        • 1. Re: Increase SGA Size
          108476
          Hi Deepa,
          if we increase SGA size, we will get more performance...
          It's not that simple! Increasing db_cache_size only helps up to the point of diminishing marginal returns:

          http://www.dba-oracle.com/t_sga_sizing.htm

          Obviously, you want to use all of your RAM (if the DB is the only app on the server), but you must allow room for PGA RAM as well. Here is how I optimize an SGA size:

          http://www.dba-oracle.com/art_dbazine_ram.htm

          Hope this helps. . .

          Donald K. Burleson
          Oracle Press author
          Author of "Oracle Tuning: The Definitive Reference":
          http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
          • 2. Re: Increase SGA Size
            153119
            As per my understaning, if we increase SGA size, we will get more performance...

            Your 'understaning' is the typical 'understaning' of someone who heard a clock banging, but doesn't know where it is.

            This is known as 'More is better tuning'
            It is utter nonsense.
            You need to use the advisors available in Oracle to see whether increasing parameters will help at all.
            People asking such questions usually manage unscalable applications.
            Increasing the SGA when the application is unscalable, is not a fix, it is not even a cure, it is only a workaround.
            And if it does help, only temporarily, if it all.

            --
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: Increase SGA Size
              119642
              When we last upgraded our hardware we had loads of memory free and decided to use the multiple buffer pool option we now have a 16GB sga with very good performance. We set our critical reporting objects to have their blocks cached in the keep_pool and integration load objects in the recycle_pool with all other objects being default_pool with 6 database writer processes.

              We also had the luxury of being able to increase our pga_aggregate_target considerably.
              • 4. Re: Increase SGA Size
                561093
                As per my understaning, if we increase SGA size, we will get more performance...
                The more we eat the more healthy we get ?????????

                Our stomach gets upset and we end up paying a visit to the doctor
                • 5. Re: Increase SGA Size
                  615452
                  Mr. Sybrand,

                  Why you always serious and getting angry...since we are juniours to you, we know sga sizing is not that much easy..... thats why we are trying to get help from you guys....
                  • 6. Re: Increase SGA Size
                    119642
                    As per the other posts, you will have to test test & test again; we went through a rigorous test cycle after changing the parameters, nothing is simple. But there is no reason to waste memory; after all if you can utilise this resource all the better.

                    Create benchmark timings of critical processes before and rerun after changes. These benchmark timings will come in handy when you make any other changes to the system.

                    Kind regards Bill
                    • 7. Re: Increase SGA Size
                      153119
                      deepa_t,

                      I suffer from people who can't design a proper application and resorted to throwing memory at the problem for many many many years now.
                      The problem with juniors is they never seem to investigate an issue thoroughly and often resort the repairing a tire instead of replacing it.

                      The assertion you can tune a database by increasing the SGA is utter ....
                      And anyone reading current documentation and current performance tuning books can be aware of that.
                      The last version where this was the only way to resolve it this way was probably 6.0.36, which was retired in 1994.

                      Right now I have e-mails flying all around me caused by just another bunch of incompetent developers.

                      --
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 8. Re: Increase SGA Size
                        108476
                        People asking such questions usually manage unscalable applications.
                        That's just ridiculous.
                        • 9. Re: Increase SGA Size
                          108476
                          Hi Citrus,
                          The more we eat the more healthy we get ?????????
                          Even a fully cached database will run faster, but it gets marginally smaller:

                          http://www.dba-oracle.com/images/u00320021121brl01_B.gif

                          However, as to the other SGA components, I agree . . . .
                          • 10. Re: Increase SGA Size
                            429475
                            Right now I have e-mails flying all around me caused by just another bunch of incompetent developers.
                            He thinks he is born with Oracle in a silver spoon in his mouth. I don’t blame him; since there are some people who fit for the saying "You can't straighten a dog's tail even if you put it inside a flute for twelve years".

                            There is an old mathematic theory “A is a friend of B and B is friend of C => A is friend of C” and he thinks; it’s still true for "DBWR -> FAST_START_MTTR_TARGET -> LGWR => DBWR -> LGWR???"

                            You should have asked like “Can we increase the size of SGA_TARGET, PGA_AGGREGATE_TARGET, SGA_MAX_SIZE to utilize maximum memory” instead of “if we increase SGA size, we will get more performance...”, since you didn’t specified any performance related issue in your question. If you have 16GB RAM and Oracle is the only software running on it then, you should utilize 80% of it for maximize the performance and the only way is to increase the size of above parameters.