Oracle MTS (Multithreaded Server)


What is MTS ?

Without MTS a dedicated server process is assigned to each client connection request. When a connection is idle the dedicated server process is idle, wasting resources. MTS will allow sharing of server processes between client connections using dispatcher and shared server processes. When a shared server process is idle it is allocated automatically to an active client connection. Thus server processes are shared between multiple client connections. Client connections share server processes by way of the server process selecting an active client connection from a queue of requests waiting for service. The management of communication between client connections and shared server processes is performed by dispatcher processes. A dispatcher process is the target of a client connection request and is responsible for redirecting a client connection to an idle or currently unused shared server process.

MTS effectively overrides dedicated server connections with shared connections. MTS works such that dispatcher processes are used by multiple client requests to communicate with shared server processes. Thus the client communicates with one of many dispatcher processes rather than directly with a dedicated server process, thus database connections can effectively be shared amongst different users where users share pre-configured connections. The best use of dispatchers is when users spend a large amount of connection time idle, ie. they are not utilising a preserved set of database connection resources. When one client is not utilising a connection then another client can utilise the database connection thus effectively sharing connections. When a user requests a connection the listener process will reply to the user connection request with the memory address pointer of the least busy dispatcher, ie. the dispatcher with teh shortest request queue. The dispatcher process will maintain a queue for user processes to place their requests on. Dispatchers are not appropriate where multiple users are executing intensive processing for long periods of time. Client processes requiring large data load connections are best served with dedicated server connections. MTS processes can be stopped for periods of time or specific client connections can be served exclusively by dedicated connections whilst other clients continue to be serviced by shared server-dispatcher serviced connections.

With MTS the listener will pass a client process to the least busy dispatcher process. The dispatcher will maintain both the request and response queues. The dispatcher places client requests onto the request queue where shared server processes retrieve those requests. When the shared server has completed the request the shared server process will place the results onto the reponse queue. The dispatcher process will then read the response queue and pass the result back to the client connection. MTS will increase usage of the shared pool as a result thus the SGA must be increased when using MTS.

SQL*LDR or import data uploads or backups are best serviced by dedicated connections. A Recovery Manager (RMAN) database connection can only be serviced by a dedicated connection (Set the attribute SERVER = DEDICATED in the tnsnames.ora file). Client processes can be forced to always use a dispatcher process when connecting to a database by setting the attribute SERVER = SHARED in the tnsnames.ora file.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = TEST) (SERVER = SHARED))
  )

RMAN =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = RMAN) (SERVER = DEDICATED))
  )

MTS Parameters and Configuration

mts_dispatchers

The address, description and protocol are mandatory. The mts_dispatchers can be set up multiple times in the parameters file. Effectively multiple dispatchers can listen for client requests on multiple protocols and / or multiple ports on a single protocol. Obviously those differring dispatcher configurations can have different numbers of dispatchers. The more dispatchers there are available then the better database performance for large numbers of users becomes.

mts_dispatchers="(address=(protocol=tcp)(host=<ip_address>)(port=1521))"
mts_dispatchers="(address=(protocol=tcp)(host=<ip_address>)(port=1522))"

Optional mts_dispatchers Attributes

How Many Dispatchers ?

When setting the mts_dispatchers parameter, if not enough dispatchers are started automatically on instance startup, there will be an immediate performance hit starting up more dispatchers in order to cater for the required number of concurrent client connections. The number of dispatchers is generally dependant on the operating system, network protocol requirements and performance required. More dispatchers will decrease resources used by each user but will also decrease performance for each user. For NT there should be 1 dispatcher for every 1000 connections. Calculate the number of dispatchers required by rounding-up the division of maximum number of concurrent sessions for the operating system by connections per dispatcher.

Connection Pooling

Connection pooling can only be enabled when using MTS. The sharing of a dispatcher process' set of connections must be enabled to connect to many client processes. With dispatcher connection pooling physical connections are maximised to multi-threaded server processes. A timeout mechanism can also be utilised to release idle connections for use by other client processes. Enable connection pooling by setting the POOL = ON attribute in the MTS_DISPATCHERS parameter in the parameters file. The CONNECTIONS, SESSIONS and TICKS attributes can also be used to control connection pooling.

MTS_DISPATCHERS = "(PROTOCOL = TCP) (DISPATCHERS = 1) (POOL = ON) (TICK = 1) (CONNECTIONS = 1000) (SESSIONS = 500)"
MTS_DISPATCHERS = "(PROTOCOL = TCP) (DISPATCHERS = 1) (POOL = ON) (TICK = 1) (CONNECTIONS = 750)"

Control of MTS for and from the Client

MTS configuration can be controlled with multiple dispatcher parameter definitions, from the server, in the parameter file. Thus different clients requesting different services can be directed to connect to the server through different dispatcher configurations, ie. different dispatchers based on the service requested by the client.

service_names = accounts.xyz.com
instance_name = accounts
mts_dispatchers="(address=(protocol=tcp)(dispatchers=1))"
mts_dispatchers="(address=(protocol=tcp)(service=nyaccounts.xyz.com)(dispatchers=2))"

Thus in the example above all clients connecting to service=nyaccounts.xyz.com will use two dispatchers. All others will use a single dispatcher process.

Also from the tnsnames.ora file on the client node MTS can be completely overridden by setting the CONNECT_DATA SERVER attribute to DEDICATED. This forces a client connection to connect to the server using a dedicated server process rather than a shared server process.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = TEST) (SERVER = SHARED))
  )

RMAN =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = RMAN) (SERVER = DEDICATED))
  )

Setting use_dedicated_server=on in the sqlnet.ora general connection profiling configuration file will force all client connections from the client node to connect to the server with a dedicated server process on the server. This option could be utilised for a node performing batch functions such as reporting and backups.

Supporting Large Networks

In general, large networking environments with high numbers of concurrent connections can be supported with Oracle Multi-Threaded Server (MTS). The objective of MTS is preservation of server resources such as memory and processor usage time. MTS can effectively share connections between multiple users by use of dispatchers and shared server processors in place of dedicated server processes. Dispatcher processes communicate between dedicated server processes and client processes therebye shared server connections where dispatchers support multiple users and multiple dispatchers are supported by each server process. Additionally, when using MTS the UGA (User Global Area) can be moved into the large pool and thus not compete with the database buffers for memory resources in the shared pool. The UGA contains session information, sort areas and private SQL areas. Since connections (sessions) are placed into the large pool, sort space is also placed into the large pool. Sort space can occupy a large amount of memory since each connection will reserve an amount of memory specified by the sort_area_size parameter in the parameter file.

MTS is enabled by the setting of the mts_dispatchers parameters in the example parameter file extracts as shown below.

mts_dispatchers=(address=(<protocol>)(<hostname>)(<port>)(<# dispatchers>)(<# connections>))"
mts_max_dispatchers=<max # dispatchers>
mts_servers=<# shared servers on startup>
mts_max_servers=<max # shared servers>
mts_service=<SID | service_names parameter in parameter file>
mts_listener_address=<TNS listener address>
mts_dispatchers="(address=(protocol=tcp)(host=<host>)(port=5001)(dispatchers=2))"
mts_max_dispatchers=2
mts_servers=2
mts_max_servers=4
mts_service=TEST
mts_listener_address="(address=(protocol=tcp)(host=<host>)(port=1521))"

When setting the mts_dispatchers parameter, if not enough dispatchers are started automatically on instance startup, there will be an immediate performance hit starting up more dispatchers in order to cater for the required number of concurrent client connections. The number of dispatchers is generally dependant on the operating system, network protocol requirements and performance required. More dispatchers will decrease resources used by each user but will also decrease performance for each user. For NT there should be 1 dispatcher for every 1000 connections. Calculate the number of dispatchers required by rounding-up the division of maximum number of concurrent sessions for the operating system by connections per dispatcher.

To force a user to always use a dispatcher set the server=SHARED attribute in the tnsnames.ora file. Otherwise set the server=DEDICATED attribute in the tnsnames.ora file.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = TEST) (SERVER = SHARED))
  )

RMAN =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = RMAN) (SERVER = DEDICATED))
  )

Also note that dispatchers can be configured in the parameters file to pass to any port at an IP-Address or tied-in to a specific port at an IP-Address.

mts_dispatchers="(address=(protocol=tcp)(host=<ip_address>)(dispatchers=2))"

Note that when specifiying multiple ports that those ports must be adjacent.

mts_dispatchers="(address=(protocol=tcp)(host=<ip_address>)(port=6200)(dispatchers=1))"
mts_dispatchers="(address=(protocol=tcp)(host=<ip_address>)(port=6201)(dispatchers=1))"