Use UNIX and Oracle Tools to Monitor Shared Server Connections

Monitoring Shared Server means looking at it from two different vantage points, and each answers questions the other cannot. At the operating system level, dispatchers and shared servers are just processes like any other, visible with the same tools you would use to watch anything else running on the host. Inside the database, a family of V$ views expose exactly what those processes are doing moment to moment: how busy each one is, how long requests are waiting, and whether the numbers you configured back in Lessons 2 and 4 are actually holding up under real load.

Each dispatcher and shared server process is a distinct OS process, named ora_d<NNN>_<SID> and ora_s<NNN>_<SID> respectively, exactly as covered in Lessons 1 and 3. A single command finds both:

ps -ef | grep ora_[ds]

For connection level detail rather than process level, netstat -an | grep <port>, or ss -tn on more current Linux systems, shows active TCP sessions to the listener and dispatcher ports directly. lsof -i TCP:<port> answers the same question by way of open sockets rather than the connection table, which is occasionally the more convenient of the two depending on what else you are already troubleshooting.

General system tools like top, vmstat, and sar remain useful for overall CPU, memory, and I/O trends on the host, but nothing about their use is specific to Shared Server beyond watching the process names above; a spike in top next to a busy ora_s003 process tells you which shared server to investigate further inside the database, not what it is actually doing.

None of these OS level tools can distinguish a shared server that is genuinely CPU bound from one that is simply waiting on a lock or on network I/O; they show you that a process is consuming resources, not why. That distinction is exactly what the data dictionary views below exist to answer, which is why an OS level tool is normally the first place you look, confirming that dispatcher and shared server processes exist and are running at all, rather than the last.

DESCRIBE any of these views directly in SQL*Plus for the authoritative, current column list on your own instance; what follows is the confirmed core of each, not an exhaustive reproduction, since a full column dump for half a dozen views teaches less than knowing which columns actually matter.

V$QUEUE holds PADDR, TYPE (COMMON for the single shared request queue, or DISPATCHER for one of the per-dispatcher response queues), QUEUED, WAIT, and TOTALQ, already put to work in Lessons 3 and 5 for measuring average wait time by protocol:

SELECT type, queued, wait, totalq
FROM v$queue
WHERE type IN ('COMMON', 'DISPATCHER');

V$DISPATCHER holds NAME, NETWORK, STATUS, MESSAGES, BYTES, BREAKS, OWNED, CREATED, IDLE, BUSY, LISTENER, and CONF_INDX, the last of which ties a specific dispatcher back to its DISPATCHERS configuration from Lesson 2:

SELECT name, network, status, messages, bytes, busy, idle
FROM v$dispatcher;

V$DISPATCHER_RATE reports rate statistics for dispatcher activity rather than raw totals: current, average, and maximum values across several distinct categories, including dispatch loop rate, message rate, and buffer and byte rates to shared servers and to clients tracked separately. Compare current and average rates against the maximum for each: values consistently sitting near the maximum suggest adding dispatchers, while values well below it suggest you could reduce them without hurting response time, the same conclusion Lesson 4's monitoring approach reaches from the shared server side.

V$SHARED_SERVER holds NAME, STATUS (including QUIT for a server that has terminated, already used in Lesson 4's active-count query), MESSAGES, BYTES, BREAKS, CIRCUIT, IDLE, BUSY, and REQUESTS:

SELECT name, status, requests, busy, idle
FROM v$shared_server;

V$CIRCUIT holds CIRCUIT, DISPATCHER, SERVER, SADDR, STATUS (NORMAL, BREAK, EOF, or OUTBOUND for a link to a remote database), QUEUE (COMMON, DISPATCHER, SERVER, or NONE for an idle circuit), MESSAGES, BYTES, and BREAKS. It is the view to reach for when the question is about one specific client connection's path through the architecture rather than a dispatcher or shared server's aggregate activity:

SELECT circuit, dispatcher, server, status, queue
FROM v$circuit;

A circuit's own QUEUE column is worth reading carefully alongside V$SESSION: a circuit sitting on NONE is simply an idle client connection, nothing to investigate, while a large number of circuits stuck on COMMON at once, all waiting to be picked up by a shared server, is the circuit level view of the exact same congestion V$QUEUE's WAIT column would already be showing you from the queue's side. Neither view replaces the other; V$QUEUE tells you congestion exists, V$CIRCUIT tells you which specific connections are caught in it. V$SHARED_SERVER_MONITOR holds MAXIMUM_CONNECTIONS, MAXIMUM_SESSIONS, SERVERS_STARTED, SERVERS_TERMINATED, and SERVERS_HIGHWATER, useful specifically for checking whether CIRCUITS, SHARED_SERVER_SESSIONS, or MAX_SHARED_SERVERS have ever actually been hit since the instance started, rather than just whether they were sized generously enough in theory:

SELECT maximum_connections, maximum_sessions, servers_highwater
FROM v$shared_server_monitor;

V$SESSION rounds this out at the session level: filtering on SERVER = 'SHARED' isolates sessions actually using Shared Server, exactly the check Lesson 7's troubleshooting scenario relied on to confirm a connection landed where it was supposed to.

Seeing how these columns actually look in practice helps more than any description. These are genuine SELECT * outputs against V$QUEUE and V$DISPATCHER:

SQL> select * from v$queue;

PADDR    TYPE           QUEUED       WAIT     TOTALQ
-------- ---------- ---------- ---------- ----------
00       COMMON              0          0         15
00       OUTBOUND            0          0          0
420548A4 DISPATCHER          0          1         15
42054B0C DISPATCHER          0          0          0
42054D74 DISPATCHER          0          0          0

SQL> select name, network, status, messages, bytes, idle, busy from v$dispatcher;

NAME  NETWORK                                    STATUS   MESSAGES      BYTES       IDLE       BUSY
----- ------------------------------------------ -------- ---------- ---------- ---------- ----------
D000  (ADDRESS=(PARTIAL=yes)(PROTOCOL=ipc))       WAIT          202      13320    4681834        174
D001  (ADDRESS=(PARTIAL=yes)(PROTOCOL=ipc))       WAIT          313      21904    4681771        240
D002  (ADDRESS=(PARTIAL=yes)(PROTOCOL=tcp))       WAIT       811151   92165767    4449027      23298
D003  (ADDRESS=(PARTIAL=yes)(PROTOCOL=tcp))       WAIT       660608   72314081    4490706      19130

Reading the first example, every row shows zero queued items and near-zero wait, a healthy, lightly loaded instance; if the DISPATCHER rows instead showed climbing WAIT values, that would point to too few dispatchers for that protocol, exactly the sizing question Lesson 2's formula addresses. In the second example, dispatchers D002 and D003, on TCP, show far more messages, bytes, and busy time than D000 and D001, on IPC, which is expected: IPC connections are typically local, low volume administrative or background traffic, while the TCP dispatchers are carrying the bulk of real client load. If one TCP dispatcher's BUSY time badly outpaced a sibling handling the same protocol, that imbalance, not just the raw totals, would be the more interesting signal.

A short script combining SQL*Plus with the queries above turns one-off checks into something you can run on a schedule:

#!/bin/bash
sqlplus -s / as sysdba <<EOF
SET LINESIZE 150
SET PAGESIZE 100
SELECT name, status, requests, busy, idle FROM v\$shared_server;
SELECT name, network, status, messages, bytes FROM v\$dispatcher;
EXIT;
EOF

For history rather than a live snapshot, Automatic Workload Repository reports capture shared server and dispatcher activity over time, the same way they capture everything else about instance performance:

@?/rdbms/admin/awrrpt.sql

Active Session History reports session level activity specifically, useful for tying a particular slow period back to which sessions, shared server or otherwise, were actually running at the time:

@?/rdbms/admin/ashrpt.sql

Oracle Enterprise Manager wraps most of this in a graphical interface for teams that prefer that over ad hoc SQL, and the trace files and alert log under $ORACLE_BASE/diag are still worth checking directly when something in the shared server architecture, a dispatcher failing to start, for instance, needs more detail than any V$ view captures.

Worth deciding upfront: which of these belongs in a scheduled script versus a one-off check during active troubleshooting. The shell script above, or an AWR report generated on a schedule, is well suited to establishing a baseline, what normal looks like for this instance at this time of day, so that an actual incident stands out clearly against it later. Querying V$CIRCUIT for one specific stuck connection, by contrast, is inherently a one-off check; scheduling it doesn't make sense the way scheduling a shared server summary does, since the whole point is investigating something unusual happening right now.

Every parameter this lesson's queries help you tune, DISPATCHERS, SHARED_SERVERS, MAX_SHARED_SERVERS, and the rest, applies dynamically through ALTER SYSTEM, as established throughout this module, with no restart required. That persistence still depends on the instance using a server parameter file rather than a traditional text based PFILE; a PFILE, the classic init.ora file, remains fully supported, it just cannot be written to directly by ALTER SYSTEM. The initialization parameters page in the database architecture course covers PFILE and SPFILE administration in more depth.

None of these tools replace judgment about what the numbers mean; a busy dispatcher on its own is not a problem, an instance-wide pattern of dispatchers running consistently near their maximum rate while requests queue up in V$QUEUE is. Watching the operating system and the data dictionary together, rather than either alone, is what turns a snapshot into an actual diagnosis.

The next lesson explains how to identify contention in Oracle Shared Server.

Before moving on to the next lesson, click the Quiz link below to check your mastery of shared server monitoring.
Monitoring Shared Server - Quiz