Re: Oracle 7.3.4 performance issues on AIX 4.3.2

Hi Khurram,
as u can see the wait for io is rising dramatically. The same thing happens on solris too.
If u can, try to turn off logging for the most traffic causing tables. Check if these tables are causing page segmentation exceptions e.g. your segments are 512 Bytes and a row has a size of 513 Bytes. Rebuild your indexes periodically
Reduce private memory allocaton for sessions Have a look at Tom Kytes http://asktom.oracle.com for more high sophisticated tips
Try http://www.oratool.de for getting statistics easily

good luck
Martin

"Khurram Khan" <khurram.khan_at_qict.net> schrieb im Newsbeitrag news:a23090a4.0402110605.b88b671_at_posting.google.com...
> Hi Guys,
>
> We are using AIX4.3.2 with Oracle 7.3.4 on F50 configured Paging space
> is 512MB with 40% max utilization. Database is installed on SSA RAID
> box(hdisk1) where we have configured RAID5.
>
> I have noticed immense performance issues with server in peak hrs, as
> per my observation before we / user execute any search query on
> database system performs in reasonable condition ASA we execute any
> search query server encounter extreme bottleneck issues
>
> Below I have given output of sar vmstat and iostat commands before
> executing search query and after executing search query, please advice
> how can we overcome this issue, required indexes are already available
> in oracle.
>
>
> Server performance before user execute any query on database.
> %usr %sys %wio %idle
> Sar Average 26 5 4 65
>
> C/ >vmstat 1 10
> kthr memory page faults cpu
> ----- ----------- ------------------------ ------------ -----------
> r b avm fre re pi po fr sr cy in sy cs us sy id wa
> 1 1 68531 2043 0 1 1 75 228 0 250 1973 397 37 11 45 7
> 1 1 68531 2038 0 0 0 0 0 0 227 5586 202 27 7 61 5
> 1 1 68531 2038 0 0 0 0 0 0 209 5682 156 26 6 68 0
> 1 1 68531 2036 0 0 0 0 0 0 225 4890 324 39 6 50 5
> 1 1 68531 2036 0 0 0 0 0 0 207 5589 153 23 6 71 0
> 0 1 68531 2036 0 0 0 0 0 0 207 5310 145 25 4 71 0
> 0 1 68531 2036 0 0 0 0 0 0 212 5323 161 26 8 63 3
> 0 1 68531 2028 0 0 0 0 0 0 218 4829 246 28 15 46 11
> 0 1 68531 2028 0 0 0 0 0 0 204 5353 154 24 5 71 0
> 1 1 68531 2028 0 0 0 0 0 0 220 5520 164 32 5 62 1
>
> iostat
> Disks: % tm_act Kbps tps Kb_read Kb_wrtn
> hdisk0 0.0 0.0 0.0 0 0
> hdisk1 6.5 16.0 4.0 0 32
>
>
> After the execution search query on database
> %usr %sys %wio %idle
> sar 35 16 49 0
>
> / >vmstat 1 10
> kthr memory page faults cpu
> ----- ----------- ------------------------ ------------ -----------
> r b avm fre re pi po fr sr cy in sy cs us sy id wa
> 1 1 81410 124 0 1 1 75 228 0 250 1958 397 37 11 45 7
> 2 2 81410 121 0 0 0 90 315 0 294 5485 327 21 11 0 68
> 0 1 81427 127 0 0 0 99 554 0 279 5620 338 30 9 11 50
> 1 1 81427 127 0 0 0 0 0 0 213 5388 184 23 14 60 3
> 1 1 81427 127 0 0 0 0 0 0 210 5344 156 23 6 71 0
> 0 1 81427 127 0 0 0 0 0 0 216 5348 157 22 6 72 0
> 0 2 80914 669 0 0 0 0 0 0 217 5601 173 25 5 68 2
> 0 1 80631 985 0 0 0 0 0 0 207 6717 188 28 9 63 0
> 0 1 80631 985 0 0 0 0 0 0 208 5506 167 31 7 62 0
> 0 1 80631 984 0 0 0 0 0 0 209 6042 173 29 5 63 3
>
> iostat
> Disks: % tm_act Kbps tps Kb_read Kb_wrtn
> hdisk0 5.0 20.0 4.0 0 20
> hdisk1 94.0 416.0 102.0 400 16
Received on Sat Feb 14 2004 - 06:02:51 CST