Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 7.3.4 performance issues on AIX 4.3.2

Re: Oracle 7.3.4 performance issues on AIX 4.3.2

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 11 Feb 2004 17:58:01 -0800
Message-ID: <2687bb95.0402111758.50b9cb36@posting.google.com>


"Gerard H. Pille" <ghp_at_skynet.be> wrote in message news:<402A843F.8080700_at_skynet.be>...
> Khurram Khan wrote:
> > 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
>
> Hallo KK,
>
> I suppose you are aware that your config is a bit outdated, but if you can lay
> hand on the installation manuals for O7.3.4, verify if all necessary patches
> have been installed on your AIX.
>
> Geert

1- Old OS, old Oracle, probably means old hardware. Perhaps it is getting tired :-D

2- When was the last time you updated your statistics or are you running rule based? Either way with 7.3 you have bstat/estat available so perhaps you should fire it off during a peak period for 10 minutes or so and see what it shows.

3- Look for the heaviest hitters for physical and logical IO and make sure these statements are running well

4- Did you just recently switch to RAID-5? Basically RAID-5 is not a very good database disk setup, but with suitable cache in front of it you can generally get by. How much cache is in the RAID box?

How many different RAID stripes do you have? How many disks to each stripe? And how is your database partitioned accross the stripes?

Without more detail nobody is going to be able to pinpoint your problem for you.

HTH -- Mark D Powell -- Received on Wed Feb 11 2004 - 19:58:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US