Re: Performance With Multiple Users

In our last gripping episode Alex Filonov <afilonov_at_pro-ns.net> wrote:
> 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.
>

Yours is one possible answer.

Has the shared pool been checked when this activity is occurring? As users connect to the database and query tables the available shared pool resources diminish. Possibly your shared pool is too small to handle the user load (although with only 8 users accessing the instance I cannot imagine this to be a serious problem). If the users are not local to the server then Net8 issues arise; are you using MTS? If not then the Net8 memory requirements are being taken from the shared pool contributing to a decrease in available shared pool memory. If you are using MTS you should have your large pool configured to provide a separate memory area for MTS connections; this will free up shared pool resources so that they are available for non-network processes.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.