Oracle: TWO_TASK vs. MTS

[Ralph Grothe]

Yet another Oracle question,

The cluster that runs 14 Oracle instances as packages that I have to look after is a real performance hog.

In an introductory book on Oracle (I have to train myself the basics since I'm not supplied with sufficient information from the customer's DBAs nor developers) I read somewhere - which sounds absolutely plausible to me - that if you are after performant client servicings, you better run the db server as a multi threaded server (MTS).
When I have a look at the Oracle sessions by sqlplus it says that all connections are dedicated

SQL> select server from v$session;
...
DEDICATED

SERVER
---------
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED

140 rows selected.


This seems to me - after what I've read in the book - to be the real reason for the lousy performance the customer complains about.

Do you think we will get better performance if we reconfigure the instances to run as MTSs?

[Thierry Poels_1]

Hi,

MTS is only a real benefit if you have many user connections with very little activity (e.g. a web server).
MTS saves on memory usage, but might even have a negative impact on performance.

To speed things up adding memory and assign it wisely to db_buffers might do wonders. Oracle generally requires lots of memory and cpu power for top performance.

regards,
Thierry.

[Volker Borowski · Accepted solution]

Hello Ralph,

it depends on your application. If you have an application-server layer in between, that does the user multiplexing, use of MTS would be more expensive, because you would have all the stuff belonging to a user context switch on more than one level.

If your client has an individual connection to the database, than it might be more expensive to swap an entire dedicated server process out to disk, instead of just rolling user-context in/out of a MTS server process.

So you should check in v$session for
SERVER
USERNAME
MACHINE (or was it TERMINAL ?)

Now if MACHINE and USERNAME look the same for most of the sessions, it looks like you run an application server, which would suggest to stay dedicated first, and dig a bit deeper on application architecture.

If you have distinct USERNAME and/or MACHINE values, it is likely, that MTS could be a benefit ... if each user has a reasonable THINKTIME in which there is NO activity.
If you have a mixture, you might need to configure both. An MTS pool for many users with a high THINKTIME and some dedicated server processes for special actions, like jobsessions, administration, power-users...

Hope this helps
Volker

[Bill Thorsteinson]

Look at IO by disk and cpu utilization. If CPU is maxed
out, then the servers do not
have sufficient capacity for
the database as running.

If disk IO is maxed out, then
the database may not have
sufficient block buffers. If
only certain disks are maxed
out, then the IO distribution
is likely incorrect.

Look at the swapping activity.
It should be low. If it is
high the database may have
too much memory allocated.
This lease to thrashing of the
database, where shared memory
must be paged in to release or
reuse a block buffer.
You may need more memory on
the servers, or to cut back
the memory allocated to the
database. Look at the size
of the processe from
ps -fl | grep ora_smon
This should be greatert than 2000. Look at the init*.ora
files in $ORACLE_HOME/dbs.
The uncommented lines should
be the ones for large.
Block buffers should be further incremented by factor
of 5-100 depending on available memory.

Be aware that each database instance has a (hopefully)
quite large shared memory
segment allocated to all
processes. This skews the
reported.

Look at the alert.log files.
If you have waits for redo
logs then the redo logs are
likely so small or too few.

I hope this gets you started.

[Ralph Grothe]

Thierry, Volker, Bill,

thank you for your suggestions.

I have to apologize for coming back so late to this thread.
But again it wasn't my blame, as the Forum's webserver (or the one my requests were sent to by the Forum webservers' dispatcher) hasn't been available to me during office hours here in Germany (always timouts, or partly loaded pages with no contents).
Looks to me as if the webservers or the dispatcher also urgently need a performance boost (eyh site maintainers, how about a threading webserver, I hear Apache 2.X is threading capable ;-)