Memory bottleneck on L-server running HP-UX11i and Oracle 8.17

[Kirill Cherkashin]

Hello wizards,

I'm running Oracle 8.17(one instance,SGA 1.5 GB) on L server(2 CPU,2Gb RAM,HP-UX 11i) and ~ 100 users connecting to the server. In last time I noticed that system is working very slow. I'm very suspicious that it's mailnly just memory bottleneck.;-)
However, could you mighty wizards confirm it?
Also,pls. find attachment with outputs.
If yes,how much memory I need to buy? Btw,how could I calculate how much memory I need for every user process?

Thanks in advance,

Kirill

[Stefan Farrelly]

Yes, youre certainly out of memory. With free memory only being a couple of MB and lots of paging going on. To really see how much memory you are short you need to to a swapinfo -mt command and look at the figure on the dev line under MB used - this is how many MB has been paged to disk (and how much RAM more-or-less youre short).

I also notice your disk I/O is very heavy. wio% is very very high. It should be <10%. But if the users arent complaining then ok.

To work out how much RAM each user is using look at the 2nd column from your ps command, specifically at the oracle proceses with LOCAL=YES in them. Each one is over 100MB which is very large! (take the 2nd column figure and * 4096 to get size in K). This is a rough size as some is shareable with other processes and not taking into account what the Oracle dastabase itself is using to manage it all (shared memory) but its a pretty good indiciation.

[Kirill Cherkashin]

Hi Stefan,
thank you for quick answer.

yes,I forgot to put output from swapinfo.

pls. find it below:

# swapinfo -atm
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4096 565 3531 14% 0 - 1 /dev/vg00/lvol2
reserve - 1758 -1758
memory 1537 307 1230 20%
total 5633 2630 3003 47% - 0 -

and of course users are not complaining at all they just screaming almost whole day.;-)

[Stefan Farrelly · Accepted solution]

Hi,

Well, the 2nd figure from the dev line on swapinfo is 565, which means at the point your system was the most busy (may not have been today, anytime since it was last booted) your had 565MB of processess paged out to disk. ie. your 565MB short of the maximum you need.

This doesnt mean to say you need to buy some more RAM just yet. How much HP-UX buffer cache is configured ?

sysdef | grep bufpages

And multiply the figure back * 4096 for the size in bytes. Your total is probably somewhere in the hundreds of megabytes. You can decrease this to free up some memory by configuring these 2 parameters in your kernel;

dbc_mac_pct
dbc_min_pct

You can use sysdef | grep dbc to see what they are now also.
For your system I suggest you set
dbc_max_pct 10
dbc_min_pct 2
This will set the HP-UX cche to a max of 200MB. You could decrease this even more if you want, to say 5 (5% or 100MB). Build a new kernel and reboot and watch swapinfo over the next few days to see how much memory is paged out.

[Andreas D. Skjervold]

Hi

With dedicated server each user process consumes approx 34 MB... which alone gives you some to chew!

If your Oracle server is set up with Multithreaded Server, the picture is quite different as most of the PGA of the user process is moved inside the SGA and is beeing queued between the users. This might also give poor performance if some user processes is performing large batch jobs, as everyone then will have to wait in line for the shared PGA to be available. MTS is great for random selects / updates, but not for batch loads etc.

You'll find the user processes as oracle and see for yourself what the size is.

Andreas

[Kirill Cherkashin]

#sysdef | grep bufpages
bufpages 26214 - 0- Pages -
# sysdef | grep dbc
dbc_max_pct 20 - - -
dbc_min_pct 5 - - -