What percentage of RAM should Oracle SGA be set?

[Volker Borowski]

Well,
first question would be : Do you have dynamic SGA configured ?
If yes you are more flexible, because you can resize your buffers while the db stays up and online.

set
sga_max_size=8000000000 (8GB)
db_cache_size=3000000000 (3GB)
pga_aggregate_target=1024M (1GB)
shared_pool_size=500M

and distrubute the rest sparefully (java_pool_size if needed, other pools if configured and in use [i.E. if used diffrent db_blocksizes in one db or keep_pool])

This will give you the option to increase all buffers if needed according to the statspack-advice as already sugessted an utilize up to 8GB only for memory purposes.

I'd recommend to keep some spare RAM for OS purposes and binaries being executed.

You can only get more detailed while monitoring the system.

Volker

[TwoProc]

Well, I like my hit ratio closer to 98-99% than 95%. But, how much ram is a bit harder to figure.

The main question that you need answered is "how much can I increase it without going into swap". Then, you can determine how to correctly use what is available in this yet-to-be-determined "safe zone."

Right now, how much free ram do you have used at the peak period. About 80% of that free area is what I'd say you start off thinking about as your available memory to use for trying to increase your performance. Of course, don't just start off by increasing a component of the SGA by that much. Start smaller. This "free area" represents your 10G less OS overhead, possible file system buffereing, the SGA, and all of the little PGA's of every connected user (at peak) and all of the other programs doing whatever they do which takes memory. So, figure out what 80% is, and write that down and, for starters don't add into any pools, buffers, etc, any more than this amount in total.

Secondly, we need to figure what to increase. You said that you've got 95% hit ratio on the cache. Someone said that you could probably double it. I've seen everything from a dramatic increase in performance with a change like this, to nothing noticeable. I think you'll notice a measurable difference in performance (providing that you're measuring with some good tools). A "noticeable" difference may not show, except possibly at peak periods.

Re: your bosses' point: Recognize that the 5% "miss" that you currently have is equal to almost ALL of your Oracle I/O (not including redo-logs and archive logs), and that if you can increase your hit ratio from 95% to 96% you have reduced total I/O by 20% (1/5)!!!!

But, don't just increase the buffer_cache, you probably need to see if you need to increase the size of the shared pool. Have you assessed your code to see if it is getting reloaded back into the shared pool often? If so, you really need to increase space in the shared pool ( as well as the large_pool if using it, ditto the java_pool).
If this isn't tuned right, you'll pick up performance even when your hit ratio on your buffer_cache (data area) is high. So, pay attention to this also.

Also, pay attention to sort_area_size. Are lots of sorts happening on disk? If so, you'll need to tweak this and keep in mind that if you increase this memory, you increase memory for EVERY CONCURRENT (active or not) CONNECTION. So, be VERY CAREFUL with that one.

My thinking is, that if you've got 4 or 5 Gig free in memory that you can use, you'll get some better performance out of your database, if you can use it where it is needed most.

[ericfjchen]

Thanks for all of your comments. They are very useful for me. Our environment is Oracle 9.0.1.4 on Tru64 5.1B. Do you think we should change SGA to dynamic mode? Is it better for me?

[Volker Borowski]

Hmm,

9.0.1 is quite low patchlevel.
There have been some issues in early releases with dynamic sga and automatic pga.
Check if you can patch up-to-date (9.2.0.7) from application-software point of view and switch to dynamic then !

Volker

[ericfjchen]

Due to the application we used, we can't upgrade Oracle to 9.2.0.x. Could you tell me
Which issues are in 9.0.1.4 with dynamic sga and automatic pga? Does any Oracle document mention that?

Thanks so much

Eric

[Frank de Vries]

There are two ways about this, one being very scientific about it, and second using a rough rule of thumb.
From my experience the two in reality show similar results, and the rule of thumb is easier to use like this:

Take 10% for the OS
The remaining part divide by 2.
(So basically 45%) and that I use as a MAX boundary for SGA. (You don't have to use it all at once, it is better to start lower so you can increase later on if needed)
Also if you have 2 or 3 instances, you will need to divide this RAM among the number of instances, as long as MAX is respected.

The other 45% is for PGA and other processes.
Check with ipcs -m after to see your memory
structure is as intended :)

This has always done it for me.

[Hein van den Heuvel]

>>> Due to the application we used, we can't upgrade Oracle to 9.2.0.x.

Just say NO to that. Not acceptable.
Oracle 9.0 .x is pretty much a Beta version
No reasonable product requires it.
I appreciate 'certification requirements' and such, and I would understand requiring 8.1.7, but not 9.0.x.
Someone nneds to make a stand and break through that.

>> Could you tell me
Which issues are in 9.0.1.4 with dynamic sga and automatic pga? Does any Oracle document mention that?

It might not have been in 9.0, but you have the memory.. so for the next week just double the SGA and monitor statspack before and after, notably the "Buffer Pool Advisory", besides the usual hit rate, physical read IO rate and so on.


Hein.

[Alessandro Bocchino]

As regards the buffer cache size, you can enable db_cache_advice instance parameter, then look to v$db_cache_advice
to have an estimate of the changes in logical vs phisical reads changing the block buffers from 50% to 200% of the current value.

Anyway, a lot of Oracle instance parameters are important to have correct performance from the database, the optimal mix can be defined by a detail analisis of the database load and behaviour.

Alessandro Bocchino
http://www.risolve.com

[Frank de Vries]

Hi,
I answered before, but I found a nice
query in the meanitme that may be of help in analysing your SGA:

select pool, name, round(bytes/1024 ,1) "Size kb" from v$sgastat
order by 3 desc

This will show on what sections your SGA is
spend on.

I think this will be of help.