Oracle and memory

[Jean-Luc Oudart]

Hi again,

also before you make any change you should take a few statspack reports (ask the DBA) along with OS monitoring.
Then apply the changes (usually one at a time), monitor again (OS + Oracle) and compare with the baseline.

The baseline is important for this exercise as well as when users will(!) complain about performance. At least you will have something to go back too. Most of time it's either the increase in load (average number of users /transactions) and/or new transactions.
Your perfstats reports will help you (and DBA).

Regards
Jean-Luc

[Ted Buis]

You haven't really told us much about your environment. Do you have OnLineJFS? Do you have GlancePlus? What is your buffer cache kernel parameters. Is your swapchunks/maxswapchunks value high enough to address all your virtual memory.

Your buffer cache is likely wasting memory if you still have the default for the maximum dynamic buffer cache of 50%. Also, you can avoid double buffering from Oracle and the HP-UX OS if you select the proper mount options if you have OnLineJFS, which would waste memory and create extra memory to memory transfers.

GlancePlus can show you your peak and average memory usage, so it will allow you to see how much unused memory you really have.

Since RAM access is 1000 times faster than disk access, I still think that more RAM is a great asset, but just adding it doesn't optimize performance. Optimization requires looking at the system to see what is the precise bottleneck for your particular case. You might want to get a copy of the book "HP-UX 11i Tuning and Performance" by Robert F. Sauers to better understand the tools available for examining system performance.

[Stafford Hyacinth]

All,

I have onlineJFS & GlancePlus.

The kernel params are as follows:
bufpages: 0
dbc_max_pct: 7
dbc_min_pct: 5
maxfiles: 4000
maxfiles_lim: 1024
nfile: 123000
nflocks: 2000
ninone: 34816
maxswapchunks: 8192
swapmem_on: 1
swchunks: 2048
vps_ceiling: 64
maxdsiz: 2415919104
maxdsiz_64bit: 2415919104
maxssiz: 401604608
maxsxiz_64bit: 1073741824
maxtsiz: 1073741824
maxtsiz_64bit: 1073741824
maxuprc: 3000
max_thread_proc: 4096
ntkthread: 7184
nproc: 4096
msgmap: 4098
msgmni: 4096
msgseg: 32767
msgtql: 4096
semmap: 4098
semmni: 4096
semmns: 16384
semmnu: 4092
semvmx: 32767
shmmax: 12884901888
shmmni: 1024
shmseg: 1024

[Ted Buis]

If the swap page is 4K (I think, maybe someone can help here, since I don't have time to look it up right now), then you only have 32GB covered. In that case the system will deallocate 8GB of your 40GB memory. What does swapinfo -tam say?

[Stafford Hyacinth]

swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4096 0 4096 0% 0 - 1 /dev/vg00/lvol2
dev 4096 0 4096 0% 0 - 1 /dev/vg00/swap2
dev 4096 0 4096 0% 0 - 1 /dev/vg00/swap3
dev 4096 0 4096 0% 0 - 1 /dev/vg00/swap4
reserve - 12915 -12915
memory 31561 16789 14772 53%
total 47945 29704 18241 62% - 0 -

[Ted Buis]

Actually, the maximum swap space is determined by the formula maxswapchunks*swapchunks*1024. So, your 8192*2048*1024=16GB which is the physical device swap that you have. You may wish to consider increasing maxswapchunks to 2-3X the current value, so you wouldn't have to do that if you decided to add more device swap, but I don't think it is necessary now. I was thinking that the this formula needs to cover the virtual address space of physical device swap space plus pseudoswap which in your case is 75% of RAM or ~30GB for a total of 46GB, but that doesn't appear to be the case. It seems to only have to cover the physical device swap space.

[Ted Buis]

You could use the "top" command to verify that your system sees all the physical and virtual memory.

[Steven E. Protter]

Few things occurred to me.

1) There is a limit on how much swap the OS will use. I can't remember if it was 16 GB or 32 GB but beyond that there is not more.

2) Powerful system you got, love working with big hardware.

3) Your shmmax might be a little light for Oracle. You can go up to 25% of memory which HP-UX defines as ram plus swap. 1.2 GB of shmmax isn't going to be enough to run the kind of database a system this powerful is likely to run.

SEP

[A. Clay Stephenson]

Shmmax is not 1.2GB but 12GB and for most applications that's plenty. Your situation may not be too dissimilar to this scenario: Consider a systems doing thousands of logical i/o's per seconds but only a very small number of physical i/o's per seconds. I have seen exactly that kind of situation in Oracle and Informix. Admittedly my example involves buffer cache but the situation is exactly the same in doing i/o in the SGA. The problem is that the system is doing tons and tons of reads (albeit logical ones) and the performance can be terrible. In cases like this, the answer is not more resources and faster hardware but better algorithms. In many, many cases one index exceeded all the resources and tuning by an order of magnitude.

You and your DBA's really need to get a handle on where the bottlenecks are and attack the problem in the order of the biggest bottlenecks.

I'll leave you with one other whackball thought. Convince your management to do development and test on an intentionally resource limited box with less than warp-speed hardware. When the algoritms are good enough to work well in that environment then they almost always scale up very well in production environments. The fact that this may greatly annoy your developers is an added bonus.

[Steven E. Protter]

Always good to have a proofreader.

Thanks A. Clay.

SEP