Re: advice on memory configuration
Date: Thu, 10 Nov 2011 09:42:05 +0100
Message-ID: <7f3nb7led492jdnq9qgv5603g9j2m4168h_at_4ax.com>
On Wed, 9 Nov 2011 23:18:41 -0800 (PST), vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com> wrote:
>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.
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 Received on Thu Nov 10 2011 - 02:42:05 CST