1 2 Previous Next 22 Replies Latest reply: Dec 21, 2013 12:31 PM by EdStevens RSS

    Size of  SGA

    GTS (DBA)

      Good morning all;

       

      Recently , i read some OTN THREAD disadvantages of SGA : if SGA is too large.

      Right now i cannot  search exact link. So anyone please clarify ?

       

      What official  document says

      For optimal  performance the entire SGA should be as  large as possible 

      to store  as much data  in memory to minimize disk  I/O

       

        1) If SGA IS  TOO LARGE  ,Any disadvantages ?

         2) If SGA IS TOO SMALL , Any disadvanges ?

         2) If i want to fix exact size of  SGA to my database , what is the solution ?

       

      Thanks in advance ..

       

      DB version : 10.2.0.4. on Linux

        • 1. Re: Size of  SGA
          sb92075

          8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

           

          Good morning all;

           

          Recently , i read some OTN THREAD disadvantages of SGA : if SGA is too large.

          Right now i cannot  search exact link. So anyone please clarify ?

           

          What official  document says

          For optimal  performance the entire SGA should be as  large as possible 

          to store  as much data  in memory to minimize disk  I/O

           

            1) If SGA IS  TOO LARGE  ,Any disadvantages ?

             2) If SGA IS TOO SMALL , Any disadvanges ?

             2) If i want to fix exact size of  SGA to my database , what is the solution ?

           

          Thanks in advance ..

           

          DB version : 10.2.0.4. on Linux

           

           

          SGA is not monolithic

           

          SQL> show sga

           

           

          Total System Global Area  456146944 bytes

          Fixed Size                  1344840 bytes

          Variable Size             348129976 bytes

          Database Buffers          100663296 bytes

          Redo Buffers                6008832 bytes

           

          which part are you attempting to size?

          • 3. Re: Size of  SGA
            Anar Godjaev

            HI,

             

            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 Database you may need a large SGA. If you have a lot of sessions,  the Database may use a lot of PGA.

             

            Tuning SGA |

            Calculating SGA |

             

            Thank you

            • 4. Re: Size of  SGA
              GTS (DBA)

              Hello sb;

               

              I'm n't trying to change any part of the SGA and i have one question from your answer.


              1) What is  exactly  FIXED SIZE AND VARIABLE SIZE in SGA ?

              2) what is the purpose of  FIXED SIZE AND VARIABLE SIZE ?

               

              SQL> show sga

               

              Total System Global Area  456146944 bytes

              Fixed Size                  1344840 bytes

              Variable Size             348129976 bytes

              Database Buffers          100663296 bytes

              Redo Buffers                6008832 bytes

               

              Thanks

              • 5. Re: Size of  SGA
                GTS (DBA)

                Thanks AnarGodjaev

                 

                I have asked  ..


                1) IS there any SGA advisor to set SGA size according to I/O ? 

                2) Can we change SGA size frequently according to I/O ?

                 

                Thnaks a lot.

                • 6. Re: Size of  SGA
                  user011232

                  Hi,

                   

                  Please read this article, good explain about memory advisors.

                  ORACLE DATABASE: PGA , SGA , MEMORY Advisors

                   

                  regards

                  • 7. Re: Size of  SGA
                    EdStevens

                    8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

                     

                    Hello sb;

                     

                    I'm n't trying to change any part of the SGA and i have one question from your answer.


                    1) What is  exactly  FIXED SIZE AND VARIABLE SIZE in SGA ?

                    2) what is the purpose of  FIXED SIZE AND VARIABLE SIZE ?

                     

                    SQL> show sga

                     

                    Total System Global Area  456146944 bytes

                    Fixed Size                  1344840 bytes

                    Variable Size             348129976 bytes

                    Database Buffers          100663296 bytes

                    Redo Buffers                6008832 bytes

                     

                    Thanks

                    Memory Architecture

                     

                     

                    ============================================================================

                    BTW, it would be really helpful if you would go to your profile and give yourself a recognizable name.  It doesn't have to be your real name, just something that looks like a real name.  Who says my name is really Ed Stevens?  But at least when people see that on a message they have a recognizable identity.  Unlike the system generated name of 'ed0f625b-6857-4956-9b66-da280b7cf3a2', which is no better than posting as "Anonymous".

                     

                    All you ed0f625b-6857-4956-9b66-da280b7cf3a2's look alike . . .

                    ============================================================================

                    • 8. Re: Size of  SGA
                      Eduardo Legatti

                      Hi,

                       

                      You have 3 views that can help you a lot.

                       

                      1. v$sga_target_advice
                      2. v$memory_target_advice
                      3. v$pga_target_advice

                       

                      Good Luck

                       

                      Cheers

                       

                      Legatti

                      • 9. Re: Size of  SGA
                        GTS (DBA)

                        I welcome your suggestion.   please see following links .. 

                         

                        Already i tried  to change my name << Really i could NOT change ..

                         

                        https://forums.oracle.com/thread/2574972

                         

                        https://forums.oracle.com/thread/2575340

                         

                        Even my  orginal  id is : 972355   --> still , i cannot access my id.

                         

                        https://forums.oracle.com/thread/2549936


                        I possible , please help me to change my user_name ..

                        • 10. Re: Size of  SGA
                          EdStevens

                          8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

                           

                          I welcome your suggestion.   please see following links .. 

                           

                          Already i tried  to change my name << Really i could NOT change ..

                           

                          https://forums.oracle.com/thread/2574972

                           

                          https://forums.oracle.com/thread/2575340

                           

                          Even my  orginal  id is : 972355   --> still , i cannot access my id.

                           

                          https://forums.oracle.com/thread/2549936


                          I possible , please help me to change my user_name ..

                          From the forum FAQ  (link in upper right corner) at https://wikis.oracle.com/display/Forums/Forums+FAQ

                           

                          How do I change my display name?

                          Next to your username/handle at the top right is a drop down menu. Click "edit profile", in there click edit next to the username. You'll need to log in again, after that you see all your personal fields. Roughly in the middle of that form is the OTN username field.

                          • 11. Re: Size of  SGA
                            jgarry

                            If you have an SGA so large that swapping happens, you have a grossly mistuned system.  You really shouldn't even be close to that.

                             

                            All systems have limits, and there is a trade-off between SGA and PGA.  There are circumstances where the same query could use the SGA, and later use PGA.  Simply throwing more memory at the SGA does not necessarily mean less i/o - and even where it does, it may slow everything down by totally swamping cpu.

                             

                            You use the advisors and some brain power to get SGA and PGA close to where they need to be.  The details vary by version, operating system, Oracle and OS configuration.  For example, for some configurations you may want to use an OS feature called hugepages, which doesn't work with some of the Oracle automatic memory options.

                             

                            Once you are close, most of the real system tuning comes by tuning the worst SQL.  Fixing even one wrong index can remove massive PGA usage, for example, or any of a million other things might be found.

                             

                            Thinking you can just fix the SGA beforehand and forget it misses the point completely.

                            • 12. Re: Size of  SGA
                              jgarry

                              .

                              • 13. Re: Size of  SGA
                                TSharma-Oracle

                                jgarry wrote:

                                 

                                Ya know, I'm starting to think people in India just copy random bad scripts they find on the internet and present them as their own. Oracel Apps DBA: Performance Monitoring Scripts

                                Thats a bit offensive to all Indians Joel. The guy you mentioned (AnarGodjaev) is not an Indian.

                                • 14. Re: Size of  SGA
                                  jgarry

                                  My apologies.  All the other older exact copies of that script were Indian.  They seemed to radiate as updates from a '90's script, possibly German.

                                   

                                  Edit:  And he was in India "...workshop from 2009 at our office in Mumbai, India."

                                  1 2 Previous Next