We need to decide on memory configuration for legacy application
(let's call it ABC) that uses 9.2.0.8 database. It will run on new
M5000 Solaris 10 server with 128 GB RAM. The question is: how big
should be SGA?
Background information:
- ABC is CRM application (not Siebel), important for the company.
There are plans to replace it with something else, so it wasn't
upgraded, but for the next 2 years it will be doing bulk of CRM
processing
- Current instance is running with SGA = 50 GB, buffer cache = 30 GB,
PGA target is set to 10 GB but it never used more than 3 GB. We are
not changing Solaris or Oracle version.
- ABC outages are very difficult to organize, current instance was
last restarted in April 2010. Oracle is stable, we are not seeing any
memory leaks, frequent ORA-00600, ORA-07445. The server is also
stable, it has only 8 GB swap and swap utilization is 0.
- ABC workload is mix of batch and online, online is responsible for
15% of overall load and batch for 85%. Online queries are well-tuned
but batch queries are quite bad: very large (several pages long),
involving multiple joins, subqueries, NOT EXIST, IS NULL, etc
conditions. Some batch jobs run for several ays. UNDO_RETENTION was
set to 300,000 to avoid ORA-01555 for long-running jobs. Tuning these
batch jobs queries is next to impossible. Many jobs run much longer
than we want them to run.
- We did some performance testing on the new server: used script that
submitted several batch jobs, each in a loop. Test were done with 3
values of SGA/buffer cache: 50/30 GB as in current prod, 60/55, and
88/84. They have shown significant improvement in performance
(reduction in runtime of batch jobs) when buffer cache was increased
from 30 to 55 GB and almost no improvement when buffer cache was
increased from 55 to 84 GB. I suspect that this is due to the fact
that in these tests batch jobs were processing the same sub-set of
data over and over again. In real production the jobs will be
processing different data. Buffer Cache Advisory predicts improvement
in performance for bigger cache: if cache is increased from 85 GB to
170 GB number of physical reads should fall from 188 million to 85
million.
- There is another database on this server, it uses around 10 GB.
- pmap shows that Oracle uses 4 MB page size

Question: how big should be SGA/buffer cache? There are two proposals:
1. Configure SGA around 90 GB, it still leaves around 15 GB memory
free.
2. Configure SGA 55 GB and leave 50 GB memory free.

On Wed, 9 Nov 2011 23:18:41 -0800 (PST), vsevolod afanassiev
<vsevolod.afanassiev (AT) gmail (DOT) com> wrote:


I believe even in -desupported- 9i the SGA advisor was already
available, so did you use it to see what it tells?
Looks a better method than throwing up a coin, or asking an advice of
people who don't know the application.

----
Sybrand Bakker
Senior Oracle DBA

In Prod Buffer Cache Advisor doesn't work, it reports rubish.
It Performance Test it worked and I included results in my post:

What I am looking for is advice on how to aproach this issue, what
should we look at.

Thanks

On Nov 10, 5:28*am, vsevolod afanassiev
<vsevolod.afanass... (AT) gmail (DOT) com> wrote:
I would the starting point would be exactly what you have. The fact
that you saw no real performance improvement when you bumped the
buffer pool from around 50G to 80G should be taken at face value. In
every system there is a point where the returns from more resources
start to decline. In fact there is a point where adding another
resouce especially cpu actually results in a decline in overall
performance. These points depend on the application activity pattern
and the manner in which the applicaiton is coded.

HTH -- Mark D Powell --