Memory question

[John Pavao_1]

We're running an EDI application called Gentran:Server from Sterling Commerce as well as Oracle on a 9000/800/L2000-36 running B.11.11. I've searched this site for information regarding how much memory should be on this machine but I must not be using the right search terms because I can't find anything helpful. What's driving me nuts is that I swear I saw something out here once that indicated that this machine should have twice as much RAM as it does but I can't find it now.

If I run Glance, CPU is usually at 100%, disk varies widely, Mem is usually in the high 90s, and swap util is always 38%.

The machine has two CPUs and 2GB of RAM. Is this machine underpowered or short on RAM for what we're doing with it, or are these numbers ok? Or is there some other information I could supply that would help people make a determination? I'm not an admin so I don't have access to tools that are restricted to root... The reason I'm asking about this is that response times from this system vary wildly from very quick to "go get a cup of coffee" at times, with the worst being anything that uses disk. I know that would be slower, but my experience with this application on this server type elsewhere was that it ran a lot faster in the same configuration without Oracle also running on the box. So I'm wondering if having Oracle on the server means we might need more RAM or more CPUs or if it wouldn't really help at all and this is just the way it is.

Thanks,
John Pavao

[Sridhar Bhaskarla · Accepted solution]

Hi John,

1. Swap utilization at 38% doesn't necessarily mean the system is having enough memory. If you configured way too much swap, then it will show low utilization.

2. TO better find whether the machine is short on memory, there are couple of checks

swapinfo -d

If you see anything under 'KB Used' column then there is something paged out to swap areas. That doesn't necessarily mean the system is low on memory either but may be an indication.

vmstat 5 200

Keep looking at "PO" column. These are page outs. If you see them more than 10 during the 'go get a cup of coffee' time along with above 'swapinfo -d' true condition, then your system is paging out and it is definitely low on memory.

You can also use 'glance' to find it out. Press 'm' and observe 'page outs'. If you see continuous non-zeros, then your system is short on memory.

To determine it from here, post the outputs of

swapinfo -dftr
vmstat 5 5

One kernel parameter that can really misuse the memory is dbc_max_pct. Make sure it is not at it's default value which is 50%. It will be a overkill particularly if your system does a lot of disk IO.

-Sri

[Sundar_7]

Poor response time doesnt necessarily mean memory bottleneck.

If the CPU usage is almost always at 100%, it could as well be the bottleneck in the system.

execute "sar -q" and see if runq-sz and swpq-sz is more than 5.

A relatively large number of processes that are in the runnable state but waiting for the CPU cycle usually would mean CPU bottleneck in the system

[Steven E. Protter]

In an ideal system swap utilization would be zero.

What I'd do is some comprehensive data collection (script attached) and look closely at the data.

The RAM is a bit now. We're upgrading our boxes right now which are similar to accomodate heavy hitting oracle ias 10g.

SEP

[Bill Hassell]

100% CPU is not a bad thing, it is a result of the applications are asking the computer to do. Start by seeing if you have enough CPUs installed:

uptime

In general, if the average load over 1,5,15 minutes (the last 3 numbers in uptime) is significantly larger than the number of CPUs, you need more CPUs installed. For your system, uptime numbers larger than 3.0 for long periods will be helped with another couple of CPUs. Now keep in mind that a poorly written script of program may saturate a single CPU so you may need to investigate the processes that consume the most CPU. The top command will help.

As far as RAM usage, just tell your Oracle DBAs to use a lot more SGA to improve performance (which it can) and you'll find that 2Gb is far too small for Oracle. A small number of users and/or a small number of transactions per minute and/or mostly simple SQL tasks will put very little load on Oracle and the response will be adequate. In general, a large SGA for Oracle will reduce disk I/O but will add more load to the CPUs.

There is no simple answer (more RAM, more CPUs, better written SQL tasks, more Oracle indexes, etc) to optimizing database response times. Generally, most response time problems to specific queries can be drastically improved with more indexes, balancing existing indexes or just rewriting the SQL code. Once Oracle has been optimized to the type and frequency of database requests, just add more RAM and CPUs (I'd add 2Gb immediately).