DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: SGA Size

  1. #1
    Join Date
    Sep 2001
    Posts
    37

    Question SGA Size

    Hi ppl:

    I have a simple question ppl..

    What would be the optimal size of the SGA for a database??..
    I know that bassically depends on:
    1. The estimated number of concurrents users expected
    2. The amount of phisically memory available

    Is there any other parameter that counts??..
    Or maybe there is a "magic" formula to establish the size of it??..

    Thanks in advance fellows...

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530

    Re: SGA Size

    Hi,
    The size of Sga normally depends on various factors:
    1)amount of RAM available.Normally the SGA size is kept to 60-70% of available RAM in your system.
    2)Also the SGA is tuned by taking the cachehit ratio percentage,Library Hit ratio,RowHit ratio.If any of these ratios fall or is in access of the percentage given by Oracle,then you increase or decrease the SGA size.
    3)Also Yes it depends on the transaction activity of the business.
    4)No of users accessing the database.
    But the major optimal Size of the SGA in the database is tuned according to 1 and 2.

    Regards,
    Rohit Nirkhe,Oracle/Apps DBA,OCP 8i
    oracle-support@indiatimes.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    have to disagree with point 2 -- "modern" oracle tuning advice is that the cache hit ratio is over-relyed on in the sizing of the SGA. You can create a high hit ratio simply by writing bad SQL that requires too many logical IO's.

    It could be that with well-written SQL your db performance will improve dramatically, while at the same time the cache-hit ratio might fall from 95% to 30%.

    However Rohit's first point (60-70% of available RAM) is a great place to start with sga sizing.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by slimdave
    have to disagree with point 2 -- "modern" oracle tuning advice is that the cache hit ratio is over-relyed on in the sizing of the SGA. You can create a high hit ratio simply by writing bad SQL that requires too many logical IO's.

    It could be that with well-written SQL your db performance will improve dramatically, while at the same time the cache-hit ratio might fall from 95% to 30%.

    However Rohit's first point (60-70% of available RAM) is a great place to start with sga sizing.
    It is very good of you to tell us all this and you got a point, however this is one of those tall stories. A buffer cache ratio of 30% is a bad thing. Even if your SQL runs well on that machine, it can probably run better if you increase the cache. In case you can afford it. But that depends also so much on the application. And yes, you are right that the cache hit ratio is over-relyed on in the sizing of the SGA.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Sep 2001
    Posts
    37

    Thumbs up

    I checked that ratios and all of them ar near 90%, so i think everything is fine here..

    Thank u 4 ur replies..

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by julian
    A buffer cache ratio of 30% is a bad thing. Even if your SQL runs well on that machine, it can probably run better if you increase the cache.
    Sure the ratio will increase if you increase the memory allocation, but the rate at which it increases my be very low. You could easily double the memory allocation for db_block_buffers without seeing much of an increase in the cache hit ratio. of course, you can use the cache advisor in 9i to tell you what effect you will get from resizing memory.

    Definately not a tall story though, when it is endorsed by Tom Kyte. There are other things to look at first in db tuning -- reduce your logical i/o's, use bind variables, make good use of your cpu's, for example. Oh, and if anyone tells you to separate indexes and tables into different tablespaces for performance reasons, make a mental note to pay no further attention to them. Another myth.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Feb 2003
    Posts
    2
    Originally posted by slimdave

    Definately not a tall story though, when it is endorsed by tom. There are other things to look at first in db tuning -- reduce your logical i/o's, use bind variables, make good use of your cpu's, for example. Oh, and if anyone tells you to separate indexes and tables into different tablespaces for performance reasons, make a mental note to pay no further attention to them. Another myth.
    As a newbie to the Oracle DBA world, I have had the exact OPPOSITE philosophy engrained into my head by our lead-DBA. Could you explain this myth of separating indexes & tables?

    Thanks in advance

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Show him this thread at AskTom, which also points to a thread at google.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Feb 2003
    Posts
    2
    Thanks for the reply. For whatever reason your URL wasn't working but a quick search gave me what you were posting. If anyone else has any trouble here is another link to it.

    Here is the thread topic from asktom.oracle.com:

    Dennis -- Thanks for the question regarding "Bad Policy or not", version 8.1.6.3 originally submitted on 19-Aug-2002 14:06 Eastern US time, last updated 23-Aug-2002 19:02

    & here is the amusing thread i'm going to pass on to my bossman

    http://asktom.oracle.com/~tkyte/google.html

    thanks

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I do separate indexes and tables, in all databases. Not for performance but for easier administration.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

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