1 2 Previous Next 22 Replies Latest reply: Dec 21, 2013 10:25 AM by Karan Kukreja RSS

Size of  SGA

GTS (DBA) Journeyer
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    Hi,

     

    Please read this article, good explain about memory advisors.

    ORACLE DATABASE: PGA , SGA , MEMORY Advisors

     

    regards

  • 7. Re: Size of  SGA
    EdStevens Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    .

  • 13. Re: Size of  SGA
    TSharma-Oracle Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points