Oracle Server is 100% busy, what should I do?

From: Peizhong Wu <wupz_at_umich.edu>
Date: Tue, 09 Mar 1999 14:47:50 -0500
Message-ID: <36E57AE6.AC784807@umich.edu>

Hi, I am running Oracle 8.0.5 in a Sun Ultra Sparc I and Oracle application server 4.0.7 in another Ultra Sparc I. The stored procedure

will generate dynamic html files for the web server.

During peak time, ( 30hits/sec?), the server is extramly slow. The following is the result of 'top'

---

last pid: 15852; load averages: 12.36, 7.38, 4.12 14:06:46
86 processes: 66 sleeping, 19 running, 1 on cpu CPU states: 0.0% idle, 90.2% user, 9.8% kernel, 0.0% iowait, 0.0% swap
Memory: 128M real, 2448K free, 129M swap in use, 338M swap free

  PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND

15272 onesky     1  36    0   27M   18M run     3:45  5.73% oracle
15314 onesky     1  54    0   27M   17M run     1:44  5.36% oracle
15308 onesky    12  45    0   29M   19M run     4:26  5.33% oracle
15276 onesky     1  48    0   27M   17M run     4:57  5.30% oracle
15274 onesky     1  45    0   27M   17M run     0:12  5.15% oracle
15280 onesky     1  54    0   27M   18M run     4:34  5.10% oracle
14260 onesky     1  48    0   27M   18M run     5:19  5.05% oracle
15312 onesky     1  58    0   27M   17M sleep   1:14  5.05% oracle
15278 onesky     1  48    0   27M   17M run     1:49  5.04% oracle
14256 onesky     1  47    0   28M   18M run    20:42  5.00% oracle
14254 onesky    12  54    0   29M   19M run    14:26  4.99% oracle
15318 onesky     1  48    0   27M   17M run     1:06  4.98% oracle
15262 onesky     1  48    0   27M   18M run     9:45  4.83% oracle
15297 onesky     1  48    0   27M   18M run     3:48  4.81% oracle
15268 onesky     1  45    0   27M   17M run     0:15  4.77% oracle


*********************************************************************

There is always some DBA works or Optimizing queries to improve the DB performace, but will not be enough in such case.

There are two options:
1). add more memory in the machine. how much is enough? 2). replace the machine with a 'big' one. where is the end? Sun company

suggest E450 or E3500,
Does anyone have any idea?

By the way, anyone know if it good to ask DB server to generate html( using PLSQL). It will sure increase the load of server.

Thanks

Peizhong
University of Michigan Received on Tue Mar 09 1999 - 13:47:50 CST