System hanging !!

[kpatel786]

We have 2 oracle intance on our hp box. From last few days we have facing a problem of system going in hang status whenever the users connect to input data. The IDLE % becomes 0 as the users do transaction
We have rebooted the server.

Currently it is the same situation.
The cpu usage is as below:

System: server1 Fri Oct 17 08:52:07 2008
Load averages: 3.43, 3.00, 3.48
382 processes: 332 sleeping, 49 running, 1 zombie
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 4.40 96.0% 0.0% 4.0% 0.0% 0.0% 0.0% 0.0% 0.0%
1 2.46 91.0% 0.0% 9.0% 0.0% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 3.43 93.1% 0.0% 6.9% 0.0% 0.0% 0.0% 0.0% 0.0%

Memory: 3700328K (3149332K) real, 8126080K (7380664K) virtual, 2309224K free Page# 1/11

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
1 ? 27840 nlhelma1 240 20 539M 6696K run 5:40 43.44 43.36 oracleuser1
0 ? 4637 nlhelma1 152 20 334M 78888K run 28:15 42.05 41.98 mocasrvprc
0 ? 20430 nlhelma1 237 20 539M 6504K run 14:22 28.35 28.30 oraclenuser1
0 ? 4741 nlhelma1 237 20 539M 7016K run 91:59 18.05 18.02 oraclenuser1
0 ? 8371 nlhelma1 234 20 541M 8744K run 51:15 15.42 15.39 oraclenuser1
0 ? 5743 nlhelma1 154 20 539M 6696K sleep 53:43 12.30 12.28 oraclenuser1

The swap usage is as below:
server1 (/) # swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4096 58 4038 1% 0 - 1 /dev/vg00/lvol2
dev 4096 57 4039 1% 0 - 1 /dev/vg00/lvol10
dev 4096 58 4038 1% 0 - 1 /dev/vg00/lvol11
reserve - 4444 -4444
memory 6490 1717 4773 26%
total 18778 6334 12444 34% - 0 -
You have mail in /var/mail/root


The sar output is :

rpsrv2 (/) # sar -u 5 5

HP-UX rpsrv2 B.11.11 U 9000/800 10/17/08

08:58:50 %usr %sys %wio %idle
08:58:55 90 10 0 0
08:59:00 88 10 2 0
08:59:05 86 14 0 0
08:59:10 72 12 15 1
08:59:15 90 9 1 0

Average 85 11 4 0

Your inputs are welcome. This is a serious issue. Kindly assist.

[Frank de Vries]

This are not very nice problems.
Seems that you are suffering from either
a genuine bottleneck or just a roque feature (bug) on either HPUX or Oracle

Check the logs of both OS and Oracle?

Version OS and Version Oracle,
is this problem know on Metalink ?
Are any patches due ?

Did you comply to kernel parameter prerequisites ?

What are the size of each SGA in relation to your unix memory

Can you identify a resident set that is growing ps -efl | grep oracle
look for column SZ

Does the problem occur also with one instance?

How many query are run on your system ?
Did you apply statspack and analyze where the bottleneck are ?

[Wim Rombauts]

can you tell us a little more about the processes : I assume oracleuser1, mocasrvprc and/or oraclenuser1 are the Oracle user processes (dedicated connection), but which 2 out of these 3 are oracle, and what is the other one.
What version Oracle are you using ? With this information we can see what tools are available.
When is the database hanging up, just with whatever connection you make ? Or when users from a certain application start making connections.
What happens if you just kill the most busy user connections ? Does it allow you to continue working for a few seconds/minutes ?
Have you tried to run statspack or AWR reports ?

[kpatel786]

Thanks.