5.5 Viewing MTS Status

Team-Fly    

 
Oracle Net8 Configuration and Troubleshooting
By Jonathan  Gennick , Hugo Toledo
Table of Contents
Chapter 5.  Multi-Threaded Server


You have 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, as you might expect, there are a number of dynamic performance views that report MTS- related statistics.

5.5.1 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 d xxx in their name , and the shared server processes always have s xxx 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] 

5.5.2 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. Figure 5.4 describes the information returned for each dispatcher.

Figure 5.4. Output from Listener Control's SERVICES command
figs/n8c_0504.gif

The output shown in Figure 5.4 was taken with the Listener Control display mode set to COMPAT. The NORMAL and VERBOSE modes will result in very similar-looking output. Use of the RAW display mode, however, results in some rather unreadable output.

5.5.3 Dynamic Performance Views

Several V$ views, listed in Table 5.1, return information and statistics about multi-threaded server connections and processes.

Table 5.1. Dynamic Performance Views Relevant to Multi-Threaded Server

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 in Table 5.1 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. The full set of columns in these views is described in Appendix G.

5.5.3.1 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 :

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.

5.5.3.2 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 certainly 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.

5.5.3.3 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.

5.5.3.4 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:

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.

5.5.3.5 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.

5.5.3.6 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:

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.


Team-Fly    
Top
 


Oracle Net8 Configuration and Troubleshooting
Oracle Net8 Configuration and Troubleshooting
ISBN: 1565927532
EAN: 2147483647
Year: 2000
Pages: 120

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net