10 Replies Latest reply on Feb 14, 2019 6:12 PM by John Thorton

    SGA Size

    2980262

      Hi All,

      My DBA increased SGA size in QA environment and is saying to test performance of programs so that he can implement same in production and I asked him on basis he is doing so but I couldn’t get a satisfactory answer.

       

      i need advise how we can determine what is Correct way to determine the SGA size and we are doing it correct.

        • 1. Re: SGA Size
          John Thorton

          2980262 wrote:

           

          Hi All,

          My DBA increased SGA size in QA environment and is saying to test performance of programs so that he can implement same in production and I asked him on basis he is doing so but I couldn’t get a satisfactory answer.

           

          i need advise how we can determine what is Correct way to determine the SGA size and we are doing it correct.

          Consider to Read The Fine Manual on V$SGA_TARGET_ADVICE

           

          https://docs.oracle.com/database/121/TGDBA/tune_sga.htm#TGDBA320

           

          If the total RAM stays the same & SGA is increased (gets more RAM), then what gets less  RAM & why is that acceptable?

           

          What  problem needs to be solved?

          How will larger SGA solve this problem?

          • 2. Re: SGA Size
            EdStevens

            2980262 wrote:

             

            Hi All,

            My DBA increased SGA size in QA environment and is saying to test performance of programs so that he can implement same in production and I asked him on basis he is doing so but I couldn’t get a satisfactory answer.

             

            i need advise how we can determine what is Correct way to determine the SGA size and we are doing it correct.

            So what answer did he give, and why to you not consider that to be "satisfactory"? 

            • 3. Re: SGA Size
              AndrewSayer

              2980262 wrote:

               

              Hi All,

              My DBA increased SGA size in QA environment and is saying to test performance of programs so that he can implement same in production and I asked him on basis he is doing so but I couldn’t get a satisfactory answer.

               

              i need advise how we can determine what is Correct way to determine the SGA size and we are doing it correct.

              The question is does your program perform better? By how much?

               

              It shouldnt matter to you how large the SGA is now or how your DBA went about increasing it, it is pretty much a meaningless metric to you, all that should matter is your performance in real terms.

              • 4. Re: SGA Size
                Sergey Krasnoslobodtsev

                Hi.

                Your database settings depend on its purpose.

                For each of the environments will have its own memory settings, the location of data files on the arrays, with the corresponding cost.

                For example, for a development environment, and functional testing do not require a powerful environment.

                On the contrary, the organization of user and load testing requires more powerful hardware configurations, even almost similar to the industrial environment.

                 

                 

                As for checking the configuration of the environment instance:development,testing, etc. - you need to take the configuration recommendations from Oracle as a basis.

                Corrections will be depending on the nuances of your tasks, the nature of the type of load database .

                For an example, reports can be used for monitoring: ASH/AWR or Statspack.

                • 5. Re: SGA Size
                  2980262

                  This is not 1% helpful.

                   

                  direct me to documentation or any blog related to this.

                  • 6. Re: SGA Size
                    John Thorton

                    2980262 wrote:

                     

                    This is not 1% helpful.

                     

                    direct me to documentation or any blog related to this.

                    This is not 1% helpful.

                     

                    asked & answered previously above.

                     

                    How big is big enough?

                    How small is too small?

                     

                    How will you know which response is  correct, since you obviously don't know the correct answer now?

                     

                    Please click on URL below

                     

                    LMGTFY

                    • 7. Re: SGA Size
                      Sergey Krasnoslobodtsev

                      2980262 написал(а):

                       

                      This is not 1% helpful.

                       

                      direct me to documentation or any blog related to this.

                      When General questions are asked, the answer will be a description of the approaches.

                      When you ask a question about a specific problem, with the metrics of the current settings and with the desired target metrics - the answer will be in the form of specific recommendations.
                      "It is impossible to make a diagnosis to pacient from a photo "))

                      • 8. Re: SGA Size
                        EdStevens

                        2980262 wrote:

                         

                        This is not 1% helpful.

                        Since you are here replying to yourself and include no context, we can't even tell what it is that you consider to be 'not 1% helpful'.

                         

                         

                        direct me to documentation or any blog related to this.

                        Not event the courtesy of a "please direct me ..."

                         

                        Ok, here's my suggestion:

                         

                        https://www.google.com/?gws_rd=ssl

                        • 9. Re: SGA Size
                          JuanM

                          2980262 wrote:

                           

                          i need advise how we can determine what is Correct way to determine the SGA size and we are doing it correct.

                          a) Run you programs/applications as always

                          b) Extract an AWR, ADDM & ASH Report (licensing required Diagnostic + Tuning Pack) or Statpack reports on period where the application were running

                          c) Analyze above reports

                          d) Make decitions about Database configuration there could be many findings not just about SGA.

                           

                          Regards,

                          JuanM

                          • 10. Re: SGA Size
                            John Thorton

                            2980262 wrote:

                             

                            Hi All,

                            My DBA increased SGA size in QA environment and is saying to test performance of programs so that he can implement same in production and I asked him on basis he is doing so but I couldn’t get a satisfactory answer.

                             

                            i need advise how we can determine what is Correct way to determine the SGA size and we are doing it correct.

                            Which component of the SGA was increased?

                            By how much was SGA increased?

                             

                            Please quantify how the increase in SGA impacted program performance.