Re: Users per MTS Ora 9

"B Fisher" <none_at_antispamming.com> wrote in message news:Zm6La.146449$Xl.2298132_at_twister.rdc-kc.rr.com...
> We are experiencing intermittent performance problems with our Oracle 9.2
> database. Usually fine then extremely slow (unusable). We have done
> statspack, OEM. Running on AIX box. Monitoring (nmon64) shows the
machine
> is falling asleep (cpu has never hit 80% and usually around 20%, paging
> fine, SCSI fine, disk nothing). Hit rates are > 99% with giant SGA. We
> seemed to do better on slower machine with Oracle 8.1.7.
>
> Turns out that we have MTS with 1 server. The Oracle 8 has MTS with
> server=0 thus lots of processes.

If I understand your post right, that means you don't have MTS at all. With no shared server processes, and unless you specifically state (SERVER=SHARED) in your tnsnames.ora, then each user gets a dedicated server process. No wonder the 8.1.7 database performed so well!

> At the moment we only have 120 users going
> to about 500. Could we have a problem with MTS?

Well, I just don't understand how you can configure MTS with zero shared server processes. So yes... you do have a problem with MTS, and that problem would appear to be that you don't know how to configure it properly.

>Any ideas on number of
> users per MTS (guideline).

42.

It's as good a number as any.

(In other words: come off it! It all depends, and that's what tuning is all about).

As a clue, try checking out the documentation for measuring dispatcher contention and the shared server busy rate.

>We have some large SQL/long running (ERP so we
> can't change them).

If you have lots of jobs that last longer than about 3 seconds, then you shouldn't be using MTS. Long jobs means the job hijacks the shared server process for a long time... which means fewer shared server processes for other users. MTS is only really of benefit in an environment where most of your server process time is spent idle, waiting for a user to do the next piece of SQL. If you have long-running SQL, then your server processes are spending most of their time doing long-running SQL!

~QM

>Clearly, some slowdown is when people get in at about
> the same time - no surprise, but even then the cpu is low, disk, scsi low.
>
> Any experience with this?
> Thanks
>
> scott fisher
>
>
Received on Fri Jun 27 2003 - 23:32:03 CDT