3 Replies Latest reply: Dec 30, 2012 2:49 PM by VenkatB RSS

    Database Memory Advise..

    mirzabaig28
      Hello everyone,. I am currently working on Oracle 11g R2 on Red Hat Linux 5 System. My current RAM on the server is 48 Gb.
      PGA has been assigned 10 Gb, SGA has been allocated with 30 Gb and rest 8 Gb is been used by Linux.
      Is there any suggestion for increasing or decreasing either PGA or SGA Memory.
      Thank You
        • 1. Re: Database Memory Advise..
          sb92075
          mirzabaig28 wrote:
          Hello everyone,. I am currently working on Oracle 11g R2 on Red Hat Linux 5 System. My current RAM on the server is 48 Gb.
          PGA has been assigned 10 Gb, SGA has been allocated with 30 Gb and rest 8 Gb is been used by Linux.
          Is there any suggestion for increasing or decreasing either PGA or SGA Memory.
          Thank You
          post SQL & results that would indicate any change would be appropriate.

          based upon what SQL & results were PGA = 10 & SGA = 30 determined?
          • 2. Re: Database Memory Advise..
            jgarry
            Post details of your memory configuration within Oracle (Non-standard init.ora parameters listed in the text alert log when you startup). Post details of your linux memory configuration.

            See http://kevinclosson.wordpress.com/2010/10/21/configuring-linux-hugepages-for-oracle-database-is-just-too-difficult-isn%E2%80%99t-it-part-%E2%80%93-ii/

            See the docs about memory advisors http://docs.oracle.com/cd/E11882_01/server.112/e10897/montune.htm#sthref694
            • 3. Re: Database Memory Advise..
              VenkatB
              >
              Hello everyone,. I am currently working on Oracle 11g R2 on Red Hat Linux 5 System. My current RAM on the server is 48 Gb.
              PGA has been assigned 10 Gb, SGA has been allocated with 30 Gb and rest 8 Gb is been used by Linux.
              Is there any suggestion for increasing or decreasing either PGA or SGA Memory.
              Thank You
              >

              Are you NOT using MEMORY_TARGET? If you are using memory_target, please be advised that it is allocation of both SGA and PGA.

              To answer your question, there's no ROT for this. If there's one, Oracle would not have given so many memory related parameters. Only you can say if your database is starved of memory or it's not using most of the memory you have allocated. That's why Oracle has given memory advice views which you can monitor (along with AWR reports if you have licence) to see if your database needs more memory or is not really needing all memory).

              From your description, I guess that you have SGA and PGA set (par with 10g and have ignored MEMORY_TARGET). Even in this situation, Oracle memory advicers are your best bet. You will have to check your AWR reports during peak DB hours to decide whether you have to increase or decrease memory or just do nothing.

              Regards
              Venkat