DBA Tips Archive for Oracle

  


Multi-Threaded Server Overview

by Jeff Hunter, Sr. Database Administrator

Contents

  1. Overview
  2. Benefits of MTS
  3. The Trade-offs
  4. Implementing MTS
  5. Bottom Line: Should I use MTS?
  6. Setting up your MTS environment
  7. Changing Database Initialization Parameters
  8. Forcing a Dedicated Server Connection
  9. Viewing MTS Statistics

Overview

Many Oracle DBAs today are faced with the every growing number of connections to their databases. When using the traditional method of connecting to the database (dedicated server) each client process is given a dedicated server process that runs on the database server. This server process is sometimes referred to as a "shadow process". This server process takes memory away from the system and as the number of connections increase, so does the demand for memory. Not only is memory in demand for with these server processes, but so are CPU requirements. If you have many users connecting and disconnecting from the database server, a good amount of CPU will be required in building and destroying these dedicated server processes.

Oracle provides a solution that answers the above two issues called Multi-Threaded Server. This article provides some insight into setting up Multi-Threaded Server (MTS) as well as several scripts that can be used to monitor your MTS environment.

Benefits of MTS

Think about a system in which only 4 client processes need access to the database. With dedicated server, each client process requires one dedicated server processes. If each server process consumes 8 megabytes of memory, you will need 32 megabytes to support this scenario. Now consider 100 dedicated connections. In this scenario, you would require 800 megabytes of memory! Using MTS, you can see in Figure 1 that these same 4 client connections can be supported by only 2 shared server processes.


Figure 1: Dedicated Server vs. Shared Server

Another thing to consider is OLTP environment where users are spending most of their time reading and editing data on their screens and very little time actively executing SQL statements against the database. In this scenario, many of those dedicated server processes are sitting idle and needlessly consuming memory. Because MTS allows the sharing of the server process between many client processes, the DBA can now make more efficient use of the server processes.

Lastly, MTS can improve with environments where clients are constantly connecting and disconnecting from the database. Since shared server processes are not tied directly to a client process, they are not built and destroyed each time a client connects or disconnects. Oracle will maintain an active "pool" of shared server processes. New shared server processes are only created when demand exceeds supply. On the same note, shared server processes are only destroyed when demand drops off and those extra shared server processes are no longer needed.

The following list outlines the advantages of using MTS:

The Trade-offs

MTS is not for everyone! Before implementing MTS in your environment, keep the following in mind:

Implementing MTS

After deciding MTS is right for your environment, it is now time to dig into the implementation details. The DBA will need to consider the following two issues: User Session-Specific Data (UGA) and Routing Client Requests to Shared Server Processes.

User Session-Specific Data (UGA)
Every connection to the Oracle database has a "session-specific" memory associated with it. This memory is referred to as the User Global Area (UGA) and is used to hold the values of PL/SQL variables, the values of bind variables and other items specific to a session. The UGA also contains that part of the sort area specified by the SORT_AREA_RETAINED_SIZE initialization parameter.

With a dedicated server connection, the UGA is stored within the dedicated server process's Program Global Area (PGA). When an MTS connection is made though, the UGA is stored in either the "LARGE POOL" or the "SHARED POOL". This difference is illustrated in Figure 2.

In an MTS environment, the UGA needs to be moved into a common memory structure like the large pool or shared pool since it contains "session-specific" data. Keep in mind that the use of MTS does not affect the overall "amount" of memory used for session specific data. It is only moved from the memory space of the individual process into a common memory structure accessible by all processes.

Figure 2 shows the UGA located in the large pool. If you do not have the large pool configured, then Oracle will place the UGA in the shared pool. Oracle does not recommend placing the UGA in the shared pool as this will cause fragmentation.


Figure 2: MTS changes where session-specific information is stored

Routing Client Requests to Shared Server Processes
The second major issue of MTS is setting up the dispatcher. When using a dedicate server connection, the client process simply passes the SQL statements to be executed to its dedicated server process. What happens though when in an MTS environment the client process has no dedicated server process to send the SQL to? The answer is that the request gets sent to a dispatcher process.

In an MTS environment, the dispatcher process assumes the role of communicating with the clients and routing your request. The listener no longer hands the client off to a dedicated server process, but rather gives each new client to the most lightly loaded dispatcher process. For the duration of the session, the dispatcher process will handle all communication with the client.

The dispatcher processes will then take the requests (with are often SQL statements) from the client and place them into a request queue. The request queue is located as a memory structure within the System Global Area (SGA) and is where all incoming requests are placed. All shared servers you have setup within your instance will monitor the request queue. When a request is entered into the request queue, the next available shared server process will pull it out of the queue and service it. All requests are handled in a first-in/first-out basis. This can be seen in Figure 3.


Figure 3: Within an MTS setup, the dispatcher is responsible for all communication to and from the client

When the shared server process completes the task, the results are placed into a response queue. Like the request queue, the response queue is a memory structure located within the SGA. Response queues are directly tied to a specific dispatcher. While each dispatcher has its own response queue, each dispatcher on the other hand will have the same request queue. When a dispatcher detects a response in its response queue, it will send that response back to the client that first initiated the request.

How many dispatchers should you use in your MTS environment? Well, for one you will need at lease one dispatcher for each network protocol that you are supporting. Like shown in Figure 3, you created one dispatcher for TCP/IP and another for SPX. Aside from the number of network protocols, you also need to decide on the number of connections you are planning to support. There is a limit as to how many connections that each dispatcher process can handle. (This number is operating-system specific).

Bottom Line: Should I use MTS?

Well, this depends on your environment. If you are supporting a large number of client connections and where those connections are mostly inactive, then MTS would be ideal here. MTS would not be a good setup if your environment does not involve much idle time with your client connections. (i.e. batch jobs). Keep in mind that you setup an environment that allows for both MTS and dedicate server processes. Setup user connections who only sporadically accessing the database using MTS, while batch jobs and other data intensive connections can be made using dedicated server connections. Lastly, remember that from the client's perspective, MTS does not enhance their performance, but rather reduces the CPU and memory overhead associated with supporting many client (mostly idle) connections.

Setting up your MTS environment

How Many Dispatchers?
As discussed above, you will need to provide at lease one dispatcher process for each protocol you plan on supporting. Another factor that will affect your decision on the number of dispatcher processes is the operating system's limit on the number of connections that can be made to a single process. If for example, your operating system allows a maximum of 972 connections to one process, and you want to support 3500 TCP/IP connections using MTS, then you will need a minimum of four (4) dispatcher processes for the TCP/IP network protocol.

Determining OS Connection Limits
Determining the connection limit for your particular operating system is fairly easy. You will need to use the "SERVICES" command within the Listener Control utility. Simply perform the following tasks:

  • Use the MTS_DISPATCHERS initialization parameter to configure a test database for one dispatcher.
  • Stop and restart your instance to read in the new value of MTS_DISPATHCERS.
  • Run the Listener Control utility and issue the SERVICES command. The output will include a list of dispatcher processes.
  • Now find the dispatcher process in the list of services and look at the value for max. This value indicates the maximum number of connections that your operating system allows for one process.

Here is a small sample. Note that the value for max is 972.

  LSNRCTL> SERVICES
  Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=SOPDEV2.world))
  Services Summary...
  Service "SOPDEV2" has 2 instance(s).
  Instance "SOPDEV2", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
  Instance "SOPDEV2", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=appdev)(PORT=47594))
NOTE: Do not attempt to perform the above test on a production database when users are trying to connect. In fact, use a "testing" database to ensure that you will not create connection problems for people in your production environment.
Table 1: Determining OS Connection Limits

How Many Shared Server Processes?
Setting up the number of shared server processes requires the use of two initialization parameters: MTS_SERVERS and MTS_MAX_SERVERS. They control both the minimum and maximum number of shared server processes that you want to create for your instance.

How Much Memory?
Before implementing MTS in your environment, you will need to decide how much shared pool or large pool memory is required for session-specific memory. An easy way to do this, is to gather the current amount of memory used by a representative session, and then extrapolate from that to cover the number of sessions you expect to have simultaneously. Suppose I want to gather memory information from a user logged in as "JHUNTER". First you will need to get the "SID" for the "JHUNTER" user. After obtaining the proper SID, select the current "UGA memory" and "UGA memory max" from v$sesstat.

  SQL> SELECT sid FROM v$session WHERE username = 'JHUNTER';

         SID
  ----------
          15


  SQL> SELECT sn.name, ss.value
    2  FROM v$sesstat ss, v$statname sn
    3  WHERE ss.statistic# = sn.statistic#
    4    AND sn.name IN ('session uga memory', 'session uga memory max')
    5    AND ss.sid=15;

  NAME                     VALUE
  ------------------------ ----------
  session uga memory       54364
  session uga memory max   57076
The session uga memory value represents the amount of memory that a user is currently using for session-specific information. The other value, session uga memory max represents the maximum amount of session-specific memory that has been used at any one time during the user's current session. Lets say that the "JHUNTER" user (above) was representative of many of the other users in the database that are going to connect via MTS, and you expected 500 such connections concurrently, simply multiply both values (session uga memory and session uga memory max) by 500 to come up with an estimated range of 27,182,000 to 28,538,000 bytes. This will be the amount of additional memory that you will need to allocate in the SGA for use by these 500 MTS sessions. Remember that MTS uses the shared pool for session memory, but Oracle recommends the use of the large pool. By using the large pool, you avoid fragmenting the shared pool. Given the above example, I would add the following to my init.ora file:

 large_pool = 32M
The above allocates a large pool of 32 MB in size. This is all you really have to do in order to have Oracle use it for MTS session memory.

Changing Database Initialization Parameters

When thinking about setting up your MTS environment, you might be tempted to think that this will require changes to your Net8 configuration files like the listener.ora. As you will see, this is not the case. All parameters for setting up your MTS environment are made to the instance and not any your Net8 config files. Remember that the dispatcher and shared processes are actually part of the instance, so it makes sense that you would configure them in the same place.

This section of the article discusses the changes that will need to be made to the following parameters:

Setting up an MTS environment can be as simple as adding one or more MTS_DISPATCHERS parameters to your instance parameter file and then bouncing the database. It's likely however, that you're going to want to tune some of the other parameters above.

MTS_DISPATCHERS

This is the key initialization parameter that needs to be set in order to implement MTS. The syntax here is very similar to what you would use in Net8 configuration files like the listener.ora file.

Here is an example of enabling MTS by only specifying the protocol to support. This will accept default values for all other attributes:

  MTS_DISPATCHERS="(PROTOCOL=TCP)"
If you want to support more than one protocol (TCP/IP and SPX for example), you would enter two MTS_DISPATCHERS parameter settings:
  MTS_DISPATCHERS="(PROTOCOL=TCP)"
  MTS_DISPATCHERS="(PROTOCOL=SPX)"
NOTE: When declaring multiple MTS_DISPATCHERS parameters, they must be grouped together in the init.ora file. Blank links are allowed, but do not place any other parameter settings between two MTS_DISPATCHERS settings. Doing this will cause the instance to error when you try to start it.
Two other commonly used attributes in the MTS_DISPATCHERS are setting the "number of dispatchers" and maybe the "number of connections" each dispatcher is allowed to handle. The following example allocates four TCP/IP dispatchers and each of those dispatchers can handle up to 250 connections.
  MTS_DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=4)(CONNECTIONS=250)"
Another attribute that deserves attention for MTS_DISPATCHERS is the LISTENER attribute. The LISTENER attribute allows you to specify the listener with which the dispatcher should register. By default, the dispatcher will register with the listener that is monitoring port 1521 on the local database server. Using the LISTENER attribute though, you can override this and have your dispatcher register with a listener assigned to either a different port or with a listener running on another database server. The following is an example of using the LISTENER attribute:
  MTS_DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=4)(CONNECTIONS=250) \
                   (LISTENER=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PORT=1526)(HOST=cartman))))"
Here the LISTENER attribute defines an ADDRESS_LIST containing one or more listener addresses. The dispatchers will then register with each of those listeners.
NOTE: There is also a LOCAL_LISTENER initialization parameter that provides the same functionality as the MTS_DISPATCHERS parameter's LISTENER attribute. The LISTENER attribute will override the LOCAL_LISTENER parameter and is the recommended by Oracle Corp.
One final note to make in reference to setting the MTS_DISPATCHERS is where you have the option of specifying the network address that it will monitor. (Similar to how you would set the listener process to listen on port 1521). One of the major reasons for forcing the port number for dispatchers is get around firewall issues.

By default, whenever you specify the protocol to support, the dispatcher will decide on the address automatically. You have the option though of specifying which address the dispatcher uses by replacing the PROTOCOL attribute with either the ADDRESS or DESCRIPTION attribute. Below is an example that uses the ADDRESS attribute. Also notice that in order to specify a port, that ALL dispatchers must be configured separately to ensure that no two dispatchers are sharing the same address:

  MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=cartman)(PORT=1562)) \
                   (DISPATCHERS=1)(CONNECTIONS=250)"

  MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=cartman)(PORT=1563)) \
                   (DISPATCHERS=1)(CONNECTIONS=250)"

  MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=cartman)(PORT=1564)) \
                   (DISPATCHERS=1)(CONNECTIONS=250)"

  MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=cartman)(PORT=1565)) \
                   (DISPATCHERS=1)(CONNECTIONS=250)"

MTS_MAX_DISPATCHERS

The MTS_MAX_DISPATCHERS parameter defines an upper limit on the number of dispatchers that can be running at any one time. The default value for this parameter is 5. Keep in mind that this value is subject to modification based on your values defined in MTS_DISPATCHERS. Examine the following example.
  MTS_DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=12)(CONNECTIONS=250)"
  MTS_DISPATCHERS="(PROTOCOL=SPX)(DISPATCHERS=10)(CONNECTIONS=250)"
  MTS_MAX_DISPATCHERS=16
Given the example above, the actuall value for MTS_MAX_DISPATCHERS would be adjusted upward to 22. (12 for TCP and 10 for SPX)

MTS_SERVERS

The MTS_SERVERS parameter controls the number of shared server processes that will be available to service MTS connections. With this parameter, you are defining the number of shared server processes that will be created when the instance starts. (This is also the minimum number of servers for the instance. Oracle will not reduce the number of server processes below this number).

MTS_MAX_SERVERS

The MTS_MAX_SERVERS parameter puts in place an upper limit on the number of shared server processes that can be running at any given time. While an instance is running, the number of shared server processes increases and decreases. However, the number of shared server processes will never exceed the limit placed by use of the MTS_MAX_SERVERS parameter.

LOCAL_LISTENER

As mentioned above, the LOCAL_LISTENER parameter serves the same purpose of the LISTENER attribute of the MTS_DISPATCHERS parameter. It identifies the listener with which MTS dispatchers will register with. Below are two example of setting this parameter:
  LOCAL_LISTENER="(ADDRESS_LIST= \
                           (ADDRESS= \
                             (PROTOCOL=TCP) \
                             (HOST=cartman) \
                             (PORT=1526) \
                           ) \
                         )"
  LOCAL_LISTENER="(ADDRESS_LIST= \
                           (ADDRESS= \
                             (PROTOCOL=TCP)
                             (HOST=localhost)
                             (PORT=1521)
                           )
                           (ADDRESS=
                             (PROTOCOL=IPC)
                             (KEY=O901DB)
                           )
                         )"
The first example specifies one listener address. Since the port for this listener is 1526, rather than the default of 1521, this is considered a non-default listener. The second example shows two addresses and also represents the default value for the LOCAL_LISTENER parameter.

Consider the following when trying to decide on whether to use the LOCAL_LISTENER init.ora parameter or the LISTENER attribute of MTS_DISPATCHERS. Using the LISTENER attribute gives you finer control; you can have different dispatchers register with different listeners. LOCAL_LISTENER on the other hand, provides global control. Oracle still recommends the use of the LISTENER attributed defined in the MTS_DISPATCHERS parameter.

LARGE_POOL

We have discussed the use of the LARGE_POOL several times in this article. Although it is not and MTS specific parameter, it is something that needs strong consideration when implementing MTS. If your instance does not have a large pool defined, the session specific memory for all MTS connections will be taken from the shared pool. Have MTS take from the shared pool causes fragmentation and is not the recommended configuration from Oracle.

Forcing a Dedicated Server Connection

Remember that when configuring MTS requires no changes being made to your Net8 config files. MTS configuration happens at the instance level, and when configured, new connections start using it. One thing you can control though through the use of your Net8 config files, is forcing a dedicated connection. (Overriding the MTS configuration at the instance). There are basically two ways to force a dedicated server connection: tnsnames.ora and sqlnet.ora.

tnsnames.ora
Simply add the attribute (SERVER=DEDICATED) to the list of CONNECT_DATA attributes.

  O901DB_DEDICATED.world =
  (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS =
            (PROTOCOL = TCP)
            (Host = cartman)
            (Port = 1521)
         )
     )
     (CONNECT_DATA =
         (SID = O901DB)
         (SERVER = DEDICATED)
     )
  )

sqlnet.ora
Use the USE_DEDICATED_SERVER=ON in the sqlnet.ora file to force EVERY session that you initiate from your client to use dedicated server. As you can see, this is a much less flexible approach as it does not allow for specific connections to use dedicate server.

Viewing MTS Statistics

Operating-System Commands

Within UNIX and Linux, each dispatcher and shared server process is a separate operating-system process. The exact naming format varies from platform to platform, but the dispatcher process will always have the format dxxx in their name, and the shared server process will always have sxxx in their name. In almost all cases, the xxx will be a number starting at 000. This number gets incremented sequentially for each new dispatcher and/or shared server process started.

On most UNIX system, you can make use of the ps command to generate a list of these processes. You would then pipe this output into the grep command to filter out the ones that you are interested in. The following is an example:

  O901DB on cartman: ps -ef | grep ora_[ds][0123456789]*_O901DB
  oracle  8742     1  0   Feb 06 ?        0:00 ora_s013_O901DB
  oracle  8740     1  0   Feb 06 ?        0:00 ora_s012_O901DB
  oracle  8720     1  0   Feb 06 ?        0:00 ora_s002_O901DB
  oracle  8724     1  0   Feb 06 ?        0:00 ora_s004_O901DB
  oracle  8716     1  0   Feb 06 ?        0:08 ora_s000_O901DB
  oracle  8738     1  0   Feb 06 ?        0:00 ora_s011_O901DB
  oracle  8718     1  0   Feb 06 ?        0:06 ora_s001_O901DB
  oracle  8728     1  0   Feb 06 ?        0:00 ora_s006_O901DB
  oracle  8736     1  0   Feb 06 ?        0:00 ora_s010_O901DB
  oracle  8722     1  0   Feb 06 ?        0:00 ora_s003_O901DB
  oracle  8744     1  0   Feb 06 ?        0:00 ora_s014_O901DB
  oracle  8726     1  0   Feb 06 ?        0:00 ora_s005_O901DB
  oracle  8730     1  0   Feb 06 ?        0:00 ora_s007_O901DB
  oracle  8734     1  0   Feb 06 ?        0:00 ora_s009_O901DB
  oracle  8732     1  0   Feb 06 ?        0:00 ora_s008_O901DB
  oracle  8746     1  0   Feb 06 ?        0:00 ora_s015_O901DB
  oracle  8748     1  0   Feb 06 ?        0:00 ora_s016_O901DB
  oracle  8750     1  0   Feb 06 ?        0:00 ora_s017_O901DB
  oracle  8752     1  0   Feb 06 ?        0:00 ora_s018_O901DB
  oracle  8754     1  0   Feb 06 ?        0:00 ora_s019_O901DB
  oracle  8756     1  0   Feb 06 ?        0:00 ora_s020_O901DB
  oracle  8758     1  0   Feb 06 ?        0:00 ora_s021_O901DB
  oracle  8760     1  0   Feb 06 ?        0:00 ora_s022_O901DB
  oracle  8762     1  0   Feb 06 ?        0:00 ora_s023_O901DB
  oracle  8764     1  0   Feb 06 ?        0:00 ora_s024_O901DB
  oracle  8766     1  0   Feb 06 ?        0:01 ora_d000_O901DB
  oracle  8768     1  0   Feb 06 ?        0:02 ora_d001_O901DB
The above example shows two dispatcher processes as well as 25 shared server processes.

Listener Control

Using the SERVICES command within the Listener Control utility, you can get a listing of the dispatchers that have been registered with the listener. Here is a sample out of the SERVICES command:
  LSNRCTL> SERVICES listener
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cartman)(PORT=1526)))
  Services Summary...
  Service "O901DB" has 1 instance(s).
    Instance "O901DB", status UNKNOWN, has 4 handler(s) for this service...
      Handler(s):
        "DEDICATED" established:194 refused:0
           LOCAL SERVER
        "DEDICATED" established:0 refused:0 state:ready
           LOCAL SERVER
        "DISPATCHER" established:96 refused:23 current:38 max:250 state:ready
           D000 
           (ADDRESS=(PROTOCOL=tcp)(HOST=cartman)(PORT=1219))
  Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Handler(s):
        "DEDICATED" established:0 refused:0
           LOCAL SERVER
  The command completed successfully
Figure 4 (below) gives a short overview of the different parameters in the SERVICES output command.

Figure 4: Output from the Listener Control's SERVICES command

Dynamic Performance Views


Dynamic Performance View relevant to MTS
View name Description
V$CIRCUIT Returns one row to the instance for each MTS connection. A circuit is a connection through a specific dispatcher and shared server process. Columns in this view relate each MTS circuit to a specific dispatcher, shared server process and session.
V$DISPATCHER Returns one row for each dispatcher process associated with the instance. This view returns information such as the dispatcher's name, network address, process address and status.
V$DISPATCHER_RATE Returns one row for each dispatcher process, and returns rate statistics for each dispatcher.
V$MTS Returns only one row. This view provides some statistics that you can use to determine whether or not you have the MTS_SERVERS parameter set to a reasonable value.
V$QUEUE Returns one row for MTS queue in the instance. Each dispatcher will have one response queue associated with it, and their will always be one common request queue for the instance. The number of rows returned by V$QUEUE is always equal to the number of dispatchers plus one.
V$SHARED_SERVER Returns one row for each shared server process that is currently running as part of the instance. This view returns the process name, process address, status and other useful statistics.
Table 2: Dynamic Performance View relevant to MTS

Dispatcher Process Status

  SQL> SELECT name, status, accept, created, conf_indx, network
    2  FROM v$dispatcher;

  NAME STATUS ACCEPT CREATED CONF_INDX NETWORK
  ---- ------ ------ ------- --------- --------------------------------------------------
  D000 WAIT   YES    56      0         (ADDRESS=(PROTOCOL=tcp)(HOST=cartman)(PORT=38793))
  D001 WAIT   YES    78      1         (ADDRESS=(PROTOCOL=tcp)(HOST=cartman)(PORT=38794))
  • NAME: Returns the dispatcher's name. This forms part of the operating system process name.

  • NAME: Returns the dispatcher's current status. Valid values are:

    • WAIT: The dispatcher is idle and waiting for work.
    • SEND: The dispatcher is sending a message.
    • RECEIVE: The dispatcher is receiving a message.
    • CONNECT: The dispatcher is establishing a new connection from a client.
    • DISCONNECT: A client is disconnecting from the dispatcher.
    • BREAK: The dispatcher is handling a break.
    • OUTBOUND: The dispatcher is establishing an outbound connection.

  • ACCEPT: Tells you whether or not the dispatcher is accepting new connections. Valid values are YES and NO.

  • CREATED: Returns the number of virtual circuits currently associated with this dispatcher.

  • CONFIG IDX: Indicates the specific MTS_DISPATCHERS initialization parameter on which this dispatcher is based. Dispatchers created from the first MTS_DISPATCHERS parameter in your instance's parameter file will have a CONF_INDX value of 0. Dispatcher's created from the second MTS_DISPATCHERS parameter will have a value of 1, and so on.

  • NETWORK: Returns the dispatcher's network address.

Dispatcher Process Utilization

  SQL> SELECT name, busy / (busy + idle) * 100
    2  FROM v$dispatcher;

  NAME BUSY/(BUSY+IDLE)*100
  ---- --------------------
  D000           .000359889
  D001           .000405469
Doesn't look like these dispatchers are very busy! Keep in mind that the BUSY and IDLE values are reported in hundredths of a second. If the BUSY value for a dispatcher is 100, that means the dispatcher had been busy for 1 second.

If dispatcher utilization is very high, you should consider creating more dispatcher processes. If dispatcher utilization is very low, you should consider deleting some dispatcher processes. In the above example, I would consider only running one dispatcher process to support this instance.

Queue Size and Wait Time

  SQL> SELECT paddr, type, queued, DECODE(totalq,0,0,wait/totalq)
    2  FROM v$queue;

  PADDR    TYPE         QUEUED DECODE(TOTALQ,0,0,WAIT/TOTALQ)
  -------- ---------- -------- ------------------------------
  00       COMMON            0                     .009878904
  8008AC54 DISPATCHER        0                     3.38482683
  8008AF70 DISPATCHER        0                     .009591116
You can get an idea of how well work is flowing through the request and response queues by using v$queue.

The DECODE in the query handles the case where the TOTALQ column, which is the divisor, happens to be zero. The average wait time is reported in hundredths of a second. (i.e. If the average wait time of a dispatcher is 37, works out to 0.37 seconds.)

The COMMON queue is where requests are placed so that they can be picked up and executed by a shared server process. If you average wait time is high, you might be able to lower it by creating more shared server processes.

Users and Dispatchers

  SQL> SELECT  d.name, s.username, c.status, c.queue
    2  FROM   v$circuit c, v$dispatcher d, v$session s
    3  WHERE  c.dispatcher = d.paddr AND c.saddr = s.saddr;

  NAME USERNAME           STATUS           QUEUE
  ---- ------------------ ---------------- ----------------
  D000 JHUNTER            NORMAL           NONE
  D001 AHUNTER            NORMAL           NONE
  • NAME: Returns the dispatcher's name. This forms part of the operating system process name.

  • USERNAME: Oracle username.

  • STATUS: Reports the status of the circuit, and may take one of the following values:

    • BREAK: The circuit had been interrupted due to a break.
    • EOF: The connection is terminating, and teh circuit is about to be deleted.
    • OUTBOUND: The circuit represents an outbound connection to another database.
    • NORMAL: The circuit represents a normal client connection.

  • QUEUE: Reports on the work currently being done. One the following values will be returned:

    • COMMON: A request has been placed into the common request queue, and the circuit is waiting for it to be picked up be a shared server process.
    • DISPATCHER: Results from a request are being returned to the client by the dispatcher.
    • SERVER: A request is currently being acted upon by a shared server process.
    • NONE: A circuit (or connection) is idle. Nothing is happening.

Shared Server Utilization

  SQL> SELECT name, busy / (busy + idle) * 100
    2  FROM v$shared_server;

  NAME BUSY/(BUSY+IDLE)*100
  ---- --------------------
  S000            .00155527
  S001           .000669656
  S002           .000012313
  S003                    0
  S004                    0
  S005                    0
  S006                    0
  S007                    0
  S008                    0
  S009                    0
  S010                    0
  S011                    0
  S012                    0
  S013                    0
  S014                    0
  S015                    0
  S016                    0
  S017                    0
  S018                    0
  S019                    0
  S020                    0
  S021                    0
  S022                    0
  S023                    0
  S024                    0

Other Shared Server Statistics

The v$mts view is useful when trying to tune both the MTS_SERVERS and MTS_MAX_SERVERS initialization parameters. Here is an example output from v$mts:

  SQL> SELECT servers_started, servers_terminated, servers_highwater
    2  FROM v$mts;

  SERVERS_STARTED SERVERS_TERMINATED SERVERS_HIGHWATER
  --------------- ------------------ -----------------
               94                 18                25
  • SERVERS_STARTED: The number of shared server processes started as the instance adjusts the number of shared processes up and down from the initial value specified by the MTS_SERVERS parameter. When the instance starts, and after the initial number of shared server processes specified by MTS_SERVERS has been started, this value is set to 0. From that point on, this value is incremented whenever a new shared server process is started.

  • SERVERS_TERMINATED: A count of the total number of shared server processes that have been terminated since the instance was started.

  • NOTES: If the SERVERS_HIGHWATER value matches, the instance's MTS_MAX_SERVERS value, then you might realize a performance benefit from increasing MTS_MAX_SERVERS. If the counts for SERVERS_STARTED and SERVERS_TERMINATED keep climbing, then you should consider raising MTS_SERVERS. Raising the minimum number of shared server processes should reduce the number that are deleted only to be recreated later.


Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 09-Jun-2003 00:00:00 EDT
Page Count: 27508