Lesson 13

Oracle Shared Server (formerly MTS) lets many client sessions share a small pool of server processes. Clients connect to dispatchers, which enqueue requests; shared server processes dequeue, execute, and return results. This reduces per-session memory/process overhead and improves scalability for large numbers of lightweight, concurrent sessions.

Good Use Cases for when to use Shared Server

• High concurrency of short, frequent SQL/PLSQL calls (OLTP^[1], session-chatty apps, connection pools).
• Environments where per-session memory/process footprint must be minimized.

When not to use Shared Server

• Long-running queries, bulk loads, maintenance jobs, and other CPU/IO-intensive tasks prefer a dedicated server.
• Operations that require exclusive/serial access patterns (i.e., some utilities and recovery operations).

Connect with appropriate privilege and check the key parameters:

-- As a DBA
sqlplus /nolog
CONNECT / AS SYSDBA

SHOW PARAMETER shared\_servers
SHOW PARAMETER max\_shared\_servers
SHOW PARAMETER dispatchers 

Adjust conservatively and observe:

-- Example: scale shared servers and dispatchers
ALTER SYSTEM SET SHARED_SERVERS = 4;
ALTER SYSTEM SET DISPATCHERS   = '(PROTOCOL=TCP)(DISPATCHERS=3)';

Shared server processes

SELECT NAME, STATUS, REQUESTS, BYTES_RECEIVED, BYTES_SENT
FROM   V$SHARED_SERVER
ORDER  BY NAME;

Aggregated activity

SELECT TOTAL_REQUESTS, IDLE_TIME, BUSY_TIME
FROM   V$SHARED_SERVER_MONITOR;

Dispatchers

SELECT NAME, NETWORK, STATUS, ACCEPTED, IDLE, BUSY
FROM   V$DISPATCHER
ORDER  BY NAME;

Queues & wait pressure

SELECT NAME, TOTALQ, WAIT, (TOTALQ - WAIT) AS NOWAIT
FROM   V$QUEUE
ORDER  BY NAME;

Virtual circuits

SELECT CIRCUIT, SERVER, DISPATCHER, STATUS
FROM   V$CIRCUIT;

Trace a problem session (modern approach)

-- Find the session to trace
SELECT sid, serial#
FROM   v$session
WHERE  username = '&USER';

BEGIN
DBMS\_MONITOR.SESSION\_TRACE\_ENABLE(
session\_id => \&SID,
serial\_num => \&SERIAL,
waits      => TRUE,
binds      => TRUE
);
END;
/
\-- Later
BEGIN
DBMS\_MONITOR.SESSION\_TRACE\_DISABLE(
session\_id => \&SID,
serial\_num => \&SERIAL
);
END;
/ 

• Login/request latency rising: Check V$QUEUE.WAIT; consider increasing SHARED_SERVERS or DISPATCHERS, and verify listener endpoints.
• Dispatchers maxed out: Inspect V$DISPATCHER.IDLE/BUSY; add a dispatcher or split protocols/endpoints.
• Uneven load: Review client connection pooling and service/endpoint mapping; ensure protocol alignment.
• Heavy jobs starving others: Move those sessions to dedicated (service, logon trigger, or client connect string).

Use SQL*Plus/SQLcl for command-line work and Enterprise Manager Cloud Control for fleet-level monitoring/automation. Server Manager (svrmgrl) is retired; stick to supported tooling.

• Dispatcher: Listener-facing process that accepts connections and enqueues requests.
• Shared Server: Process that dequeues and services requests from many sessions.
• Virtual Circuit: Internal linkage between a client connection, a dispatcher, and server work.
• Dedicated Server: One server process per session; best for long/large operations.
• SQL*Plus/SQLcl: Supported Oracle CLIs; use them instead of legacy Server Manager.

• Shared Server boosts concurrency by pooling server processes — great for short, frequent calls.
• Measure first: tune SHARED_SERVERS/DISPATCHERS and watch V$ views for impact.
• Place heavy jobs on dedicated services to protect latency-sensitive traffic.
• Use supported tools (SQL*Plus/SQLcl, EM Cloud Control); Server Manager is legacy.

[1]Online Transaction Processing (OLTP): Online Transaction Processing (OLTP) is a class of software programs capable of supporting transaction-oriented applications on the internet. In OLTP, a transaction is a sequence of database operations that are executed as a single logical unit.