Before implementing MTS (Multi-Threaded Server), there are several questions you need to answer:
- What protocols do you want to support?
- How many dispatchers do you want to have?
- How many shared server processes do you want Oracle to initially create whenever you start the instance?
- What is the maximum number of shared server processes that you want to allow?
- Do you still need to provide for some dedicated server connections?
- How are you going to handle the extra memory demands of MTS?
The last item, regarding memory, can sneak up on you. When dedicated server connections are used, session-specific memory is contained in the memory area of each dedicated server process. With MTS, session-specific memory ends up in either the shared pool or the large pool. When you implement MTS, you may need to increase your pool sizes to accommodate the extra demand for memory.
What Protocols to Support?
You can decide this based on the protocols in use at your site. You do not need to implement MTS for every protocol that you use. If you are supporting 500 TCP/IP users and only 5 SPX users, you may find that there's no real benefit to be gained by moving those 5 SPX users to MTS. The big gain would probably come from moving those 500 TCP/IP users to MTS.
Another thing to think about here is whether or not you are supporting Common Object Request Broker Architecture (CORBA) connections to your database. CORBA is sometimes used to support object-oriented development. If you are supporting CORBA, then you'll need separate MTS_DISPATCHERS entries in your instance parameter file to define dispatchers that support the session and presentation layers used for CORBA connections.
How Many Dispatchers?
Dispatcher processes handle the flow of requests and results between database clients and the shared server processes. At a minimum, you need to have one dispatcher for each protocol that you support. However, there are several reasons why you might want or need more than that.
One factor affecting the number of dispatchers you may need to create is your operating system's limit on the number of connections that can be made to a single process. If your operating system allows a maximum of 254 connections to one process, and you want to support 600 TCP/IP connections using MTS, then you'll need three dispatcher processes for the TCP/IP protocol. Take the number of connections that you want to support, divide that by the maximum number of connections per process, and round the result up to the next integer value. Click Here for what to do if you don’t know the connection limit.
Performance is another factor affecting the number of dispatchers that you might want to have. Dispatchers need to be able to keep up with the rate at which clients make requests, and the rate at which results need to be sent back as a result of those requests. If your clients are busy, you may need to create more dispatcher processes in order to distribute the load. The later section titled "Changing the Number of Dispatchers" talks more about this issue. A reasonable course of action when you first configure MTS is to base the number of dispatchers on the number of connections that you need to handle. You can then monitor how busy they are and make adjustments later if necessary.
How Many Shared Server Processes?
Two initialization parameters, MTS_SERVERS and MTS_MAX_SERVERS, allow you to specify the minimum and maximum number of shared server processes that you want to create for an instance:
- MTS_MAX_SERVERS Sets an upper limit on the number of shared server processes that can be created. Choose a value for this based on the total amount of resources you are willing to dedicate to server processes.
- MTS_SERVERS Specifies the number of servers to initially create. This defaults to 1. You can leave it at that, or you can set it to a higher value that reflects the typical load on your system. Ideally, you want to minimize the rate at which shared server processes are created and destroyed.
Providing for Dedicated Server Connections
When implementing MTS, you may want to provide for a limited number of dedicated server connections. Not all types of connections benefit from MTS, and some tools and operations require a dedicated connection. In particular, you need dedicated connections for the following:
- Connections that have a high degree of utilization. A good example of this is a batch job that pulls down and processes a large amount of data. Such jobs run better over a dedicated connection.
- SQL*Plus or Server Manager connections when those tools are used to start or stop an instance.
- Recovery Manager (RMAN) connections.
Usually, the best way to provide for a dedicated connection is to create a separate net service name for that purpose. The use of MTS can be overridden in a net service name definition.
How Much Memory?
When you implement MTS, you need to have some idea of how much shared pool or large pool memory is required for use as session-specific memory. One way to do that is to look at the current amount of memory used by a representative session, and then extrapolate from that to cover the number of sessions you expect to have connected simultaneously. The query in the following example retrieves information about the amount of session memory used by the currently logged in user named GNIS:
SQL> --First, get the SID for the user named GNIS. SQL> SELECT SID 2 FROM v$session 3 WHERE username='GNIS'; SID ---------- 11 SQL> --Next, check on the GNIS user's session memory usage. SQL> SELECT sn.name, ss.value 2 FROM v$sesstat ss, v$statname sn 3 WHERE ss.statistic# = sn.statistic# 4 AND sn.name IN ('session uga memory', 'session uga memory max') 5 AND ss.sid=11; NAME VALUE ------------------------ ---------- session uga memory 51568 session uga memory max 61300
The session uga memory value represents the amount of memory that a user is currently using for session-specific purposes. The session uga memory max value represents the maximum amount of session-specific memory that has been used at any one time during the user's current session. If this user was representative of your other users, and you expected to have 500 such connections concurrently, you could multiply both values by 500 to come up with an estimated range of 25,784,000 to 30,650,000 bytes. This is the amount of additional memory that you need to allocate in the SGA for use by MTS sessions. By default, MTS uses the shared pool for session memory, but Oracle recommends the use of the large pool. By using the large pool, you avoid fragmenting the shared pool. With respect to this example, you might add the following to your instance parameter file:
LARGE_POOL_SIZE = 32M
This allocates a large pool of 32 megabytes in size, somewhat larger than the high end of our estimated range for session memory. Allocating a large pool is all you need to do in order to have Oracle use it for MTS session memory. MTS session memory comes out of the shared pool only if there is no large pool at all.