Re: Performance With Multiple Users

The answer to your question is obvious: it takes approx 1s CPU time to query one record, so if you have many users doing the same query, it takes more time on 1 CPU machine. It's better on multi-cpu, of course. Different problem is why. I think that 100 partitions is a little bit too many for 50000000 rows. May be I'm wrong, depends on the size of the row and the size of the index. You can trace this select statement and find out what plan is used and how much cpu and elapsed time per statement.

In article <903rvo$jgp$1_at_nnrp1.deja.com>,   yitbsal_at_statcan.ca wrote:
> There is one cpu, and all users query the same table.
>
> We've tried with a multi-processor machine, and found that each
> additional user's processing time is spread across the processors. So
> the result is something like:
>
> # users, time per user
> 1, 1s
> 2, 1s
> 3, 1s
> 4, 1s
> 5, 1.25s
> 6, 1.5s
> 7, 1.75s
> 8, 1.8s
>
> Salaam
>
> In article <9015r9$deq$1_at_nnrp1.deja.com>,
> Alex Filonov <afilonov_at_pro-ns.net> wrote:
> > In article <8vmn64$gpc$1_at_nnrp1.deja.com>,
> > yitbsal_at_statcan.ca wrote:
> > > Server Specs:
> > > Sun Sparc Ultra-450
> > > 3072MB RAM
> > > 8207MB virtual memory
> > > Oracle 8i
> > >
> > > The performance of our Oracle database seems to degrade abnormally
 as
> > > the number of concurrent users increases. For example, the following
> > > query:
> > >
> > > SELECT *
> > > FROM MYTABLE
> > > WHERE MYCOL1 = 10010;
> > >
> > > runs in 1 second with 1 user, 2 seconds each for 2 users, 3 seconds
> > > each for 3 users, etc. (These are rough figures). The query is
 tuned to
> > > run in the most optimal manner, using an index.
> > >
> > > Table MYTABLE has 50,000,000 rows.
> > > It's primary key is (MYCOL1,MYCOL2).
> > > It has about a hundred partitions by MYCOL1.
> > >
> > > I realize I haven't given detailed information, but I'm not looking
 for
> > > precise answers. Can anyone speculate about why performance might
> > > degrade so rapidly with increasing number of users?
> > >
> > > Can you please also give me some idea of how your systems fare as
 the
> > > number of concurrent users increases?
> > >
> > > Thanks,
> > > Salaam Yitbarek
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Couple of questions:
> >
> > 1. How many CPUs on the machine?
> > 2. Do all users query the same table when performance degrades?
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 30 2000 - 13:57:35 CST