Sizing SGA
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Sizing SGA

  1. #1
    Join Date
    Aug 2001
    Posts
    64

    Talking

    Hi all, I hope you can help me. Senior DBA doesn't seem to think that the size of SGA is causing performance problem on a Solaris 2.6, But comming from Sys Admin Background to an Oracle position, I think the size of SGA is way to small, h ere's what the values are:
    Total System Global Area 104807824 bytes
    Fixed Size 64912 bytes
    Variable Size 47218688 bytes
    Database Buffers 57344000 bytes
    Redo Buffers 180224 bytes

    This is too small for a system with 2GB of Memory, 2 cpu, and over 769 Mb of swap(I didn't set the system up, personally I would make swap biger). I notice when the database starts up initially and running within a day or so, the memory usages is under 45%, whereas after several days, it maxs out the memory, we only have about 36Mbytes free. The only apps running on this box is oracle81 version one. How do I determined if the SGA needs to be resized and what is a good rule of thumb. How do I get my point across that SGA definitely needs to be look at. Thanks.

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    Since SGA is not big, what else is causing your system to tkae up your memory.
    Oracle does not know about the physical RAM, only the SGA.
    You need to find out which process is causing physical RAM to be used up.
    One thing which may cause is heavy sorting.

    Anyway SGA does seem to be to small.

    DBA has to see various waits, hit ratios and may be the end users might be getting
    ORA 4031 errors.



  3. #3
    Join Date
    May 2001
    Location
    Delhi
    Posts
    341

    SGA

    Hi,

    I think you hv to keep on checking your performance parameters. like. library cache, reads/call, hitratios etc.

    and depending on those you hv to resize your SGA.

    thatz the general rule which is being followed.

    lets c what gurus tell.
    --------------------------
    The Time has come ....

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Well itīs small or not that small depending itīs use
    Check PGA, sort area size etc

  5. #5
    Join Date
    Jan 2000
    Location
    Silver Spring MD USA
    Posts
    105
    I would set up STATSPACK and compare performance stats over a period of time. You mentioned you start to see some degradation after several days, so compare how it looks when it seems fine and when it starts to go down hill. This may help you get an idea of what areas are suffering most. (be sure TIMED_STATISTICS is set to TRUE)

    It also gives you some ground to stand on when you make your suggestions to the senior DBA.

    But definitely find out what other processes are running on the server. We normally turn off all the bells and whistles on our database servers so we can maximize the use of memory.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width