Q
Problem solve Get help with specific problems with your technologies, process and projects.

Questions about MTS tuning

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close