Re: Oracle performance issue

Mauro Minomizaki wrote:
>
> "Tom Hoffmann" <tom.hoffmann_at_worldnet.att.net> wrote in message
> news:S3su7.25346$W8.1674177_at_bgtnsc04-news.ops.worldnet.att.net...
> > On Tuesday 02 October 2001 22:59, Mauro Minomizaki wrote:
> >
> > > Hi there,
> > > It is my first post here, and I hope I'm posting this message in the
> > > right forum.
> > > I have an IBM AIX server machine running Oracle 8.0.6.3 and AIX
> > > 4.3.3-ML08 update.
> > > When running several select count(*) in parallel I noticed a
> > > performance degradation that is pretty unusual.
> > > Taking a look at the processes table, I see that asynchronous I/O
> > > servers (aioserver) are consuming all CPU available on this server
> > > (12-way SMP) and the strange thing is that there is very little I/O
> > > really running to the disks.
> > > I am sysadmin of this server, so, I have very little knowledge in DB
> > > performance side. But, I have already done some AIX tuning in
> > > aioserver and vmtune that improved some performance.
> > > Does anyone have any hint or suggestion to make in order to improve
> > > select performance?
> > > Just one more question, I know that this Oracle version is a little
> > > old, upgrading it to 8.1.7 or 9i will improve performance as well?
> > > Thank you for your attention!
> >
> > Well, you have told us virtually nothing about your environment, so all
> > I can give is a general response.
> >
> > According to IBM, 80% of a database's performance is determined by the
> > database design and the application programming.
> >
> > Of the remaining 20%, the factors affecting performance are machine
> > memory, Oracle SGA size, aio configuration, disk speed, channel speed,
> > and processor speed.
> >
> > If you want to share some more info with us, maybe we can offer more
> > practical suggestions based on your configuration.
> >
> > I would recommend upgrading to 8.1.7. There have been significant
> > improvements with how Oracle uses aio servers under AIX in the last few
> > releases.
>
> Thanks for the information!
> I will try to give you more detailed information about my environment...
> I've got a p680 12-way 600MHz with 20GB RAM, this server handles a large
> medical insurance database and it does 95% of its time read in the database.
> Previous server was an RS/6000 S7A 12-way 262MHz with 8GB RAM, and we
> noticed no better or even worst performance when we updated servers. Both
> servers are connected to the same Shark box with the same numbers of fiber
> connections. So, I doubt that would be a problem with disk I/O bottlenecks.
> Our local DBA is doing some tests in order to stress the p680 performance.
> One of these tests is to start multilpe select count(*) from tableX in
> parallel. And, we noticed that the new server performs very well in small
> number of these processes running. When we started 64 or 128 selects in
> parallel, we noticed many processes in the run-queue and a high percentage
> of CPU used by aioservers, and the performance goes down.
> I am trying to understand why aioservers are getting CPU, if I am only
> processing select requests in the database?
> Do you have any hint to improve Oracle select performance in AIX OS?
> Thank you very much for your information!
> Best regards,
> Mauro Minomizaki

There has been numerous posts in the past about AIO on AIX and various kernel parameters that needed to be modified. I'm not an AIX expert so I can't comment on them - maybe search groups.google.com and see what pops up.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."