The Oracle multithreaded server (MTS, later
renamed "shared servers") was developed way-back in
the days of Oracle7 when RAM was very expensive and
RAM region sizes were severely limited by 32-bit
technology.
However, the MTS is still needed for
Java connections and
shared_servers=1 is default because internal
Java (and others) require a shared server to access
the database.
Oracle
shared servers are also inappropriate for ERP
packages that
spawn and hold database connections within the
application server layer.
Bobby Durrett notes that shared servers make
sense in rare cases where you have a farm of web
servers connecting to your Oracle database, a case
where connection pooling makes sense.
"This weekend we had the most
convincing evidence that our change from dedicated
to shared servers on a database that supports a farm
of web servers was the right move. We have had some
outages on the weekend caused by a sudden burst in
web server generated database activity. In the past
the CPU load would spike and log file sync (commit)
waits would be 20 times slower and we would have to
bounce the database and web servers to recover.
Sunday we had a similar spike in database activity
without having any sort of outage."
There are also limitations to using the MTS.
The Oracle 10g documentation indicates that
session migration for BFILE datatypes in shared
server (multithreaded server) mode is not supported.
Warnings about the MTS
As of 2015, I see very few Oracle shops
(well under 5%) who continue to use the MTS (unless
they have Oracle Java connections, which require MTS
Shared Servers), and it
is largely obsolete for today's 64-bit servers with
lots of inexpensive RAM resources.
Quest Software's Guy Harrison has
this warning
about using the MTS:
"MTS becomes downright dangerous
when Automatic Shared Memory Management (ASMM) or
Automatic Memory Management (AMM) is in place.
When you use MTS and AMM (or
ASMM) together, PL/SQL programs that try to create
large collections can effectively consume all
available server memory with disastrous consequences
. .
AMM allocates virtually all memory
on the system to the large pool in order to
accommodate the PL/SQL memory request. First it
consumes the buffer cache, then it reduces the
PGA_AGGREGATE_TARGET - all the way to zero!"
Oracle's
Tom Kyte notes that the MTS should not be used
without a "real reason" and he notes that shared
server connections are slower than with dedicated
database connections (Oracle's default behavior):
- "Unless you have a real reason to use MTS
-- don't."
- "a shared server connection is by design
"slower" than a dedicated server (more stuff
goes on, more complex) it is most likely only
getting in the way."
Mr. Kyte also offers some rules-of-thumb for
shared server use and configuration:
"In the golden age of client server way
back when -- you might have 100 users connected
to the database from 9-5, but at any point in
time, only 5 or 6 of them were actually ACTIVE.
In this case, 5 or 6 shared servers would be
perfect."
"if you were cpu starved and context
switching like mad -- the dedicated server
connection could be running at a slow speed
itself, slower than the extra work that shared
server would impose. therefore, lightening up
the load on the server could be beneficial...."
My experience concurs that Oracle shared servers
should not be used without a compelling reasons
(i.e. super high connect/disconnect rates on an
instance with limited resources) and that the vast
majority of Oracle databases will run more
efficiently without shared servers. Dedicated server
connects are far faster than multi-threaded server
connections, and 64-bit Oracle combined with the low
cost of RAM has driven-down the rare cases where
shared servers are justified.
When should I consider using
the MTS?
Of course, your mileage may vary, but most Oracle
experts agree that the MTS is only used in very rare
cases, primarily for instances with resource
limitations or a high "think time". Let's take
a closer look at the rules-of-thumb for using Oracle
shared servers. In
this case study by Brian Keating we see that his
case study results do not recommend using the
multi-threaded server (MTS) if there is sufficient
RAM on the Oracle server:
If there is enough physical memory on the
server so that all of the concurrent processes
(operating system, Oracle, user connections,
applications, etc.) during peak periods can fit
into physical memory, then don't bother
implementing MTS. There are two reasons for
this:
- As long as you do not use more than
100% of the physical memory on a server, the
operating system will not have to page or
swap. If that's the case, there will not be
any pressing need to conserve memory. In
other words, reducing a system's memory
usage from 80% of physical memory to 20% of
physical memory will not really buy you
anything—it will simply mean that more of
your physical memory will be idle most of
the time. So, unless you have at least some
virtual memory usage, I would not recommend
going through the additional complexity of
MTS.
- More importantly, MTS appears to
cause some additional CPU overhead as
compared to running an equivalent number of
dedicated connections. Therefore, if a
system is CPU-bound, then MTS will probably
not be a good option for that system.
Keating also notes cases where using the
multithreaded server (MTS can cause a degradation in
performance:
I encountered one situation in which a
database server's CPUs were constantly pegged at
100% usage, and the CPU queue length (the number
of processes waiting for CPU time) was typically
6 or 7 during peak periods. That database had
been using MTS for several years, even though
there was more than enough physical memory on
the system to support dedicated connections. So,
in an attempt to reduce the system's CPU usage,
I disabled MTS. The average CPU queue length
during peak periods went down to about 4.
Moral of the story: If a system is
CPU-bound and that system has enough RAM to fit
all of its processes into physical memory, then
MTS should probably not be used on that system.
In shared server architecture, the listener
assigns each new client session to one of the
dispatchers. As the user makes requests, the
dispatcher sends the request to the shared server.
It is also possible that a different set of shared
servers are utilized for a given user session. The
dispatchers act as the coordinating agents between
the user sessions and the shared servers.
A dispatcher is capable of supporting multiple
client connections concurrently. Each client
connection is bound to a virtual circuit. A virtual
circuit is a piece of shared memory used by the
dispatcher for the client connection requests and
replies.
An idle shared server process picks up the
virtual circuit from the common queue, services the
request, and relinquishes the virtual circuit before
attempting to retrieve another virtual circuit from
the common queue. In this way, a small number of
server processes are able to service a large number
of clients or users. This method also supports an
increased number of users with less system
resources.
Note that not all applications are certified to
use shared servers, but that server-side load
balancing in a RAC may benefit from using shared
servers.
As seen in Figure 9.5, the listener communicates
with the dispatchers on behalf of the user or client
sessions. Once the user sessions establish
connectivity with dispatchers, the shared servers
service them.
Prior to the release of Oracle Database 10g, you
needed to set up at least one dispatcher for the
shared server configuration to be enabled. You
normally needed to set the dispatchers
initialization parameter to configure the
information about dispatchers.
With Oracle Database 10g, even without specifying
a dispatcher with the dispatchers parameter, you can
enable shared server by setting shared_servers to a
nonzero value. The default behavior is that Oracle
creates one dispatcher for the TCP protocol
automatically. This way, it is easier to configure a
shared server environment.
The equivalent dispatchers initialization
parameter for this configuration would be:
DISPATCHERS="(PROTOCOL=tcp)"
When you need to use shared servers while the
system is running, you can simply set the dynamic
shared_servers initialization parameter to a value
greater than zero with an ALTER SYSTEM command.
As with other parameters, you can change just the
current instance with this command and, if you are
using an SPFILE, you can change the parameter for
future instances as well. For example, to activate
three shared servers in the current instance and the
SPFILE, enter this command:
SQL> ALTER SYSTEM SET SHARED_SERVERS=3
SCOPE=BOTH;
There are several other parameters that can be
set in the shared server environment, but they are
not required. Once you set shared_servers, your
system will be running in shared server mode.
Parameters with the prefix MTS are now obsolete.
This means if you try to start an instance using
these parameters you will receive the following
error: "ORA-25138: <parameter> initialization
parameter has been made obsolete "
Even if you try to set mts_servers during the
runtime of an instance:
SQL> ALTER SYSTEM SET MTS_SERVERS = 2;
ALTER SYSTEM SET MTS_SERVERS = 2
* ERROR at line 1:
ORA-25138: MTS_SERVERS initialization
parameter has been made obsolete
the replacement parameters
listed in the table are dynamic, meaning that you
can change the values while the instance is running.
Table 9.1 shows the replaced parameters.
All the
replacement parameters listed in the table are
dynamic, meaning that you can change the values
while the instance is running. Table 9.1 shows
the replaced parameters.
OBSOLETE PARAMETER
|
REPLACED BY PARAMETER
|
mts_servers
|
shared_servers
|
mts_max_servers
|
max_shared_servers
|
mts_dispatchers
|
dispatchers
|
mts_max-dispatchers
|
max_dispatchers
|
mts_circuits
|
circuits
|
mts_sessions
|
shared_server_sessions
|
mts_listener_address
mts_multiple_listeners
|
local_listener
|
Table 9.1 Oracle 10g Replacement Parameters
In the case of the dispatchers parameter, the
results of the change will depend on which
attributes you modify. Since several of the
attributes affect the network session layer when a
dispatcher is started, they cannot be changed for
dispatchers already started. These attributes are:
protocol, address, description, presentation,
connections, sessions, ticks, and multiplex.
You can dynamically modify the other attributes
(listener and service) and affect existing as well
as new dispatchers of the same configuration.
There is a new 10g view, v$dispatcher_config,
that shows more information about existing
dispatchers. This view displays information about
the dispatcher configurations, including attributes
that were not specified and were given a default
value. The column CONF_INDX in v$dispatcher_config
can be joined to the conf_indx column
in"v$dispatcher to see all of the detailed
information about a given dispatcher. This
information helps you to make more informed
decisions on what attributes to modify and helps
determine if you need to add or remove dispatchers.
For example, to get service and other details
about dispatchers, use the following query:
SQL> select name, dispatchers, substr(service,1,20)
service, idle, busy
from v$dispatcher,v$dispatcher_config where SEE
CODE DEPOT
NAME DISPATCHERS SERVICE IDLE
BUSY
---- ----------- ----------- ---------- --------
D000
1 LONDBXDB 1641097 8
|