Oracle MTS簡介

MTS是ORACLE SERVER的一個可選的配置選擇，是相對DEDICATE方式而言，它最大的優點是在以不用增加物理資源（記憶體）的前提下支援更多的併發的連線。

Joseph C.Johnson以餐館給出一個MTS的形象的比喻　　
　　假設ORACLE是一家餐館，當你走進一家餐館時你感覺最舒服的服務方式就是有一個專門的waiter來為你服務，而不管餐館中來了多少人，她只對你請求應答，這是DEDICTE的處理方式，也就是說每一個ORACLE客戶端的連線都有一個專門的服務程式來為它服務。而大部的餐館的服方式都不是一對一的，當你走進的時侯，你就被指定了一個waiter，她也可能為其它桌服著務，這對於餐館來說是最有利的，因為他們可以服務更多的客人而不需要增加他們的員工。這樣對你來說也可能是不錯的，如果餐館不是太忙，她服務的客人的請求都很簡短且容易完成，你的感覺也好像自己擁有一個專門的waiter，waiter把你的ORDER轉給廚師，然後把做好的菜拿給你，這就是MTS的處理方式，這些共享的waiters我們叫她們為Dispatchers，廚師我們則叫他們為Shared Server Processes。

 

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.

 

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.

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))"

先在主機命令列 $ 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問題(當然這只是解決方法之一)

A：不是。如果硬體好點，建議使用Delicated模式。biti語錄：“我是能不用MTS就不用”

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.

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

A：不是。

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方式比較