Use the V$QUEUE View to Identify Contention

V$QUEUE's relevant columns are TYPE, COMMON for the single shared request queue or DISPATCHER for one of the per-dispatcher response queues, along with QUEUED, WAIT, and TOTALQ, all confirmed in Lesson 8. A single query with a computed average gives a clear read on both queue types at once:

SELECT type, queued, wait, totalq,
       DECODE(totalq, 0, 0, wait/totalq) avg_wait_hsec
FROM v$queue;

Rising QUEUED, or a growing avg_wait_hsec, means a backlog. On the COMMON row, that points toward shared servers not keeping up with the requests being handed to them; on a DISPATCHER row, it points toward that dispatcher's protocol needing more capacity, exactly the distinction Lesson 9 walked through in detail with its own worked example. For a per-connection view rather than a per-queue one, V$CIRCUIT's own QUEUE column, covered in Lesson 9, shows SERVER for a circuit currently being serviced and COMMON for one still waiting its turn; that's the view to check when the question is about one specific stuck connection rather than overall queue depth across the instance.
It's worth being deliberate about which of these three, V$QUEUE's COMMON row, its DISPATCHER rows, or V$CIRCUIT, you check first, since they answer questions at different levels of granularity. Start with V$QUEUE for the instance-wide picture; if either row type looks elevated, narrow down with V$DISPATCHER or V$SHARED_SERVER to find which specific process is under strain, as covered in Lesson 9; only reach for V$CIRCUIT once you already suspect one particular connection rather than a general trend.

This query, unlike much of the surrounding material, needed no correction:

SELECT network                               "Protocol",
       DECODE( SUM(totalq), 0, 'No Responses',
       SUM(wait)/SUM(totalq) || ' hundredths of seconds')
      "Average Wait Time per Response"
FROM
   v$queue q,
   v$dispatcher d
WHERE
   q.type = 'DISPATCHER'
   AND
   q.paddr = d.paddr
GROUP BY network;

It returns the average time a response waits in each dispatcher's response queue before being routed back to the client, grouped by network protocol, with DECODE handling protocols that haven't had any responses queued at all yet rather than returning a division-by-zero error. A result might look like:

Protocol  Average Wait Time per Response
--------  ------------------------------
tcps      .1739130 hundredths of seconds
tcp       No Responses

This result says that TCPS responses have waited an average of 0.17 hundredths of a second, essentially instantaneous, while no TCP responses have been queued at all during the collection period, not a problem, just an absence of TCP traffic so far. If the average wait time for a specific protocol keeps climbing as the application runs rather than staying flat like this, adding dispatchers for that protocol, using the sizing formula from Lesson 2, is usually the fix. Querying this table by default requires SYS or a suitably privileged account, the same dictionary access requirement covered in Lesson 9.

If Shared Server itself is the problem rather than any one dispatcher or protocol, stopping new shared connections immediately and falling back to dedicated servers for anything new is a single statement:

ALTER SYSTEM SET SHARED_SERVERS = 0;

This is the same mechanism Lesson 7's "Turning It Back Off, Cleanly" section already covered in depth, not a separate emergency-only command; existing shared connections are not cut off immediately, they drain naturally as those sessions disconnect, exactly as described there. Reaching for this is a reasonable last resort when contention has gotten bad enough that diagnosing the specific cause can wait until dedicated connections have restored acceptable response time, but it's a blunt instrument. Once things are stable again, the diagnostic approach earlier in this lesson, checking which specific queue or dispatcher was actually under strain, is what tells you whether to simply turn Shared Server back on as it was or adjust something first.

V$DISPATCHER gives general dispatcher information; V$DISPATCHER_RATE gives rate statistics specifically, reported with three prefixes: CUR_ for the current session, AVG_ for the average since statistics collection began, and MAX_ for the maximum over that same period.

Compare current values against the maximums. If current and average rates sit well below the maximum, and response time already looks acceptable from the application side, the dispatcher count is probably about right for this workload, or could even be reduced without anyone noticing. If current and average rates are close to the maximum, more dispatchers are likely needed before response time actually starts to suffer, not after. Checking this during both light and heavy usage periods, rather than at just one point in the day, and adjusting based on the pattern that emerges across both rather than a single reading, is the same rule of thumb that has applied to this view since it was first introduced; nothing about the underlying logic has changed even though the architecture around it has been renamed twice since, first from Multi-Threaded Server to Shared Server, and the parameters along with it as covered in Lesson 3.

Interpretation genuinely does vary by platform and by application type, which is worth taking seriously rather than treating as a disclaimer to skim past: a rate that looks alarming on one system's hardware and workload mix can be entirely unremarkable on another's. If you need a load pattern to compare against and don't want to wait for one to occur naturally, running a stress test against a test instance and polling V$DISPATCHER_RATE periodically during it is a reasonable way to establish what "busy" actually looks like on your specific system before you need that number during a real incident.

If you have access to an Oracle database server, it's worth running the V$DISPATCHER and V$QUEUE queries from this lesson and the last one directly against it rather than only reading about them. Worth checking specifically:

1. How many listeners are defined?
2. How many dispatchers are defined for each protocol?
3. How many shared server processes are defined, and does that match what Lesson 4's sizing guidance would suggest for the actual workload?
4. Are the V$DISPATCHER statistics reasonable for the load the instance is actually under, or is something clearly out of proportion?
5. How do the response queue wait times look, both right now and compared to what they were an hour ago?

Ten lessons back, Module 5 opened with a simple distinction: a dedicated server per client is expensive at scale, and Shared Server exists to fix that by pooling processes instead. Everything since has been working out the consequences of that one idea.

Lessons 1 through 4 built the architecture itself: dispatchers, the common queue, shared servers scaling dynamically between a floor and a ceiling that PMON manages on its own, and the dispatcher parameters that control all of it. Lesson 5 extended that architecture across multiple listeners for load balancing, both the client's own randomization and the server's least-loaded selection working together. Lessons 6 and 7 introduced a genuinely separate technology, DRCP, that solves an adjacent but distinct problem, pooling dedicated-style connections through a connection broker rather than pooling server processes the way Shared Server itself does. Lessons 8 through 10 closed the loop with how to actually watch all of it: which view answers which question, and how to tell a dispatcher problem from a shared server problem from a problem that was never inside Shared Server at all.

If one thread runs through all ten lessons, it's that Oracle's own documentation for this architecture has stayed remarkably stable under a lot of surface-level churn. The parameters were renamed once, at Oracle9i, and the views and their columns have barely moved since; a query written against `V$DISPATCHER` decades ago still runs correctly today, which is not something you could say about most other parts of a database system over that same span of time. What actually changes release to release is what sits alongside Shared Server, connection pooling technologies chief among them, not the core dispatcher and queue model this module spent most of its time on.

That stability cuts both ways for anyone learning this material now. It means the underlying architecture is genuinely worth understanding in depth rather than treating as something likely to be obsolete again in a few years. It also means that when a tutorial, generated or otherwise, insists something here works differently than what these ten lessons have shown, verified column by column and parameter by parameter against current Oracle documentation, that claim deserves real skepticism rather than automatic trust. Consistency across decades of releases is exactly the kind of signal that makes an outlying claim easy to check and, more often than this module's source material would suggest, easy to disprove.