Re: Oracle performance issue

From: Kevin Brand <unixoracle_at_hotmail.com>
Date: Wed, 03 Oct 2001 19:32:25 GMT
Message-ID: <dJJu7.72375$707.37473814@news2.rdc2.tx.home.com>

I'm wondering what situation the DBA is attempting to model with this test?

Is he running 64 to 128 "select count(*) from" statements against the same table? Are there any where clauses on these select statements?

You must be running on cooked files if you are using AIO, so depending on the answers above, you may be saturating a small chunk of your overall filesystem size with a massive number of sequential reads ( and read-aheads ). Since this area would quickly become cached in system memory ( persistent memory blocks for files in AIX ), you would ( potentially ) not *see* much I/O at the disk level.

I would think the high run queue and subsequent CPU usage, while justified given the number of active, CPU-bound processes you are exercising here, would contain more of a closer-to-equal mix of both Oracle host processes and the aioservers.

Hth

-Kevin

"Mauro Minomizaki" <mauro_m_at_terra.com.br> wrote in message news:3bba826a_at_news.terra.com.br...
> "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

>
>
>
>

Received on Wed Oct 03 2001 - 14:32:25 CDT