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
         (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問題(當然這只是解決方法之一)