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.


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
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.