Re: performance problems with many clients

Hi,
There are certain questions that need to be asked. However we will try and clear somethings up. MTS:
MTS is an administration nightmare (maredrum) I have benched with 7334 and there are certain problems that are only fixed in 7343
There is a bug with regards to multi language which has proved that when we tested
this in a French environment we are the only ones in the world to ever have really benched MTS(in French)
to calculate load balancing. ( US7ASCII works fine) MTS does not give memory back - its a marketing dream back (maybe 50k per user process).
This is therefore not a wise approach in your case. Memory:
Try Shutdown (normal) of the db, to ensure that followed by a shutdown abort to clear any potential shared segments and look at how much memory you have without the database started.
Start it and then calculate it again.
Let the users attack the system and create yourself a baseline that you should note down for future tests.
Oracle memory is shared memory run "size" on the oracle executeable and you will find out the portions of
text and data. Text is therefore shared. This is then to be counted only once in your memory usage. from your ps output. So user 1 count size, user 2..n = size -text(size) add after the async processes

This will give you a memory used calculation. So you can use the rest. maybe 200MB
SGA/Shared SQL area
It appears that from your results that your SGA and shared SQL area could be rather small for your instance.
I would look at these. If you are finding that you are doing lots of sorts try looking at the sort_area_size default 64k is for pc/dos This will greatly reduce your system activity as the disk activity will be reduced.
This of course is a fine balance as it is used on a per user basis. Try 512k and eventually 1MB. Look at activity on the datafile for your temporary tablespace.
CPU
CPU states: 0.0% idle, 27.0% user, 73.0% kernel, 0.0% iowait, 0.0% swap Your users are not really using the system. It is as though there is a handbreak being applied. Your system is certainly doing something.
This is hard to say what. As the system will use CPU to prepare io operations....
A daily graph of usage would be more useful. ALERT
Look at your alert to see if there any msg like "can not complete checkpoint." Your rollbacks are either too small or you don't have enough of them. Aim for a switch every half an hour under average system load. This is a good performance and security compromise.

There could be several cumulative reasons. The alert is often a mine of information.

This is a classic operation so reasonably straight forward to sort out.

The most important item is to create a base line before you modify anything otherwise you will have nothing to show your boss. Then you won't be able to justify your pay increase.
Cheers
Peter Lomax
plomax_at_oriolecorp.com
(any other questions please contact aunt_augusta_at_oriolecorp.com) Bjorn Borud wrote:

> we have a SPARC 20 with about 433Mb of RAM running Solaris 2.5 and
> Oracle 7.3.2 that performs less than adequately.
>
> what is perhaps particular to the problem is that we have a lot of
> clients that connect, so Oracle ends up running a lot of processes.
> at times we have something like 60-70 clients connected and the system
> is extremely slow.
>
> top looks like this:
>
> last pid: 26331; load averages: 31.38, 28.19, 28.04
> 163 processes: 123 sleeping, 39 running, 1 on cpu
> CPU states: 0.0% idle, 27.0% user, 73.0% kernel, 0.0% iowait, 0.0% swap
> Memory: 433M real, 7568K free, 235M swap, 618M free swap
>
> PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
> 26317 xxxmserv 33 0 11M 2272K run 0:02 3.62% 3.92% oracle
> 26318 xxxmserv 33 0 11M 2272K run 0:02 3.60% 3.92% oracle
> 26320 xxxmserv 33 0 11M 2272K run 0:02 3.59% 3.91% oracle
> 26319 xxxmserv 33 0 11M 2272K run 0:02 3.61% 3.89% oracle
> 26321 xxxmserv 33 0 11M 2272K run 0:02 3.52% 3.87% oracle
> 26324 xxxmserv 33 0 11M 2272K run 0:02 3.52% 3.85% oracle
> 26323 xxxmserv 33 0 11M 2272K run 0:02 3.50% 3.84% oracle
> 26322 xxxmserv 33 0 11M 2272K run 0:02 3.52% 3.84% oracle
> 26329 xxxwww 33 0 11M 2272K run 0:01 2.19% 3.24% oracle
> 26145 xxxmserv 34 0 59M 6492K run 0:24 3.23% 3.23% oracle
> 26316 xxxmserv 34 0 11M 2272K run 0:02 2.61% 3.20% oracle
> 26315 xxxmserv 34 0 11M 2256K run 0:01 2.11% 2.46% oracle
> 26221 xxxmserv 34 0 11M 6516K run 0:10 2.27% 2.08% oracle
> 26276 xxxmserv 34 0 11M 5764K run 0:06 2.31% 2.08% oracle
> 26292 xxxwww 34 0 11M 2272K run 0:03 2.30% 2.08% oracle
> 26278 xxxwww 34 0 5244K 2800K run 0:03 2.28% 2.07% httpsd
> 26314 xxxmserv 34 0 11M 2256K run 0:01 1.86% 2.06% oracle
> 26313 xxxwww 34 0 11M 2256K run 0:01 2.14% 2.00% oracle
>
> as you can see this is a system administrators worst nightmare; a
> load of processes each using a bit of CPU and loads of runnable
> processes fighting for a chance to run.
>
> as I understand the Oracle installation is pretty much out-of-the box
> with a few small adjustments made.
>
> I'm not a DBA and I'm certainly no Oracle DBA, but I would like to
> find out what could be done. I have heard some people muttering about
> the possibility of a multithreaded server (does the standard Oracle
> 7.3.2 server have MT-capability?), but the documentation doesn't seem
> to say much about how this could be done.
>
> any hints as to where I should begin and what I should read are
> welcome.
>
> -Bjørn
> --
> Bjørn Borud <borud_at_guardian.no> | "The Net interprets censorship
> <URL:http://borud.priv.no/> | as damage and routes around it."
> UNIX person, one of "them" | - John Gilmore
Received on Thu Sep 03 1998 - 00:00:00 CDT