8 Replies Latest reply: Mar 29, 2011 5:36 AM by 412498 RSS

    SGA and PGA size

    Neo-b
      Hello All,

      I have a question.
      What is the best criteria to set the size of the SGA and PGA size in oracle 10g? and the memory_target size in oracle 11g ?
      Is it related to the size of data ? what is the criteria/factors in which it affect setting the size of the SGA, PGA (10g) and memory_target (11g)?

      Regards,
        • 1. Re: SGA and PGA size
          sb92075
          SELECT * FROM V_$SGA_TARGET_ADVICE;
          SELECT * FROM V_$PGA_TARGET_ADVICE;
          • 2. Re: SGA and PGA size
            12cdb
            What is the best criteria to set the size of the SGA and PGA size in oracle 10g?
            It will depend on your requirement and availability of memory on the server. But you can start with minimum size and can observe the system and increase accordingly.

            You can read asktom note..

            http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:30011178429375
            • 3. Re: SGA and PGA size
              Aman....
              Please read,http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/memory.htm#i53187

              Aman....
              • 4. Re: SGA and PGA size
                Lubiez Jean-Valentin
                Hello,

                What is the best criteria to set the size of the SGA and PGA size in oracle 10g? and the memory_target size in oracle 11g ?
                Is it related to the size of data ? what is the criteria/factors in which it affect setting the size of the SGA, PGA (10g) and memory_target (11g)?
                First of all you have to satistify the prerequisites given by the Installation Guide corresponding to the Oracle Release.

                Then, you have to take into account the memory available on the Server. If the SGA is too large the Server may swap and the Performances can become worst.

                Afterwards, it depends on the activity of the Database, for a DSS Database you may need a large SGA. If you have a lot of sessions, the Database may use a lot of PGA.

                In *10g* you have the ASMM (Automatic Shared Memory Management) and the Memory Advisor (see the Views V$SGA_TARGET_ADVICE / V$PGA_AGGREGATE_TARGET) may help you to set the Parameter SGA_TARGET and PGA_AGGREGATE_TARGET.

                In *11g* you have the AMM (Automatic Memory Management). The Memory Advisor with the View V$MEMORY_TARGET_ADVICE may help to set the Parameter MEMORY_TARGET.

                Of course, in *11g* you can also disable the AMM and use the ASMM instead. But its recommended to use the AMM and let Oracle tunes the memory components.

                Please, find below links about the Memory Management and Memory Advisor:

                http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/cncptdba.htm#CNCPT1244

                http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/montune.htm#ADMQS12321


                Hope this help.
                Best regards,
                Jean-Valentin
                • 5. Re: SGA and PGA size
                  Neo-b
                  Hello Lubiez,

                  One more clarification
                  How can identify the best value for memory_target from V$MEMORY_TARGET_ADVICE?
                  is it the last value in the MEMORY_SIZE column ? which has the minimum ESTD_DB_TIME?

                  Regards,
                  • 6. Re: SGA and PGA size
                    Lubiez Jean-Valentin
                    Hello,

                    How can identify the best value for memory_target from V$MEMORY_TARGET_ADVICE?
                    We can take the example below:
                    SQL> select * from v$memory_target_advice;
                    
                    MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
                    ----------- ------------------ ------------ ------------------- ----------
                            393                ,75        17909              1,0004          0
                            524                  1        17902                   1          0
                            655               1,25        17900               ,9999          0
                            786                1,5        17897               ,9997          0
                            917               1,75        17897               ,9997          0
                           1048                  2        17897               ,9997          0
                    You can see that beyond 786 Mo the ESTD_DB_TIME_FACTOR cannot be improved (it stays here stuck at 0,9997). So, I may set (in this example) the MEMORY_TARGET to 786 Mo, I won't have benefit to increase it more.


                    Hope this help.
                    Best regards,
                    Jean-Valentin
                    • 7. Re: SGA and PGA size
                      Helios-GunesEROL
                      Hi;

                      Please check below thread
                      How to picl to set SGA size
                      Re: Estimating SGA Size

                      Regard
                      Helios
                      • 8. Re: SGA and PGA size
                        412498
                        Auto change in 10g.