advice on memory configuration
From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Wed, 9 Nov 2011 23:18:41 -0800 (PST)
Message-ID: <3c8f3497-0934-44ad-961e-0239754e8407_at_d37g2000prg.googlegroups.com>
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.
Date: Wed, 9 Nov 2011 23:18:41 -0800 (PST)
Message-ID: <3c8f3497-0934-44ad-961e-0239754e8407_at_d37g2000prg.googlegroups.com>
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.
Received on Thu Nov 10 2011 - 01:18:41 CST