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:
|
| 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:
|
| QUEUE |
Reports on the work currently being done. One of the following values will be returned:
|
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