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...

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

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.

Quote:

---

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.

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

Thank u 4 ur replies..

Quote:

---

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.

Quote:

---

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 :)

Show him this thread at AskTom, which also points to a thread at google.

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

I do separate indexes and tables, in all databases. Not for performance but for easier administration.