| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle performance issue
From the sounds of it...I would bet your High Water Mark on the table(s) on
which you are performing the select Count(*) are way too high...What this
means is that significant Inserts and Deletes have occured on the tables in
question
and you should rebuild them...Either export and re-import the data or try
the rebuild command from oracle...The machine seems to be a bomb and
should respond instantaneously to a select count(*) regardless of the size
of the table...Or you have an incredible high number of extents on both the
table
and the related indexed for the table.
Hope this helps
Greg Vitetzakis V Greg Vitetzakis V.P. Professional Services OSTnet (Open
Source Technologies Inc.) www.ostnet.com
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:3BBB8AA9.3A34_at_yahoo.com...
> 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
>
>
>
 |
 |