performance degrading

[radi_1]

Hi,
I have a customer whoes system performance degrades whenever a certian user starts runing an oracle 7 job.the top command shows for this user;
PRI NI SIZE RES %wcpu %cpu
227 20 7900K 1068K up to 93 up to 90
the rest of other users processes go sleep.Basically not many proceses run at the same time on the system ,the top command shows many procsses are sleep even when that troublesome user is also sleep.
Other info.
system model is D-220
hp-ux 10.20
RAM is 256MB
swap is 512MB
No. of actual users is 30
No. of active users at one time could reach to 40.
Now one other weird thing,when I run dmesg command, a list of repeated error messages appear:

file:table is full

As far as I know this is somthing to do whith nfile kernel parameter,the values I have on the system are;
maxfiles 60
maxfiles_lim 1024
maxuprc 75
nfile 1251
nflock 200
ninod 764
nproc 532
maxusers 64
I have other parameters if any one interested to reply.
Please if this is a kernel parameters issue,what parameter is causing the problem and exactly what value should these parameters have or is it after all an oracle problem?

[harry d brown jr]

You need to immediately bumb up these parameters!

maxfiles - From 60 to at least 512
maxfiles_lim 1024 - double it
maxuprc 75 - to 256
nfile 1251 to around 20000
nflock 200 to around 30000
ninod 764 to around 50000
nproc 532 to around 2000+
maxusers 64 - to 128

Here's two tuning documents:
http://otn.oracle.co.kr/docs/oracle78/hp/server.733/a43837_2/ch1.htm

http://www.interex.org/conference/iworks2001/proceedings/1034/1034.pdf


live free or die
harry

[Rita C Workman]

I agree with Harry you need to increase some kernel parms.
BUT, I'm a bit more conservative than Harry....you didn't mention anything on your server configuration.

When I increase parms, I 'tune'....in other words I increase in reasonable amounts and monitor to see how the system performs..then I tune some more depending on results and resources available.

Instead of big jumps, why not try increasing maxusr from 64 to say 200...other parms should adjust accordingly with this change. Increase your maxfiles to 512.
I would ask what is your dbc_max% and dbc_min%?...if it is at default of max/min 50/10 than try to reduce to 10/5. I might also wonder what your maxuprc is set at...

But again...since we don't know your configuration, we are just taking a best guess here......

Rgrds,
Rit

[radi_1]

Hi Harry and Rita
Thanks for your replies.I will try to do what you have suggested if customer agrees to reboot and I will come back to you as soon as possible.
As for you Rita ,I am not quit sure what you mean by server configuration but still the following info. might help you;
One H.D on the system configured as volume group.
The box on a LAN with no subnets.
Network media is 10Base2
Users connected through terminal servers.
One instance oracle application running on the server.
Regards.

[Rita C Workman]

What I meant for configuration was:
*Type of server (N class K class)
*Amount of memory
*O/S Platform (hpux 10.2 or 11 or 11i)
*Application (Oracle,Informix,SAP)

You might also run swapinfo -tam and post results
..as well as attach a list of your parms.

Rgrds,
Rita

[harry d brown jr]

Rita,

You are right, I forgot that it's only a D-220, I get so used to having big machines with lots of resources.

live free or die
harry

[John Palmer · Accepted solution]

Hi,

This sounds more like an application problem to me. So this Oracle process takes a long time - you need to get your DBA to check the SQL and how it is being processed by Oracle. Maybe full table scans of large tables are being done. If the process is holding database locks then it could be stopping other users from doing any work.

Regards,
John
P.S Rita - fair play almost all the information that you asked for was in the original post ;-)

[Rita C Workman]

My apologies....
I guess it's like the difference between hearing and listening...I read but did not see.

Rita

...maybe I really do need that vacation...

[radi_1]

Hi Harry anad Rita,
I could not carry out your segestions because customer has called the oracle people and they have made some corrections on their tables and the system performance has improved a little and said more corrections are needed so I have to wait to see the end of thier work then I wiil make kernel parms change if needed to further improve performance.So in any case points will be coming your way when the matter is setteled.
As for john Palmer I think your opinion might prove to be the right answer and I let you know
soon and full mark to you if that will be the case.