Memory Usage for a 12 G Oracle 9i database

[TWBSupport]

Hi all -
Got a question.. I have a rp2470, 1 650 cpu w/8G RAM w/8G swap. Oracle developers & dba are telling me that this is not enough memory for a 12G Oracle 9i database. Hate to kill their expectations, but the system is to the max RAM it will hold. But I was wondering what you all think on this. Where I came from prior to this job we ran Solaris (can't remember the version (4 years ago) w/ a 10G Oracle 8.16 database on 2G RAM & everything ran fine with no issues what so ever. Is it normal for Oracle 9i reports to need more than 8G RAM (8G SWAP (2G used in SAM))to run optimally for a 12G database on HPUX? Are there any more optimizations I can do on the system side? I think I've done all I can, but I definately could have missed something. Thank you all for your opinions & advice.

[A. Clay Stephenson · Accepted solution]

This is a bit difficult to answer because it depends more on the actual load than the size of the database. It's possible that if many clients are connecting or you are running large applications locally then you could have a memory bottleneck. Having said that, you really need to start doing some memory usage ananlysis. At the very least look at swapinfo and vmstat output. If your pageout rate is significant then you need more memory OR you must reduce the load. If you are seeing swapping activity then tell your DBA's to reduce the size of their SGA because the performance hit from swapping is at least 2 orders of magnitude greater than that of additional disk i/o because of reduced caching.

It's also possible that you have poorly designed code so that the only way it operates well is to have almost the entire database cached.

[TWBSupport]

We have 50 users max accessing this database, the daily load is approx 30 - 40 from 8 - 5. 15 - 25 after normal business hours. I have run swapinfo when they state the database is dragging & it's not even being utilized, I was somewhat surprised when I checked SAM prior to posting this that it stated 2G was used, since swapinfo never is showing the swap being accessed. Even at the slowest database crawl, the physical memory usage is at 74%. And swap 0%. Granted this is a slow day today, but here is how it's setup.
swapinfo
Kb Kb Kb PCT START/ Kb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4194304 0 4194304 0% 0 - 1 /dev/vg00/lvol2
dev 4194304 0 4194304 0% 0 - 1 /dev/vg00/swap1
reserve - 2916444 -2916444
memory 6457772 852760 5605012 13%

[A. Clay Stephenson]

You aren't swapping; this strongly suggests that more memory wouldn't help. I suspect that the hardware/OS is not the component that needs tweaking but rather the SQL code itself. It's time for the DBA's to do some analysis.

[TWBSupport]

I wanted to add the SAM memory info also.

Physical Memory: 8199.3 MB
Real Memory: Active: 646777.7 KB
Total: 2714923.8 KB Virtual Memory:
Active: 736910.1 KB Total: 3029688.8 KB
Free Memory Pages: 990785 at 4 KB/page
Swap Space:
Avail: 8192 MB Used: 2873 MB

[TWBSupport]

Clay -
That's what I was thinking, but I thought maybe I was missing something. I have 3 developers & 1 dba that keep coming down on me with this & keep telling my boss that it's the hardware, so I, of course, start second guessing everything that I've been telling them & decide to see if I am wrong. Thank you for your help.

[Jean-Luc Oudart]

Hi Belynda

Adding extra hardware should be last resource. There is plenty to do before such as running statspack and produce reports (as your DBA). I would suggest that when things are good you should keep reports for the baseline (OS & Oracle).
This could be new (and long) transactions or some locks on table. Again, the DBA should check for these !

Regards
Jean-Luc

[Mark Grossman]

hello,
I agree with the rest of the folks. Our DBA's would actually be more concerned about the single cpu. They like at least 2 for Oracle. However, i assume youve checked cpu usage as well as disk, network , etc when they are complaining.
There are so many database and code options that can effect their application performance. If you can show them some stats from sar or glance or perfview to back up your claims that the system is not having the problem, then perhaps they will start looking elsewhere.

Mark

[A. Clay Stephenson]

Let me give you my standard question for DBA's. If the response were 2x as good, would this still be a dog? If the answer is yes then the only place to look is the software. If the answer is no then you might consider tuning better although in the vast majority of cases, improving i/o layouts, increasing memory, cpu's, etc. typically improvers things by about 1.2. In many cases, even with a 10X increase in performance, the dog would still be there; in those cases, the ONLY place to look is the code.

Because this is a database server (and because a legacy bad tuned parameter set may have been inherited) there is one tunable that can have absolutely terrible impact upon performance and that is a timeslice setting of 1 rather than 10.

I find that baseball bats are excellent tools for adjusting DBA's.