Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle MTS - The Multithreaded Shared Servers

Oracle Tips by Burleson Consulting 

 

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 2009, 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:

  1. 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.
  2. 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

 


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.