Oracle MTS简介

一、什么是MTS

MTS = Multi-Threaded Server

MTS是ORACLE SERVER的一个可选的配置选择，是相对DEDICATE方式而言，它最大的优点是在以不用增加物理资源（内存）的前提下支持更多的并发的连接。

Joseph C.Johnson以餐馆给出一个MTS的形象的比喻　　
　　假设ORACLE是一家餐馆，当你走进一家餐馆时你感觉最舒服的服务方式就是有一个专门的waiter来为你服务，而不管餐馆中来了多少人，她只对你请求应答，这是DEDICTE的处理方式，也就是说每一个ORACLE客户端的连接都有一个专门的服务进程来为它服务。而大部的餐馆的服方式都不是一对一的，当你走进的时侯，你就被指定了一个waiter，她也可能为其它桌服着务，这对于餐馆来说是最有利的，因为他们可以服务更多的客人而不需要增加他们的员工。这样对你来说也可能是不错的，如果餐馆不是太忙，她服务的客人的请求都很简短且容易完成，你的感觉也好像自己拥有一个专门的waiter，waiter把你的ORDER转给厨师，然后把做好的菜拿给你，这就是MTS的处理方式，这些共享的waiters我们叫她们为Dispatchers，厨师我们则叫他们为Shared Server Processes。

二、MTS架构

[metalink-Note:29038.1]

1.  Oracle Multi-Threaded Server Architecture.
----------------------------------------------

                            +-----------+   user
                           +-----------+|   processes
                           |           ||
                           |APPLICATION||
                           |   CODE    ||
                           |           |+
                           +-----------+
                                /|\
                                 |                     CLIENT
          - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
                                 |                     SERVER
                                \|/
                       +----------------------------------+
                      +----------------------------------+|
                      |                                  ||
                      |       DISPATCHER PROCESSES       || 
                      |                                  |+  
                      +----------------------------------+    
                        |                             /|\
                        |                              |
                        |     +-----------+            |
                        |    +-----------+| shared     |
                        |   +-----------+|| server     |
                        |   |  ORACLE   ||+ processes  |
                        |   |SERVER CODE|+             |
                        |   +-----------+              |
                        |    /|\ /|\  |                |
                        |     |   |   |                |
                        |     |   |   |                |
                        |     |  \|/  |                |
        +---------------|-----|-------|----------------|-------+
        |              \|/    |      \|/               |       |
        |   +-------------------+   +----------------------+   |
        |   |     REQUEST       |   |   RESPONSE QUEUES    |   |
        |   |     QUEUES        |   +----------------------+   |
        |   +-------------------+                              |
        |                  SYSTEM GLOBAL AREA                  |
        |                                                      |
        +------------------------------------------------------+

Client Connections to MTS
    ~~~~~~~~~~~~~~~~~~~~~~~~~
 A client process wanting to connect to an Oracle instance using MTS
 should go through the following steps:

  a) Call the listener
  b) The listener tells it to call back on the address where
     the dispatcher is listening (a REDIRECT)
  c) The client calls the dispatcher and establishes a connection.
  d) The dispatcher now has a CLIENT connection ESTABLISHED and
     will also continue to listen for any new connections.
  e) The client sends a SQL message to the dispatcher.
  f) The dispatcher unwraps this message packet and places the
     request onto a queue in the SGA (System Global Area).
     This queue has NOTHING to do with SQL*Net. The SQL*Net
     layer ends in the dispatcher.
  g) A shared server will pick up the request from the SGA queue
     and process it. When there is any result this is placed
     on a separate queue in the SGA ready for the dispatcher.
  h) The dispatcher picks up the response message from the SGA
     and wraps this into a SQL*Net message. This is passed
     back to the client.

三、确定你的DB是否在使用MTS

[metalink-Note:1071305.6]

Solution Description:
=====================
 
Look at the "init.ora" parameter MTS_DISPATCHERS.
 
MTS_DISPATCHERS defines the number of dispatchers created when the instance
starts up. If this is set to zero, or is null (default value), then you are NOT
using MTS.

If MTS_DISPATCHERS is greater than zero, then check these other "init.ora"
parameters for valid values:

   MTS_MAX_DISPATCHERS  Maximum number of dispatchers
   MTS_SERVERS  Number of server processes created at startup
   MTS_MAX_SERVERS  Maximum number of shared server processes
   MTS_SERVICE  SID
   MTS_LISTENER_ADDRESS  Configuration of the listener
 

Solution Explanation:
=====================
 
These other "init.ora" parameters may have valid values, but if MTS_DISPATCHERS
is zero or null, MTS is not being used.

四、MTS相关参数的含义

[metalink-Note:29038.1]

The main parameters required to start MTS are:

  o MTS_SERVICE - This parameter establishes the name of the MTS service
    that clients connect to, in order for the dispatchers to handle requests.
   
  o MTS_DISPATCHERS - Dispatchers are detached processes that handle client
    process requests and communicate them to server processes for execution.

    This parameter defines the number of dispatchers to startup for
    each protocol that is configured for MTS.  For example,
 
         mts_dispatchers = "ipc, 2"

  o MTS_SERVERS - This is the number of shared server processes that
    start at instance startup time.  Shared servers service the
    client requests passed on to them by the dispatchers.

  o MTS_LISTENER_ADDRESS - This is the full address for dispatchers to listen
    on, and is the same as the address configured in the TNS listener.  The
    address specification is protocol-specific.  For example:

         mts_listener_address = "(address=(protocol=ipc)(key=sw))"

五、使用MTS连接DB

先在主机命令行 $ lsnrctl services;

"D000" established:0 refused:0 current:1 max:972 state:ready
         DISPATCHER <machine: db1, pid: 3950>
         (ADDRESS=(PROTOCOL=tcp)(HOST=eagle1)(PORT=33166))；

查看dispather的监听端口号——33166。(dispather进程名称一般是 ora_dNNN_SID,NNN in (000-999))

在client端的tnsname.ora中，注意：

TODB_MTS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 33166))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = dbname)
    )
  )

这样可以解决ora-12520问题(当然这只是解决方法之一)

六、一问一答

Q：MTS一定比Delicated模式好吗？

A：不是。如果硬件好点，建议使用Delicated模式。biti语录：“我是能不用MTS就不用”

Q：MTS与Delicated模式可以相互转换吗？

A：可以。

MTS => Delicated

Reset the MTS Parameters in init.ora and reopen your DB.

Delicated => MTS

Added the MTS Parameters in init.ora and reopen your DB.

Q：MTS模式下可以用Delicated连进来吗？

A：可以。(SERVER = Delicated)即可。反之则不行。

Q：RAC环境一定得是MTS吗？

A：不是。

Q：JDBC thin 支持MTS吗？

A：目前不支持。

1、large_pool_size这个参数我该设为多大呢？
　　
　　当large_pool_size的大小能够满足所有的共享服务进程所需的内存就可以了，当然如果内存够用的话可以适当的加大一点，如下的语句便可以得出自实例启动来MTS连接所用的内存的最大数量，可以看出来是200多M。　　
　　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、如何判断我dispatcher的数量是不是够用呢？
　 使用如下的语句，当dispatcher的繁忙比率超过50%的时侯，你就要考虑增加Dispatcher的数量了，用Alter system动态却可完成。
　　SELECT name, (busy / (busy + idle))*100 "Dispatcher % busy Rate"
　　FROM V$DISPATCHER
　　
3、如何判断共享服务进程是不是够用呢？
　　使用如下的语句来确定每次请求的平均等待时间，监测Average Wait time per reques这个值，当这个值持续增长时你该考虑增加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、如何在MTS配置的Server请求Dedicate的连接着？
　　你在Tnsnames.ora中做服务名配置时加入SRVR=DEDICATED这个选项就可以了，示例如下：
　　billing =
　　(DEscrīptION =
　　(
　　ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = ks3)(PORT = 1521))
　　)
　　(
　　CONNECT_DATA =
　　(SERVICE_NAME = billing)
　　(SRVR = DEDICATED)
　　)
　　)

七、MTS与DEDICATE方式比较