Friday, March 26, 2010

Shared Server (MTS) Diagnostics

Architecture, of the Shared Server Database Configuration

The best position to be in will always be based on understanding. The components of the Shared Server database configuration consist of the Dispatchers and the Shared Servers. These components run as separate processes in the operating system (or threads in some operating systems). They interact with each other through the use of a Common Queue (CQ - also known as the Virtual Queue) and individual Dispatcher Queues. Both queues reside in the Shared Global Area (SGA) and are sized automatically by the database itself. Another component of Shared Server is not a process but an abstraction of the user session(more of an owned pointer), called a Virtual Circuit (VC). The communication between the Dispatchers and Shared Servers is primarily done by passing ownership of a Virtual Circuit from one to another.





The stages that Shared Server goes through are quite simple. After the client sends the connection request to the Listener, it will either redirect or hand off (called warm hand-off) the connection to the Dispatcher (the Dispatcher does not necessarily need to be on the same host as the Listener). Once the client has connected to a Dispatcher it stays connected to that Dispatcher. Before the client completes the database log in, the Dispatcher associates a Virtual Circuit (VC) for that database session. There exists exactly one row in the VC view (V$CIRCUIT) for each client connection. This view also shows the current status of the client's VC. Once the VC has been associated with the database session, the client will complete the database logon by passing the username and password to the Dispatcher. This request, as part of the VC for that new session, will be placed in the Common Queue where the first available Shared Server will complete the logon. Once each phase of the logon has completed, the Shared Server will pass the VC back to the Dispatcher, which then passes the response back to the Client (this actually takes several round trips to the client, in just the same manner as if it was a Dedicated connection).


Once the logon has completed, the client starts a normal conversation with the database. When the client makes a request to the database, it is the Dispatcher that picks up this request from the operating system's network protocol. The Dispatcher then determines which client session the request came from (remember that a Dispatcher can be configured for Connection Pooling and Multiplexing: see the Net Administration Guide for more information on those configurations), tags that sessions' VC that there is a new message (there is also a pointer to that session buffer in the VC) and places the VC in the Common Queue. The CQ is serviced by the Shared Servers on a first-in-first-out basis. The next available Shared Server pulls the VC from the CQ and processes the request. Part of the VC structure is the identity of the Dispatcher that created it (and which client is connected to it). When the Shared Server is finished processing the request, it writes the output to the session buffer, changes the VC's ownership back to the Dispatcher that created it, places the VC into that Dispatcher's queue, and posts the Dispatcher it has something in its queue. The Dispatcher then checks its queue and sends what is in the session buffer through the operating system network protocol back to the Client.


In the case where there is a request for a Database Link, it is the Shared Server process that will, from the link definition, create an outbound VC and place it into the least loaded Dispatcher's queue (not necessarily the same Dispatcher the Client is connected to). This Dispatcher then logs into the remote database and passes the query to it for processing. Once the remote database responds, the Dispatcher then places the VC back into that Shared Server's ownership.


The Dispatchers are not limited to just the Oracle Net protocol. They also are able to understand FTP, HTTP(S), WebDAV, IIOP, SMTP, and TCP protocols.



The main views containing Shared Server information include:

  • V$CIRCUIT
  • V$DISPATCHER
  • V$DISPATCHER_CONFIG
  • V$DISPATCHER_RATE
  • V$QUEUE
  • V$SESSION
  • V$SHARED_SERVER
  • V$SHARED_SERVER_MONITOR



Dispatchers: The Number Of Dispatchers

The number of Dispatchers present in a Shared Server database configuration may vary from zero up to the system INIT.ORA parameter of MAX_DISPATCHERS. The initial number of Dispatchers created at instance startup is the value defined by the DISPATCHERS parameter in the system INIT.ORA and can exceed the MAX_DISPATCHERS value (as of version 10). Both parameters can be altered at runtime by ALTER SYSTEM commands (version 9.0 onwards, consult the SQL Reference manual for the particular version being run).


It is the PMON database background process that starts or stops any background processes, such as Dispatchers and Shared Servers. Dispatchers are not dynamically started or stopped, but must be manually maintained. Apart from restarting the database in order for the database to take on a new value set in the INIT.ORA, the following command may also be issued while the database is running:


SQL> ALTER SYSTEM SET DISPATCHERS='string';


Where 'string' is a valid setting for the DISPATCHERS parameter. For example:


SQL> ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP )(DISPATCHERS=2)';
SQL> ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCPS)(DISPATCHERS=2)';


PMON creates or destroys Dispatchers and then informs the Listener with their current state. PMON also updates the Listener about every ten seconds (depending on system load and other factors) with the current number of sessions and database load. To have PMON update the Listener with a change outside its normal cycle (as long as the LOCAL_LISTENER and REMOTE_LISTENER parameters are properly set in the system INIT.ORA) use this command:


SQL> ALTER SYSTEM REGISTER;


One can then query then Listener for the new services that have been registered:


#> lsnrctl services 

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 25-MAY-2006 17:26:51 
Copyright (c) 1991, 2005, Oracle. All rights reserved. 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) 
Services Summary... 
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 
Service "V10R2.us.oracle.com" has 1 instance(s). 
  Instance "V10r2", status READY, has 5 handler(s) for this service... 
    Handler(s): 
      "DEDICATED" established:1 refused:0 state:ready 
          LOCAL SERVER 
      "D000" established:0 refused:0 current:0 max:992 state:ready  
         DISPATCHER  
         (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38049)) 
      "D001" established:0 refused:0 current:0 max:992 state:ready 
         DISPATCHER  
         (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38050)) 
      "D002" established:0 refused:0 current:0 max:992 state:ready 
         DISPATCHER  
         (ADDRESS=(PROTOCOL=tcps)(HOST=anrique.us.oracle.com)(PORT=38051)) 
      "D003" established:0 refused:0 current:0 max:992 state:ready 
         DISPATCHER  
         (ADDRESS=(PROTOCOL=tcps)(HOST=anrique.us.oracle.com)(PORT=38052)) 
The command completed successfully 


From this output we can see that there are four dispatchers, two using TCP and two using TCPS. The TCP ports that are assigned to the Dispatchers were allocated by the operating system. To specify what port to use, each Dispatcher has to be configured individually specifying the port number in the ADDRESS parameter section (see the Oracle Net Administration Guide on how to assign ports to Dispatchers).


Dispatchers can also be configured for a particular service. By default, each Dispatcher will service all SERVICE_NAMES and the DB_NAME. Dispatchers can be set up to only service a specific Service.


SQL> ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=1)(INDEX=1)(SERVICE=V10R2_DISP)'; 

SQL> ALTER SYSTEM REGISTER; 


#> lsnrctl services 
LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 25-MAY-2006 18:54:39 
Copyright (c) 1991, 2005, Oracle. All rights reserved. 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) 
Services Summary... 
  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 
Service "V10R2_DISP" has 1 instance(s). 
  Instance "V10r2", status READY, has 1 handler(s) for this service... 
   Handler(s): 
      "D003" established:0 refused:0 current:0 max:992 state:ready 
          DISPATCHER  
          (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38731)) 
Service "V10r2" has 1 instance(s). 
   Instance "V10r2", status READY, has 4 handler(s) for this service... 
      Handler(s): 
      "DEDICATED" established:0 refused:0 state:ready 
         LOCAL SERVER 
      "D002" established:0 refused:0 current:0 max:992 state:ready 
         DISPATCHER  
         (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38697)) 
      "D001" established:0 refused:0 current:0 max:992 state:ready 
         DISPATCHER  
         (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38696)) 
      "D000" established:0 refused:0 current:0 max:992 state:ready 
         DISPATCHER  
         (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38692)) 
The command completed successfully 


The command added one Dispatcher, D003, to service the SERVICE_NAME of V10R2_DISP (specified by the SERVICE clause). To get this Dispatcher to service V10R2_DISP the INDEX clause was used (INDEX can also be used to "separate" Dispatchers from other TCP Dispatchers). This new Dispatcher gets a new INDEX:


SQL> SELECT CONF_INDX, NAME, NETWORK FROM V$DISPATCHER; 

CONF_INDX NAME NETWORK 
--------- ---- ---------------------------------------------------------------- 
        0 D000 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38692)) 
        0 D001 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38696)) 
        0 D002 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38697)) 
        1 D003 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=38731)) 


If the desire is to add more Dispatchers for a particular protocol, either issue the ALTER SYSTEM command specifying the full DESCRIPTION of the Dispatcher, or the Dispatcher's INDEX. For example, below are three Dispatchers configured for TCP and one configured for TCPS and there is a need to add one more TCP Dispatcher:


SQL> SELECT CONF_INDX, NAME, NETWORK from V$DISPATCHER; 

CONF_INDX NAME NETWORK 
--------- ---- ---------------------------------------------------------------- 
        0 D000 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=1500)) 
        1 D001 (ADDRESS=(PROTOCOL=tcps)(HOST=anrique.us.oracle.com)(PORT=1501)) 
        0 D002 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=48236)) 
        0 D003 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=48237)) 

SQL> ALTER SYSTEM SET DISPATCHERS='(INDEX=0)(PROTOCOL=TCP)(DISPATCHERS=4)'; 

SQL> SELECT CONF_INDX, NAME, NETWORK FROM V$DISPATCHER; 

CONF_INDX NAME NETWORK 
--------- ---- ---------------------------------------------------------------- 
        0 D000 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=1500)) 
        1 D001 (ADDRESS=(PROTOCOL=tcps)(HOST=anrique.us.oracle.com)(PORT=1501)) 
        0 D002 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=48236)) 
        0 D003 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=48237)) 
        0 D004 (ADDRESS=(PROTOCOL=tcp)(HOST=anrique.us.oracle.com)(PORT=48248)) 


Dispatchers: Shutting Down Dispatchers

If the need is to shut down or reduce the number of Dispatchers by using the ALTER SYSTEM statement, the database can decide, or a specific Dispatchers can be chosen. To identify the name of the specific Dispatcher process to shut down, use the V$DISPATCHER view.


SQL> SELECT CONF_INDX, NAME, NETWORK FROM V$DISPATCHER; 

CONF_INDX NAME NETWORK 
--------- ---- ----------------------------------------------------------------- 
        0 D000 (ADDRESS=(PROTOCOL=tcp) (HOST=anrique.us.oracle.com)(PORT=38049)) 
        0 D001 (ADDRESS=(PROTOCOL=tcp) (HOST=anrique.us.oracle.com)(PORT=38050)) 
        1 D002 (ADDRESS=(PROTOCOL=tcps)(HOST=anrique.us.oracle.com)(PORT=38051)) 
        1 D003 (ADDRESS=(PROTOCOL=tcps)(HOST=anrique.us.oracle.com)(PORT=38052))


Each Dispatcher is uniquely identified by a name of the form Dnnn (where n is a number in the 0-9 range). In Unix, the Dispatcher process will have a name like ora_dnnn_sid. For Windows, the Dispatchers run as threads and are only visible using certain utilities, but the Oracle views will be the same. The Dispatchers are grouped by CONF_INDX based on the DESCRIPTION (or ADDRESS and PROTOCOL).

To shut down Dispatcher D002, issue the following statement:

SQL> ALTER SYSTEM SHUTDOWN 'D002';


The Dispatcher stops accepting new connections and will wait until all of the sessions it handles are disconnected before shutting down.


For a more "immediate" shutdown of the Dispatcher (it is in a bad state or it won't shutdown with the above command) issue this command:


SQL> ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';


The IMMEDIATE keyword stops the Dispatcher from accepting new connections and immediately terminates all existing connections through that Dispatcher. After all sessions are cleaned up, the Dispatcher process shuts down.


When a Dispatcher is shut down, other Dispatchers will not inherit the name, so it is possible to see some Dispatchers missing. When starting up a new Dispatcher, it will obtain the next open Dispatcher name. In the case where TCPS D002 Dispatcher was shut down, and a new Dispatcher for TCP was started, the new Dispatcher will be called D002.

Dispatchers: Monitoring Performance

In general, Dispatchers will not be very busy because their tasks are relatively quick to complete. In the example below, Dispatchers are less than 1% busy.


SQL> SELECT NAME "NAME", SUBSTR(NETWORK,1,23) "PROTOCOL", OWNED, 
   2 STATUS "STATUS", (BUSY/(BUSY + IDLE)) * 100 "%TIME BUSY" 
   3 FROM V$DISPATCHER; 

NAME PROTOCOL                OWNED   STATUS %TIME BUSY 
---- ----------------------- ------- ------ -------------- 
D000 (ADDRESS=(PROTOCOL=tcp)      26   SEND .358392479 
D001 (ADDRESS=(PROTOCOL=tcp)       3   WAIT .251346468 
D002 (ADDRESS=(PROTOCOL=tcp)       5   WAIT .230378452 
D003 (ADDRESS=(PROTOCOL=tcp)       5   WAIT .563705148 
D004 (ADDRESS=(PROTOCOL=tcp)       0   WAIT 0


The OWNED column of V$DISPATCHER view shows the number of clients currently connected to each Dispatcher. In the above example, a D000 has 26 clients connected. D000 is in the process of sending a message to a client as is shown by the SEND status. D004 has OWNED = 0 because it has just been started using the following command:


SQL> ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=5)';

Dispatchers: Performance

One perspective for interpreting Dispatcher performance is measuring the wait times in the various queues by querying the view V$QUEUE.


SQL> SELECT D.NAME, Q.QUEUED, Q.WAIT, Q.TOTALQ, 
   2 DECODE(Q.TOTALQ,0,0,(Q.WAIT/Q.TOTALQ)/100) "AVG WAIT" 
   3 FROM V$QUEUE Q, V$DISPATCHER D 
   4 WHERE D.PADDR = Q.PADDR; 

NAME QUEUED   WAIT TOTALQ AVG WAIT 
---- ------ ------ ------ ------------ 
D000      0  27800  28152 .00987496448 
D001      0  14304  10158 .01408151210 
D002      0  33390  12366 .02700145560 
D003      0  10833   9217 .01175328198 


SQL> SELECT Q.TYPE, Q.QUEUED, Q.WAIT, Q.TOTALQ, 
2 DECODE(Q.TOTALQ,0,0,(Q.WAIT/Q.TOTALQ)/100) "AVG WAIT" 
3 FROM V$QUEUE Q 
4 WHERE TYPE = 'COMMON'; 

TYPE    QUEUED WAIT   TOTALQ AVG WAIT 
------- ------ ------ ------ ------------ 
COMMON       0 222657 43395  .05130936743 


In the above example, the WAIT column is the total amount of time all requests have waited in the particular queue. The TOTALQ column is the total number of requests in a queue since the startup of the database. The AVG WAIT denotes the average wait (in seconds) per queued request.


The row with the TYPE of COMMON represents the Common Queue. The CQ holds all client requests to be processed by the Shared Servers. Please note that V$QUEUE view is not related to the Oracle Streams Advance Queuing feature.

Shared Servers: Setting the Quantity of Shared Servers

The quantity of Shared Server processes will vary between INIT.ORA parameters SERVERS and MAX_SERVERS. Initially, the MAX_SERVERS value should be set to some estimated maximum number. For the initial setting, it can be set to the maximum number of expected sessions on the database (this is just a suggestion as it could also be set to far less). The effect of setting this parameter to a large value only affects the size of the Common Queue. This parameter can be changed dynamically by issuing the command:


SQL> ALTER SYSTEM SET MAX_SHARED_SERVERS = {number}; 


Setting the initial value for SERVERS is also estimated. It can be set to some number under the setting of MAX_SHARED_SERVERS. But once the system is running under production load, the parameter SERVERS can be changed to accommodate the load. The Oracle Database Performance Tuning Guide explains how to monitor V$QUEUE to determine if SERVERS should be increased:


SQL> SELECT DECODE(TOTALQ, 0, 'No Requests', 
   2 WAIT/TOTALQ || ' HUNDREDTHS OF SECONDS') "AVERAGE WAIT TIME PER REQUESTS" 
   3 FROM V$QUEUE 
   4 WHERE TYPE = 'COMMON'; 

AVERAGE WAIT TIME PER REQUEST 
----------------------------- 
.090909 HUNDREDTHS OF SECONDS 


If the system is suffering from high SYS load due to having to create and destroy many shared servers, then SERVERS might be set to one plus the number in SERVERS_HIGHWATER that is found in the V$SHARED_SERVER_MONITOR view (the name of the view is V$MTS in 8.1 and before).


SQL> SELECT MAXIMUM_CONNECTIONS "MAX CONN", MAXIMUM_SESSIONS "MAX SESS", 
   2 SERVERS_STARTED "STARTED", SERVERS_TERMINATED "TERMINATED", 
   3 SERVERS_HIGHWATER "HIGHWATER" 
   4 FROM V$SHARED_SERVER_MONITOR; 

MAX CONN MAX SESS STARTED TERMINATED HIGHWATER 
-------- -------- ------- ---------- --------- 
     100      100       0          0        20


Setting this parameter based on the SERVERS_HIGHWATER value will reduce the expense of process creation and match the known maximum of Shared Servers. These are just suggestions as to a starting point to configuring Shared Server. The Shared Server views should be monitored to make sure the settings are appropriate for this instance. Monitoring the instance initially to make sure the settings are correctly configured will assure a well-tuned system.

Shared Server Performance

Shared Servers are created by PMON. Upon instance startup, PMON will create them according to the value of the SHARED_SERVERS parameter. If more SHARED_SERVERS are needed, PMON will create them up to MAX_SHARED_SERVERS to meet the need. PMON will terminate idle Shared Servers until the number goes back to SHARED_SERVERS. When measuring the performance of the Shared Servers, it is normal to see the lower numbered Shared Servers to be busier then the higher numbered ones.


SQL> SELECT NAME "NAME", PADDR, REQUESTS,
   2 (BUSY/(BUSY + IDLE)) * 100 "%TIME BUSY", STATUS
   3 FROM V$SHARED_SERVER;

NAME PADDR              REQUESTS %TIME BUSY STATUS
---- ---------------- ---------- ---------- ----------------
S000 000000030107D73B      51525 9.19084132 WAIT(RECEIVE)
S001 000000030107B233      26817 5.07654792 WAIT(COMMON)
S002 000000030107B3BE       6362 1.44008509 WAIT(RECEIVE)
S006 000000030108574C         54 86.9953920 WAIT(RECEIVE)
S008 000000030107B549          1 99.9994096 WAIT(ENQ)


In the above example, all the Shared Servers are between 1% and 99% busy. Shared Server S008 is very busy processing a single client request and Shared Server S000 has been busy handling numerous smaller requests. In general, the S000 Shared Server will always be the busiest and could easily be 100% busy all the time. This is by design.


The reason that S003-S005 and S007 are not listed is because the SHARED_SERVER parameter was set to 3 so PMON removed those Shared Servers because they went idle long enough to be removed. The idle interval cannot be set, nor does it need to be as it is more efficient to not have to create a Shared Server. S006 and S008 are not idle so they will exist as long as there is work for them to do.


In the case where there is a gap in the %TIME BUSY, such as is illustrated above where higher numbered Shared Servers S006 and S008 are nearly 100% used. This could be due to some sessions having so much work to do that a Shared Server has been dedicated to that particular session. It is sessions like this that should be found and forced to connect with a Dedicated server processes. Such heavy sessions have enough continuous workload that the service time the Dispatcher adds may slow them down.


The STATUS column of the V$SHARED_SERVER view provides useful information about WAIT status. In particular, the WAIT(ENQ) status tells the DBA that the user is waiting for a lock resource, and in rare cases, acts as an alert for a deadlock situation.


An overview of server creation and termination and high-water mark is available from the V$SHARED_SERVER_MONITOR view.


SQL> SELECT MAXIMUM_CONNECTIONS "MAX CONN", MAXIMUM_SESSIONS "MAX SESS", 
   2 SERVERS_STARTED "STARTED", SERVERS_TERMINATED "TERMINATED", 
   3 SERVERS_HIGHWATER "HIGHWATER" 
   4 FROM V$SHARED_SERVER_MONITOR; 

MAX CONN MAX_SESS STARTED TERMINATED HIGHWATER 
-------- -------- ------- ---------- --------- 
      29       29       1          1         5 


The MAXIMUM_CONNECTIONS is the value of the maximum number of Virtual Circuits in use at one time.


The MAXIMUM_SESSIONS is the highest number of Shared Server sessions in use at one time since the instance started.


The SERVERS_STARTED and SERVERS_TERMINATED columns maintain a running total of Shared Server process creation and termination by PMON (but do not include the number set in the SHARED_SERVERS parameter).


The SERVERS_HIGHWATER value holds the high-water mark for the Shared Server count since the instance startup.


These statistics are useful indicators to check if SERVERS is set too low or too high. If the SERVERS_STARTED or SERVERS_TERMINATED are zero, this is an indication that too many Shared Servers may have been configured. Similarly, if the values of SERVERS_STARTED and SERVERS_TERMINATED grow quickly, the number for SHARED_SERVERS is likely to be too low and should be set to SERVERS_HIGHWATER + 1 (the "+ 1" is for good measure and has no intrinsic meaning).

Virtual Circuits and Sessions

The SERVER column in the V$SESSION view shows the type of Server that is currently servicing each session.


SQL> SELECT SERVER, SUBSTR(USERNAME,1,15) "USERNAME",
   2 SUBSTR(OSUSER,1,8) "OS USER", SUBSTR(MACHINE,1,7) "MACHINE",
   3 SUBSTR(PROGRAM,1,35) "PROGRAM"
   4 FROM V$SESSION
   5 WHERE TYPE='USER';

SERVER    USERNAME        OS USER  MACHINE PROGRAM
--------- --------------- -------- ------- ---------------------------
DEDICATED SYS             oracle   anrique sqlplus@anrique (TNS V1-V3)
NONE      SCOTT           george   US-ORAC sqlplus.exe
SHARED    SCOTT           bill     US-ORAC sqlplus.exe
NONE      SCOTT           tina     US-ORAC sqlplus.exe
NONE      BILL            harry    US-ORAC sqlplus.exe
NONE      SCOTT           richard  US-ORAC sqlplus.exe
NONE      SCOTT           kevin    US-ORAC sqlplus.exe
NONE      SCOTT           andy     US-ORAC sqlplus.exe
NONE      SCOTT           henry    US-ORAC sqlplus.exe
NONE      SCOTT           jill     US-ORAC sqlplus.exe
NONE      SCOTT           mary     US-ORAC sqlplus.exe
DEDICATED                 oracle   anrique oracle@anrique (J000)
NONE      SCOTT           sally    US-ORAC sqlplus.exe


In the above example, DEDICATED means that client is connected with a dedicated server process. Shared Server connections appear as NONE or SHARED depending on whether a task is currently being serviced by a Shared Server or not. In this case only the OS User "bill" is being serviced by a Shared Server.


The V$CIRCUIT view provides more detailed information about usage of circuits by each session.


SQL> SELECT SADDR, CIRCUIT, DISPATCHER, SERVER, SUBSTR(QUEUE,1,8) "QUEUE", 
   2 WAITER FROM V$CIRCUIT;

SADDR            CIRCUIT          DISPATCHER       SERVER           QUEUE    WAITER
---------------- ---------------- ---------------- ---------------- -------- ----------------
00000003010BC87B 00000003013CE6BC 000000030107B9EA 00               NONE     00
00000003010BAA60 00000003013CEB88 000000030107BE8B 00               NONE     00
00000003010C1198 00000003013CEDEE 000000030107B85F 00               NONE     00
00000003010BD9AF 00000003013D05EA 000000030107B85F 000000030107B3BE SERVER   000000030107B3BE
00000003010C04B1 00000003013D0D1C 000000030107B85F 00               NONE     00
00000003010A5AEA 00000003013D5C42 000000030107B9EA 000000030107F92D SERVER   000000030107F92D


The DISPATCHER column identifies the Oracle process ID for the Dispatcher Associated with the session. The SERVER column provides the Oracle process ID for the Shared Server currently servicing the client session, and zero if the session is not being serviced. For the QUEUE column, NONE represents the circuit is idle, SERVER means it is currently being serviced by a Shared Server, DISPATCHER means it is being serviced by a Dispatcher, and COMMON means it is on the Common Queue waiting to be picked up by a Shared Server.


The WAITER column is the Oracle process ID of the process that is currently waiting for data to appear in the Circuit. It will contain "00" when no database operation is in progress, otherwise it will list the Oracle process id for the Dispatcher or a Shared Server. Dispatchers are very quick to complete their work, so quick that it is rare to catch a glimpse of a Dispatcher in the WAITER queue.

References

Shared Server information
Oracle support