application performance running on AIX machine

[billy1]

We have an oracle 8i database running on an IBM AIX version 4.3. This database is for an application that can run using reflections (telnet connection) or as a GUI interface run on NT. The application though has a very slow response time. At all times there seems to be three or four heavy processes on the AIX machine. When these are looked at closely they can be merely select statements on the database or something like that. If I run the 'top' command I can see these heavy processes.
top
oracle 111580 1 34 09:30:30 - 22:00 oraclepmsy (DESCRIPTION=(LOCAL=
no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 112468 1 32 08:34:42 - 0:45 oraclepmsy (DESCRIPTION=(LOCAL=
no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 37718 1 30 10:49:48 - 5:25 oraclepmsy (DESCRIPTION=(LOCAL=
no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 8028 1 30 09:09:34 - 17:21 oraclepmsy (DESCRIPTION=(LOCAL=
no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 87290 1 27 11:09:14 - 0:03 oraclepmsy (DESCRIPTION=(LOCAL=
no)(ADDRESS=(PROTOCOL=BEQ)))
webstep 63528 93280 15 10:49:47 pts/14 1:59 /pcr_78/bin/czmp072 WEBSTEP WEB
STEP PRD GROUPNO=1
oracle 19358 1 5 08:34:02 - 0:30 oraclepmsy (DESCRIPTION=(LOCAL=
no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 117338 1 2 09:50:47 - 0:31 oraclepmsy (DESCRIPTION=(LOCAL=
no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 45092 1 2 07:4


Also I run vmstat and can see the system is running slowly:
vmstat 2 5
kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
1 1 182536 622 0 0 1 36 94 0 212 319 315 45 27 25 3
8 3 182629 515 0 3 0 0 0 0 399 9342 2364 42 58 0 0
6 2 182767 314 0 0 0 0 0 0 443 7100 4391 58 42 0 0
6 2 183576 214 0 2 30 358 858 0 484 7433 4919 60 40 0 0
10 2 183193 1661 0 0 6 553 1053 0 308 7727 3067 30 70 0 0

There's nothing idle...
My question is, why is this happening ? Is the problem with the database or with the AIX machine ? Am I short of memory ? Or if the network was slow would this cause these simple database queries appear as large processes on the Unix O/S side ?
Any help would be much appreciated.

 

[Morsing]

The vmstat output shows it not a memory problem and not a disk I/O problem. It could be a slow network but it looks like the processes are blocked or trying to do something they can't??

Strange they don't appear to be struggling for resources.

Cheers

Henrik Morsing
Certified AIX 4.3 Systems Administration
& p690 Technical Support

 

[DSMARWAY]

hi,

it could be the SQL query the user is running that is causing the slow performance, you need to analyse what the user is running , does the dbase have enough dbase SGA area. You have mentioned users do a select statement , are they selecting and searching through big tables , do the tables have indexes.

HTH

 

[billy1]

Yeah you're hitting the right note there. The Database which is supplied to us by another company is missing indexes all over the place. A lot of jobs that take a long time have been speeded up with the addition of indexes but I wasn't sure whether this explained the big processes on the OS side. i.e. if you're running a select query on a big table without an index (ok it will take a long time) but will it also produce this large process I see on the OS side. Also with the introduction of an index will the process appear smaller on the OS side ?

Similarly with the slow network - will it cause processes to appear large ?

Many thanks ...

 

[AIXSPadmin]

If the fr:sr ratio is greater than 1:4 then there is a problem somewhere. Also the run queue should be no more than 4 or 5 times the number of CPUs.