Oracle Shared Server: Using SQL*Plus HELP and Data Dictionary Views

Oracle Shared Server (formerly MTS) lets many client sessions share a smaller pool of server processes via dispatchers and request queues. You’ll pair SQL*Plus commands with V$ views to confirm configuration, observe load, and tune safely.

About SQL*Plus HELP: the HELP facility documents SQL*Plus topics (HELP SHOW, HELP SPOOL, HELP DESCRIBE). There isn’t a dedicated “HELP SHARED SERVER” topic—monitoring relies on parameters and views shown below.

-- Connect with appropriate privileges
sqlplus /nolog
CONNECT / AS SYSDBA

-- Check key parameters
SHOW PARAMETER shared_servers
SHOW PARAMETER dispatchers
SHOW PARAMETER max_shared_servers

• SHARED_SERVERS/MAX_SHARED_SERVERS size the shared server pool.
• DISPATCHERS defines protocol, count, and endpoints for listener-facing dispatchers.

HELP
HELP SHOW
HELP DESCRIBE
HELP SPOOL

Use these for session ergonomics and documentation while you query the data dictionary for runtime state.

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;

-- Scale shared servers
ALTER SYSTEM SET SHARED_SERVERS = 4;

-- Shape dispatchers
ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=3)';

Change gradually and watch V$SHARED_SERVER, V$DISPATCHER, and V$QUEUE for effects.

Use DBMS_MONITOR for session-level tracing (binds + waits) instead of legacy internals:

-- Identify a session
SELECT sid, serial#
FROM   v$session
WHERE  username = '&USER';

-- Enable trace
BEGIN
  DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id => &SID,
    serial_num => &SERIAL,
    waits      => TRUE,
    binds      => TRUE
  );
END;
/

-- Disable
BEGIN
  DBMS_MONITOR.SESSION_TRACE_DISABLE(
    session_id => &SID, serial_num => &SERIAL
  );
END;
/

• Slow logins / queued work? Rising V$QUEUE.WAIT → increase SHARED_SERVERS and/or DISPATCHERS; review listener endpoints.
• Dispatchers always busy? Check V$DISPATCHER.BUSY vs IDLE; consider more dispatchers or protocol alignment.
• Uneven load across servers? Validate client pooling and session stickiness; confirm consistent connection strings.
• High parse/CPU from chatty apps? Encourage binds, plan stability, and connection pooling; Shared Server favors short, frequent calls.

Enterprise Manager Cloud Control offers dashboards for dispatcher/server utilization, queue depth, and session load—use alongside the SQL above for faster diagnosis.

• Version nuances: Columns and defaults can vary by release—check your version docs.
• Security: Run monitoring and ALTER SYSTEM with least privilege, and validate changes in non-prod first.