Re: Advice on running Oracle with SGA > 20 GB

Let's say there is no RAC, only standalone instances.

Of course "start from something and then iterate" is the best approach available.
However it is not very practical as:
1. Changing SGA_MAX_SIZE requires outage. 2. We found that on heavily loaded system dynamic changes of DB_CACHE_SIZE don't work very well.
For example when we tried to resize DB_CACHE_SIZE from 20 GB to 30 GB it took almost an hour.
During this time database experienced heavy waits on 'log file sync' - but there were no log switches
and few transactions. When we tried to reisize back to 20 GB the instance crashed. This was 9.2.0.8.
3. We would rather avoid significant changes (e.g. increasing DB_CACHE_SIZE from 20 GB to 40 GB),
this means that we need to go in small steps (20, 24, 28, 32, etc). But this is time-consuming.
Many systems are on weekly/monthly cycle (i.e. certain batch job run once per month),
so we'll have to wait one month to see the results. By that time the data may change. Received on Mon Feb 01 2010 - 19:19:30 CST