We have a database with over 1200 connections, and we recently moved from a dedicated server to MTS in order to preserve process resources on the server. We had 10 dispatchers and 10 initial shared servers. The immediate effect of moving to MTS was that the dispatchers became too busy, with each one consuming about 8% to 9% of CPU, overwhelming the server. So we increased the dispatchers to 40 to reduce contention of dispatchers, and we were successful. The dispatchers now use less then 0.5% CPU, and thus the total use is less then 15-20% of CPU for all 40 dispatchers.
The problem we are facing now is that the contention has now moved down to the shared server processes. The shared servers are each now consuming lot of CPU and are thereby overwhelming the server. We allowed more servers as determined by the server terminations/creations, but we observed that the first few server processes are always kept busy, with the other servers doing much less work. That is, the work load is not getting balanced over the shared servers.
For starters, I always question a user of MTS to make sure that it is required. The MTS was developed back in the 1990's when RAM was very expensive, and the primary benefit is to reduce demands on RAM. Now that RAM is cheap, you may be better off using a 64-bit server using a larger pga_aggregate_target instead. Have you tried less dispatchers with more processes? Having 40 might be excessive.
Anyway, MTS tuning is complicated, and there are many scripts that you can run to get details on MTS bottlenecks and adjust the number and size of each dispatcher. I would recommended going to Mike Ault's code depot at www.oracle-script.com and try out his MTS diagnostic scripts.
Also, Kim Floss (President of the IOUG) has a new book with a whole sction on MTS tuning that you might find valuable:http://www.rampant-books.com/book_2004_1_oi_sql.htm
Dig Deeper on Oracle database performance problems and tuning
Related Q&A from Don Burleson
I have created a table, Party, and in it there are two main fields, party_code and party_name, and around 2,500 records are available in it. I passed... Continue Reading
Suppose you have a table which has only two columns, namely locid (not primary) and place. In Oracle, which query should I run to get the output as ... Continue Reading
I have a query that retrieves around 10 records. I have a task to concatenate the values separated with ','. I would like to know if Oracle has any ... Continue Reading