Oracle DBA Lesson 42B — Measure Shared Queues, Then Drain the Lab

Shared-server queue delay helps you decide where to investigate: dispatching, execution, resource contention, or a configured limit. Compare the same workload across a measured interval, identify the constraint, and test a change against response time and available resources. End the experiment by stopping new shared connections, letting existing lab clients finish, and restoring the captured configuration.

Oracle DBA Lesson 42B — Measure Shared Queues, Then Drain the Lab

This follows an already enabled, bounded shared-server lab with a fresh client confirmed as SHARED and an independent dedicated management connection. The examples assume Oracle Database 19c, a single-instance CDB on Linux, SQL*Plus, and an isolated disposable database with no other clients or required dispatcher services. All values below are authored teaching examples. No database commands were executed to produce them.

Take comparable interval samples

From the authorized observer in CDB$ROOT, record the sample time and the instance and startup identity, then read the views in the same order at both endpoints:

SELECT SYSTIMESTAMP AS sample_time, instance_name, startup_time
FROM v$instance;

SELECT con_id, name, paddr, status, busy, idle
FROM v$dispatcher;

SELECT con_id, paddr, type, queued, wait, totalq
FROM v$queue;

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

Capture sample A, run a small repeatable workload for a measured interval such as 60 seconds, then capture sample B. Keep the actual timestamp for each query. These changing views are read at slightly different moments, so a sample label does not freeze every row. Match the instance and startup, PADDR, queue TYPE, and CON_ID before subtracting counters. Process names alone can be reused after a process exits. See dynamic performance views and instance tuning with performance views.

V$QUEUE.TYPE='COMMON' describes the queue processed by shared servers. A DISPATCHER queue holds work for a dispatcher, including response delivery. QUEUED is the number of items currently queued. It is a point-in-time gauge. WAIT and TOTALQ are accumulating counters, and TOTALQ counts items ever queued. See V$QUEUE.

Worked queue-wait calculation

The following two observations describe the same authored common-queue row, with the same PADDR, TYPE, container, and instance startup. The interval is 60 seconds.

Observation	WAIT, hundredths of a second	TOTALQ, items	QUEUED, current items
A	1,200	200	1
B	1,500	220	3
Counter difference	300	20	Gauge values stay separate

For comparable, increasing counters:

interval mean queue wait = ΔWAIT / ΔTOTALQ
                        = (1500 - 1200) / (220 - 200)
                        = 300 / 20
                        = 15 hundredths of a second per item
                        = 0.15 seconds = 150 milliseconds per item

One WAIT unit is 0.01 second. Multiplying ΔWAIT / ΔTOTALQ by 10 expresses the average in milliseconds. The 60-second observation interval defines which differences you compare. Dividing by 60 would give a time-accumulation rate rather than this mean wait per item. The average describes the counter changes across the endpoints. Calls already present at the boundary and unfinished waits can affect a short interval, so repeat the observations with queue depth and client response time. See queue counter definitions and units.

If ΔTOTALQ=0, report the interval mean as unavailable and inspect the current queue and ongoing work. Do not divide by zero or label it zero latency. If either difference is negative, the instance restarted, a row disappeared and reappeared, or the row identity changed. Discard that comparison and capture a new baseline. A large lifetime total describes accumulated activity. Interval differences answer the current workload question.

Locate the pressure before choosing a change

Dispatcher BUSY and IDLE are also measured in hundredths of a second. For one unchanged dispatcher:

interval utilization = ΔBUSY / (ΔBUSY + ΔIDLE)
                     = 600 / (600 + 5400)
                     = 0.10 = 10%

Use an unavailable result when the denominator is zero, or take a fresh baseline after a reset or an identity change. Dispatcher CPU has a different unit, millionths of a second, and must not be mixed directly into this formula. Persistent high utilization together with dispatcher queue delay supports investigating dispatch and network capacity. Compare multiple dispatchers individually. An average can conceal an uneven workload. See V$DISPATCHER.

Shared-server BUSY and IDLE differences show occupied time, and REQUESTS differences show items taken from the common queue during each server's life. Status helps interpret a sample: EXEC means executing SQL, WAIT (ENQ) means waiting for a lock, and WAIT (COMMON) means idle and awaiting a request. A long call can keep a server occupied while further requests accumulate. High busy time includes service activity and waiting. It is not a measurement of CPU consumption. See V$SHARED_SERVER.

For the particular lab sessions, resolve the actual PDB CON_ID, username, and service first. Use the recorded identities rather than an unfiltered instance-wide session count:

SELECT con_id, sid, serial#, saddr, username, service_name,
       server, status, sql_id, sql_exec_start,
       state, event, wait_class
FROM v$session
WHERE con_id = <LAB_CON_ID>
  AND username = 'COURSE_READER'
  AND service_name = '<ACTUAL_LAB_SERVICE>'
  AND type = 'USER';

Substitute validated values. Placeholders are not executable SQL. Identify each test session by its container, SID, and SERIAL#, and retain SADDR for circuit observation. Repeated samples of the same SQL execution help identify long work. Read EVENT and WAIT_CLASS as a current wait only when STATE='WAITING'. Otherwise they describe the most recent wait. SQL activity plus current waits can suggest what to investigate. Match this with host CPU saturation, run queues, device latency, and I/O throughput collected over the same interval using the instructor's approved host tools. An active session can be waiting, and execution status alone cannot establish CPU saturation. See V$SESSION.

Evidence to combine	Investigation or measurable next step
Persistent dispatcher utilization and dispatcher queue delay	Inspect network dispatch load and imbalance; test dispatcher capacity only with resource headroom.
Common queue delay, occupied shared servers, and repeated long execution	Identify the long work and its waits; reduce that work or test appropriate routing.
Session wait evidence plus host CPU/I/O saturation or slow devices	Investigate the constrained resource or workload before adding concurrency.
Common queue delay, all workers occupied, growth at the configured ceiling, and available resources	Consider a small bounded worker-capacity experiment and compare the next interval.
Session or circuit limits reached during new connections	Investigate admission capacity and connection behavior. These limits govern connections rather than directly scheduling common-queue items.
Rising memory allocations or shrinking available pool space	Examine pool consumers and allocation failures against the workload and tested memory budget.

SHARED_SERVERS controls the root pool's initial and minimum worker count. MAX_SHARED_SERVERS bounds automatic growth when it exceeds that minimum. SHARED_SERVER_SESSIONS bounds shared-session admission, and CIRCUITS bounds circuit availability. Keep the lab's captured settings and its process and session headroom beside the interval observations. Oracle automatically adjusts shared workers within its limits. Dispatchers are explicitly configured. See managing shared-server capacity, SHARED_SERVERS, and MAX_SHARED_SERVERS.

Read memory allocations with the workload:

SELECT con_id, pool, name, bytes
FROM v$sgastat
WHERE pool IN ('large pool', 'shared pool');

BYTES is the allocation size for the named component in its pool. Preserve CON_ID, pool, and component name when comparing observations. Instance-wide rows can use CON_ID=0. Include configured and actual pool sizes, relevant free-memory rows, other consumers, and actual allocation-error evidence. A single free-memory row is insufficient to establish an allocation cause or a safe new budget. Shared-session memory uses the large pool when that pool is configured, and otherwise the shared pool, so both may matter. See V$SGASTAT and memory configuration.

Finish the experiment through the dedicated connection

The exercise changes only the isolated instance's memory configuration, from the dedicated common administrator in CDB$ROOT. In 19c the pool is sized in root. A PDB can separately disable its own shared-server use by setting SHARED_SERVERS=0 and re-enable it with RESET. This exercise performs no PDB override. See SHARED_SERVERS container behavior.

Keep the experiment's positive MAX_SHARED_SERVERS=4 while stopping new shared clients:

SHOW CON_NAME
ALTER SYSTEM SET shared_servers=0 SCOPE=MEMORY;

New clients can no longer connect in shared mode. Existing shared connections retain servers until their connections close. Oracle documents a retained count of the smaller of the previous SHARED_SERVERS setting and MAX_SHARED_SERVERS. For the example's prior minimum of 2 and maximum of 4, that retained count is 2. Setting both parameters to zero would terminate all workers and leave remaining shared requests queued until a parameter is raised. Keep the dedicated path and the positive maximum during the drain. See disabling shared server and shared-server architecture and client routing.

Let each recorded lab client finish its work and explicitly resolve its transaction. For a read-only disposable client, EXIT ROLLBACK closes SQL*Plus with rollback semantics. If the exercise performed DML, first make the intended commit or rollback decision. Do not force a dispatcher shutdown or terminate sessions to complete this practice.

Before closing the client, capture its circuit through the recorded SADDR:

SELECT con_id, circuit, saddr, dispatcher, server, status, queue
FROM v$circuit
WHERE saddr = HEXTORAW('<CAPTURED_SADDR_HEX>');

Replace the hexadecimal placeholder from the actual observation. Record every matching circuit address and each returned CON_ID. V$CIRCUIT.SADDR links the circuit to its session. QUEUE shows COMMON, DISPATCHER, SERVER, or NONE. NONE describes an idle circuit, and EOF describes one about to be removed. Both still represent rows to observe through removal. See V$CIRCUIT.

After the client's exit, repeat the scoped session query for its exact CON_ID, SID, and SERIAL#, and query each captured circuit address separately:

SELECT con_id, sid, serial#, saddr, server, status
FROM v$session
WHERE con_id = <LAB_CON_ID>
  AND sid = <CAPTURED_SID>
  AND serial# = <CAPTURED_SERIAL>;

SELECT con_id, circuit, saddr, status, queue
FROM v$circuit
WHERE circuit = HEXTORAW('<CAPTURED_CIRCUIT_HEX>');

Confirm that the recorded sessions and circuits disappear across repeated observations from an observer whose relevant container visibility was established before the test. Retain circuit container values as returned, including instance-wide zero when it is present. Scope ownership through the captured session and circuit identity. A query that omits the real PDB identity, or that lacks visibility, cannot certify that a particular lab session has drained. If a session persists, keep the management connection available and investigate the client and its work.

Capture, restore and verify exact originals

The approved teaching baseline uses explicit original root values, with no required existing dispatcher handler: SHARED_SERVERS=0, DISPATCHERS='', MAX_SHARED_SERVERS=8, SHARED_SERVER_SESSIONS=16, and CIRCUITS=16. The experiment sets 2, one TCP dispatcher, 4, 8, and 8 respectively. These small counts explain the experiment. They are not production sizing advice.

Before enablement, preserve the full original values and their order, the instance and startup identity, the parameter default and modified metadata, and any PDB overrides. Use a long SQL*Plus line width and a nonsecret spool so dispatcher strings are not truncated. V$SYSTEM_PARAMETER2 exposes each member of a list separately. Preserve every dispatcher entry with its ORDINAL.

SELECT con_id, name, value, display_value, isdefault, ismodified
FROM v$system_parameter
WHERE name IN ('dispatchers','shared_servers','max_shared_servers',
               'shared_server_sessions','circuits','large_pool_size',
               'processes','sessions');

SELECT con_id, name, ordinal, value
FROM v$system_parameter2
WHERE name = 'dispatchers';

With the recorded lab clients and circuits fully gone, restore only the settings changed by this experiment, using the exact captured originals. For the confirmed explicit baseline above:

ALTER SYSTEM SET dispatchers='' SCOPE=MEMORY;
ALTER SYSTEM SET shared_servers=0 SCOPE=MEMORY;
ALTER SYSTEM SET max_shared_servers=8 SCOPE=MEMORY;
ALTER SYSTEM SET shared_server_sessions=16 SCOPE=MEMORY;
ALTER SYSTEM SET circuits=16 SCOPE=MEMORY;

The empty dispatcher restoration applies only when that exact empty original was captured and the isolated lab has no remaining required shared clients or handlers. Preserve preexisting services such as XDB and every original dispatcher list entry on another profile. Do not apply this example rollback to a nonmatching baseline. An originally unspecified or default parameter, or a complex dispatcher list, needs an instructor-tested restoration procedure for its actual state. A blank display is not a literal NULL to paste into SET. Establish that procedure before starting the experiment. See V$SYSTEM_PARAMETER and V$SYSTEM_PARAMETER2.

Repeat the parameter queries and compare the effective values with the captured baseline. Confirm that the dedicated management session still works, that the scoped test sessions and circuits remain absent, and that the original required handlers and services are present. Record any restoration or postcheck failure. SCOPE=MEMORY changes the running configuration and leaves the startup parameter file unchanged. The root operation requires commonly granted ALTER SYSTEM. See ALTER SYSTEM.

Practice and pass criterion

1. Confirm the instructor-designated database, the root common administrator, the independent dedicated management descriptor and session, the actual lab PDB and service mapping, and scoped observer visibility. Confirm the original explicit baseline, spare process and session capacity, and the tested SGA budget. Ensure that no unrelated clients or required dispatcher handlers use this disposable instance. Record the exact release and RU and the existing rollback procedure.
2. With one already verified shared test client, capture A and B over a recorded short interval using a small read-only repeatable workload. Use queries against the provisioned course tables and keep demand bounded. Preserve per-query times and row identities. Record actual output instead of copying the authored counter values.
3. Compute interval wait and dispatcher utilization with units, denominators, and reset checks. Combine shared-server activity, scoped session waits and circuits, host resources, memory, and bounds. Explain one supported hypothesis and one diagnosis the evidence leaves unsupported.
4. Propose a measurable small change and its acceptance criteria. A proposal is sufficient when changing capacity is outside the granted exercise. If authorized, capture a comparable follow-up interval and compare response time, queue depth, wait, throughput, and resource headroom.
5. Stop new shared clients with SHARED_SERVERS=0 SCOPE=MEMORY, preserving a positive maximum. Let the recorded lab clients finish and exit with an explicit transaction outcome. Confirm that their session and circuit identities have drained. Restore the exact captured originals through the dedicated path and complete all postchecks.

Pass when you explain the bottleneck evidence and the uncertainty, correctly calculate the interval measures, reject an unsupported diagnosis, and provide actual scoped drain and restoration evidence. Capacity, lab execution, and cleanup stay pending until they are performed and recorded in the designated environment.

Required access is an authorized common root administrator with commonly granted ALTER SYSTEM, and access to the underlying SYS.V_$INSTANCE, V_$SYSTEM_PARAMETER, V_$SYSTEM_PARAMETER2, V_$DISPATCHER, V_$QUEUE, V_$SHARED_SERVER, V_$SESSION, V_$CIRCUIT, V_$SGASTAT, and the relevant container visibility. The instructor validates the minimum grants. This lesson supplies no broad grant command. Host CPU and I/O inspection requires approved host access. These notes do not authorize live or production database access, and they do not infer licensing eligibility from a view or feature.

Quiz

1. Does queue waiting always require more shared servers?

Yes. Every queued item is a worker shortage. Yes. Increasing dispatchers and workers always improves response time. No. Interval evidence distinguishes long calls, CPU/I/O constraints, dispatch pressure, and limits. No. Shared servers never affect queue delay.

2. A matching queue's WAIT rises by 300 and TOTALQ by 20. What is the interval mean wait?

15 seconds per item. 150 milliseconds per item. 300 milliseconds per item. 20 hundredths of a second per item.

3. The same queue has ΔTOTALQ=0. How should you report its interval average?

Unavailable. Inspect the ongoing queue and work, and collect another interval. Exactly zero latency. Equal to the lifetime WAIT total. Equal to dispatcher CPU time.

4. Which setting supports a normal shared-client drain in this isolated lab?

Set both shared-server controls to zero before clients finish. Force shutdown of every dispatcher. Clear all dispatcher services immediately. Set SHARED_SERVERS=0 in memory while preserving a positive MAX_SHARED_SERVERS.

5. Which evidence confirms that a particular test connection has drained?

A lower instance-wide session count. Its circuit has QUEUE='NONE'. Its recorded container and session identity, and its captured circuit rows, disappear under established observer visibility. Its session changes from active to inactive.

Names, counter values, and expected results in this lesson are authored teaching examples. No database commands were executed to produce them. Run the practice in the designated Oracle Database 19c lab, confirm the exact release update, and record what you actually observe.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro