Introduction to Oracle MTS

 

MTS (multi-threaded server) is an optional configuration option for Oracle server. Compared with the dedicate method, MTS has the biggest advantage of not increasing physical resources (memory) supports more concurrent connections. In other words, if you only have 2 GB of physical memory and you want to support 2000 connections, you should select MTS for the best performance.

CompanyDatabaseThe server load is large. pgsp has used more than 66%

After counting the process, make sure that the root cause isOracleToo many processes, that is, too many dedicated connections.

Change the user connection mode to multithread mode.

 

View the Oracle configuration. MTS configuration is enabled by default. V $ dispatcher. The number of records in V $ shared_server is 1.

 

However, when the client tnsnames. ora changes (Server = dedicated) to (Server = shared) and then connects to the database, an error is returned:

ORA-12519:

Let's look back at the server configuration parameter show parameter dispatchers

Mts_dispatchers = (Protocol = TCP) (Service = devaxdb)

Check the listener. No service devaxdb is found in LSNRCTL status.

Metalink account expired, so I had to go crazy to Google. No similar solutions were found

Alter system register;

Later...

Modified listener. ora
(Address_list =
(Address = (Protocol = TCP) (host = shdemo1) (Port = 1521 ))
)
The IP address in is changed to the host name.

Restart lsnr, OK

As a memo, you can extract an MTS-related article for your convenience.

I. Introduction
　　
MTS (multi-threaded server) is an optional configuration option for Oracle server. Compared with the dedicate method, MTS has the biggest advantage of not increasing physical resources (memory) supports more concurrent connections. In other words, if you only have 2 GB of physical memory and you want to support 2000 connections, you should select MTS for the best performance.
　　
This article first describes how MTS works, and then compares it with the dedicate method. Next we will discuss the specific configuration Implementation of MTS, and finally some questions about how to optimize MTS configuration options.
　　
Ii. How MTS works
　　
1. Joseph C. Johnson gave an image of MTS in a restaurant.
　　
Suppose Oracle is a restaurant. When you walk into a restaurant, the most comfortable service you feel is to have a dedicated waiter to serve you, no matter how many people come in the restaurant, she only responds to your request. This is the dedicte processing method. That is to say, each Oracle client connection has a dedicated service process to serve it. Most of the restaurants are not one-to-one. When you enter, you are assigned a waiter, which may also serve other tables, this is the most advantageous for restaurants because they can serve more guests without increasing their staff. This may also be good for you. If the restaurant is not too busy, the requests from the guests she serves will be brief and easy to complete, you feel like you have a dedicated waiter. Waiter transfers your order to the cook and then delivers the prepared food to you. This is the processing method of MTS, the shared waiters are called dispatchers, while the chefs call them shared server processes.
　　
2. Briefly describe how MTS works (a figure in the sybex book)
　　
1) the client sends a service request to dispatcher.
　　
2) Dispatch puts this request in the request-to-queue of the SGA Region
　　
3) one or several service processes process the request.
　　
4) The service process places the result in the response queue in the SGA area of the dispatch.
　　
5) The dispatcher picks up the result from the response queue.
　　
6) Complete the client request and send the result back to the client
　　
Iii. Comparison between MTS and dedicate. To facilitate comparison, draw the following simple table:
　　
Serial number
　　
Comparison item
　　
MTS Mode
　　
Dedicate Method
　　
1
　　
Service Process
　　
Multiple connections share a service process
　　
A connection has a dedicated service process.
　　
2
　　
Memory usage for each client connection
　　
3-4 m
　　
150-200 K
　　
3
　　
Suitable application environment
　　
Suitable for OLTP environments with many connections and few requests
　　
If the Oracle server resources are sufficient, this method is preferred.
　　
4
　　
CPU load
　　
It may cause some CPU load. If your CPU has a bottleneck, do not use this method.

Iv. MTS configuration implementation
　　
1. Common Parameters in the Oracle8i MTS Environment
　　
Serial number
　　
Parameters
　　
Description
　　
1
　　
Mts_dispatchers
　　
It is used to configure the number of dispatcher enabled when the instance is started, and the protocol that the dispatcher responds to. It is a dynamic parameter and can be modified dynamically using alter system. It has no default value.
　　
2
　　
Mts_max_dispatchers
　　
It is used to specify the maximum number of dispatcher processes running at the same time. For most applications, enabling a dispatcher for every 250 connections can achieve better performance. The default value is 5 or the number of dispatcher configurations.
　　
3
　　
Mts_servers
　　
This parameter is used to specify the number of service processes that you want to enable when an instance is started. It is a dynamic parameter and can be modified dynamically using alter condition me.
　　
4
　　
Mts_max_servers
　　
This parameter is used to specify the number of service processes for shared libraries at the same time. If your system often suffers deadlocks, add this value as appropriate.
　　
5
　　
Mts_service
　　
Set as Sid
　　
6
　　
Mts_listener_address
　　
TNS Listener address
　　
2. Common Parameters in the Oracle9i MTS Environment
　　
Serial number
　　
Parameters
　　
Description
　　
1
　　
Dispatchers
　　
Equivalent to the mts_dispatchers parameter in 8i
　　
2
　　
Max_dispatchers
　　
Equivalent to the mts_max_dispatchers parameter in 8i
　　
3
　　
Shared_servers
　　
Equivalent to the mts_server parameter in 8i
　　
4
　　
Max_shared_servers
　　
Equivalent to the mts_max_servers parameter in 8i
　　
3. In my actual environment (oracle8.1.7.4), for example, 9i, I added the following MTS parameters to the init parameter file to complete MTS configuration.
　　
# MTS set by qiuyb
　　
Mts_dispatchers = "(address = (Protocol = TCP) (host = 192.168.223.125) (dispatchers = 10 )"
　　
Mts_max_dispatchers = 20
　　
Mts_servers = 10
　　
Mts_max_servers = 50
　　
Mts_service = Billing
　　
Mts_listener_address = "(address = (Protocol = TCP) (host = 192.168.223.125) (Port = 1521)" large_pool_size = 400 m
　　
# End of qiuyb's set
　　
It should be noted that large_pool_size is the initialization parameter. In the MTS environment, we recommend that you set this parameter for better performance. In this way, UGA is allocated from a fixed region such as large_pool, instead of dynamically allocating from the shared pool, this reduces the occurrence of ORA-04031 errors.
　　
5. Optimize MTS configuration options and several questions you may ask
　　
1. How big should I set the parameter large_pool_size?
　　
When the size of large_pool_size can meet the memory required by all the shared service processes, of course, if the memory is sufficient, you can increase it by a bit, the following statement can be used to obtain the maximum amount of memory used for the MTS connection from the instance startup. It can be seen that it is more than 200 MB.
　　
Select sum (value) "Max MTS memory allocated"
　　
From v $ sesstat SS, V $ statname St
　　
Where name = 'session UGA memory Max'
　　
And ss. Statistic # = ST. Statistic #
　　
Max MTS memory allocated
　　
------------------------
　　
214457296
　　
2. How can I determine the number of dispatcher instances?
　　
Use the following statement. When the busy rate of dispatcher exceeds 50%, you need to consider increasing the number of dispatcher, which can be done dynamically using alter system.
　　
Select name, (busy/(busy + idle) * 100 "dispatcher % busy rate"
　　
From v $ dispatcher
　　
3. How can I determine whether the shared service process is sufficient?
　　
Use the following statement to determine the average wait time of each request and monitor the average wait time per reques value. When this value continues to grow, you should consider adding shared servers.
　　
Select decode (totalq, 0, 'no requests') "wait time ",
　　
Wait/totalq | 'hundredths of seconds' "average wait time per request"
　　
From v $ queue
　　
Where type = 'common'
　　
4. How does one request a dedicate connection on the server configured in MTS?
　　
You can add the srvr = dedicated Option When configuring the service name in tnsnames. ora, for example:
　　
Billing =
　　
(Descr resume ption =
　　
(
　　
Address_list = (address = (Protocol = TCP) (host = ks3) (Port = 1521 ))
　　
)
　　
(
　　
CONNECT_DATA =
　　
(SERVICE_NAME = billing)
　　
(Srvr = dedicated)
　　
)
　　
)
　　
Vi. Final text
　　
Using MTS is a good option when your Oracle Server experiences high memory utilization and frequent page changes. In general, MTS is more suitable for OLTP applications. It is not suitable for data warehouses and DDS applications.