Oracle MTS

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 s

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.

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.