There are several ways to look at what's happening in a multi-threaded server environment. Using operating-system commands, you may be able to see a list of the dispatcher and shared server processes that are running at any given time. Listener Control commands can be used to display a list of dispatchers associated with any given listener, and that list also shows you the current number of connections supported by each dispatcher. Finally, there are a number of dynamic performance views that report MTS-related statistics.

Operating-System Commands

On Unix and Linux systems, each dispatcher is a separate operating-system process. Likewise, each shared server is also an operating-system process. The exact naming convention seems to vary somewhat from platform to platform, but the dispatcher processes always have dxxx in their name, and the shared server processes always have sxxx in their name. In both cases, the xxx refers to a number that begins at 000, and that is incremented sequentially for each new dispatcher or shared server process that is started.

On most Unix and Linux systems, the ps -ef command can be used to generate a complete list of running processes. You can then search via grep for the ones that interest you. The following example shows one way to list all of the currently running dispatcher and shared server processes for the instance named donna:

[oracle@donna pfile]$ ps -ef | grep ora_[ds][0123456789]*_donna
oracle     697     1  0 15:34 ?        00:00:00 ora_s000_donna
oracle     699     1  0 15:34 ?        00:00:00 ora_s001_donna
oracle     701     1  0 15:34 ?        00:00:00 ora_s002_donna
oracle     703     1  0 15:34 ?        00:00:00 ora_s003_donna
oracle     705     1  0 15:34 ?        00:00:00 ora_s004_donna
oracle     707     1  0 15:34 ?        00:00:00 ora_d000_donna
oracle     709     1  0 15:34 ?        00:00:00 ora_d001_donna
oracle     711     1  0 15:34 ?        00:00:00 ora_d002_donna
oracle     713     1  0 15:34 ?        00:00:00 ora_d003_donna

In this example, there are four dispatcher processes and five shared server processes. You could use a less complicated grep command. For example, if you were willing to look at all the processes associated with the instance, you could use:

ps -ef | grep donna

To list all dispatcher and shared server processes for all instances, use:

ps -ef | grep ora_[ds]

Listener Control

You can use the Listener Control utility's SERVICES command to generate a list of the dispatchers that have registered with a listener. In the following example, the SERVICES command is used to list the dispatchers that have registered with the listener named PRODLISTENER:

LSNRCTL> SERVICES PRODLISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=donna.gennick.org)
(PORT=1523)))
Services Summary...
  donna         has 5 service handler(s)
    DEDICATED SERVER established:0 refused:0
      LOCAL SERVER
    DISPATCHER established:0 refused:0 current:0 max:100 state:ready
      D003 <machine: donna.gennick.org, pid: 713>
      (ADDRESS=(PROTOCOL=tcp)(HOST=donna.gennick.org)(PORT=1220))
    DISPATCHER established:0 refused:0 current:0 max:100 state:ready
      D002 <machine: donna.gennick.org, pid: 711>
      (ADDRESS=(PROTOCOL=tcp)(HOST=donna.gennick.org)(PORT=1219))
    DISPATCHER established:0 refused:0 current:0 max:100 state:ready
      D001 <machine: donna.gennick.org, pid: 709>
      (ADDRESS=(PROTOCOL=tcp)(HOST=donna.gennick.org)(PORT=1218))
    DISPATCHER established:78 refused:32 current:22 max:100 state:ready
      D000 <machine: donna.gennick.org, pid: 707>
      (ADDRESS=(PROTOCOL=tcp)(HOST=donna.gennick.org)(PORT=1217))
The command completed successfully

The output of the SERVICES command is organized by instance. In this case, the instance name is donna, and five service handlers from that instance have registered with the listener. Four of those service handlers are dispatchers. 

Dynamic Performance Views

Several V$ views, listed in the following table, return information and statistics about multi-threaded server connections and processes.

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, status, and so on.
V$DISPATCHER_RATE Returns one row for each dispatcher process, and returns rate statistics for each dispatcher.
V$MTS Returns just 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 each MTS queue in the instance. Each dispatcher will have one response queue associated with it, and there will always be one common request queue for the instance. Thus, 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.

The views described above can be used to generate a quick snapshot of current MTS status, or they can be used in MTS tuning activities. The following sections describe particularly useful queries that you can execute against these views. Click Here for a description of the full set of columns in these views.

Dispatcher status

To find out how many dispatchers you have, or to check their status, query the V$DISPATCHER view as shown in this example:

SQL> SELECT name, status, accept, created, conf_indx, network
  2  FROM v$dispatcher;
NAME STATUS ACCEPT  CREATED  CONF_INDX NETWORK
---- ------ ------ -------- ---------- ------------------------------
D000 WAIT   YES           0          0 (ADDRESS=(PROTOCOL=tcp)(HOST=
                                       donna.gennick.org)(PORT=1217))

D001 WAIT   YES           0          1 (ADDRESS=(PROTOCOL=tcp)(HOST=
                                       donna.gennick.org)(PORT=1218))

D002 WAIT   YES           0          2 (ADDRESS=(PROTOCOL=tcp)(HOST=
                                       donna.gennick.org)(PORT=1219))

D003 WAIT   YES           1          3 (ADDRESS=(PROTOCOL=tcp)(HOST=
                                       donna.gennick.org)(PORT=1220))

The columns returned by this query are defined as follows:

Column Description
NAME Returns the dispatcher's name. This forms part of the dispatcher's operating system process name.
STATUS

Returns the dispatcher's status. The following status values are valid:

  • 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 (see V$CIRCUIT) currently associated with the dispatcher.
CONF_INDX 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. Dispatchers created from the second MTS_DISPATCHERS parameter will have a value of 1, and so on.
NETWORK Returns the dispatcher's network address.

Dispatcher utilization

You can get an idea of how busy your dispatchers are by looking at the IDLE and BUSY columns of the V$DISPATCHER view. The following query returns dispatcher utilization as a percentage of the time they are busy versus the time they are idle:

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

NAME BUSY/(BUSY+IDLE)*100
---- --------------------
D000           .010461345
D001                    0
D002                    0
D003           .013101352

The dispatchers in this example aren't very busy. Two have utilization rates of around 0.01%, and two haven't been utilized at all.

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 has been busy for 1 second.

If dispatcher utilization is high, you might consider creating more dispatchers in order to lighten the load on each. If dispatcher utilization is low, you should consider deleting some dispatchers. With respect to the example shown here, there doesn't seem to be a need for more than two dispatchers.

Queue size and wait time

You can get an idea of how well work is flowing through the request and response queues by executing the query against V$QUEUE as shown in the following example:

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

PADDR    TYPE           QUEUED   AVG_WAIT
-------- ---------- ---------- ----------
00       COMMON              1         10
524C86DC DISPATCHER          3          5
524C89DC DISPATCHER          0          0
524C8CDC DISPATCHER          5         37
524C8FDC DISPATCHER          0          0

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. The average wait time of the third dispatcher in this example is 37, which 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 your average wait time is high, you might be able to lower it by creating more shared server processes.

Users and dispatchers

You can find out which users are connected to which dispatchers by joining the V$DISPATCHER, V$CIRCUIT, and V$SESSION views. Each row returned by V$CIRCUIT represents a connection from a client to an instance by way of a dispatcher and a shared server process. While the specific shared server process may change from one request to the next, the dispatcher always remains the same. The following query returns a list of user sessions connected to each dispatcher:

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
  4    AND c.saddr = s.saddr
  5  ORDER BY d.name, s.username;

NAME USERNAME                       STATUS           QUEUE
---- ------------------------------ ---------------- ----------------
D000 SCOTT                          NORMAL           NONE
D001 GENNICK                        NORMAL           NONE
D002 GNIS                           NORMAL           NONE
D003 ORAMAG                         NORMAL           NONE
D003 SYSTEM                         NORMAL           SERVER

The STATUS and QUEUE columns have the following meanings:

Column Description
STATUS

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

  • BREAK – The circuit has been interrupted due to a break.
     
  • EOF – The connection is terminating, and the 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 of 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 by 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 – The circuit (or connection) is idle. Nothing is happening.

Shared server utilization

You can report on the utilization of shared server processes in much the same way you do for dispatchers. For shared server processes, you need to query the V$SHARED_SERVER view. For example:

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

NAME BUSY/(BUSY+IDLE)*100
---- --------------------
S000           99.9875814
S001           .000560777
S002                    0
S003                    0
S004                    0

In this example, the shared server named S000 has been 99% busy. Utilization of the other shared server processes is zero, or near zero.

Other shared server statistics

The V$MTS view returns some statistics that are useful when tuning the MTS_SERVERS and MTS_MAX_SERVERS settings. The following example shows a query against V$MTS:

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

SERVERS_STARTED SERVERS_TERMINATED SERVERS_HIGHWATER
--------------- ------------------ -----------------
             62                 10                57

The columns have the following meanings:

Column Description
SERVERS_STARTED The number of shared server processes started as the instance adjusts the number of shared server 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.
SERVERS_HIGHWATER The maximum number of shared server processes that have ever been running at one moment in time.

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.

Start the discussion at forums.toadworld.com