Oracle Database Shared Server Connections Explained Clearly and Accurately (Differences from Dedicated Connections / Configuration Steps / Monitoring)

English

Introduction

“If the number of connected users increases, the number of processes explodes and slows down.”
“If web applications have long wait times.”
— In such cases, Shared Server connections are effective. Unlike Dedicated Server connections, where each client gets its own process, Shared Server shares processes to handle many simultaneous connections efficiently.

This article covers the differences between Dedicated and Shared connections, the relationship with the SGA, correct configuration and verification methods, examples in tnsnames.ora, and monitoring. We will also include diagrams, SQL execution examples, and conclude with advantages/disadvantages, FAQs, future update points, and internal link suggestions.

What Is a Shared Server Connection (vs. Dedicated Connection)

Overall Mechanism (Text Diagram)

(Client)                 (Server side: Oracle Instance)
 HTTP/SQL*Net ──→ [Dispatcher] ──→ [Common Queue (SGA)] ──→ [Shared Server Processes]
                     (accepts many connections)         (queues requests)         (free process handles)

Quick Comparison: Dedicated vs. Shared

Item            Dedicated Server Connection           Shared Server Connection
――――――――――――――――――――――――――――――――――――――――――――
Process count   One process per connection            A few shared processes handle many connections
Resource usage  Higher (grows with connections)       Lower (resources consolidated)
Best suited for Heavy workloads with few users        Light/intermittent workloads with many users
Connection spec SERVER=DEDICATED                      SERVER=SHARED

Key Points

  • In a Shared Server, the Dispatcher places client requests into a common queue in the SGA, and whichever Shared Server process is free handles the request.
  • The database always allows Dedicated connections. Shared Server connections require explicit configuration.

Why the SGA Is Closely Related to Shared Server

In Shared Server, a portion of each user’s UGA (User Global Area) resides in the SGA (Shared Pool or Large Pool), making SGA sizing important.

  • Shared Pool: Caches SQL parse results and dictionary data.
  • Large Pool: Useful for offloading larger UGAs such as Shared Server and backup operations.
  • Common Queue (V$QUEUE): Monitors queue length and delay.
  • Monitoring views: V$DISPATCHER, V$DISPATCHER_RATE, V$SHARED_SERVER, V$CIRCUIT, V$QUEUE, V$SGASTAT.

Step-by-Step Shared Server Configuration

1) Minimal Setup (Enable)

ALTER SYSTEM SET SHARED_SERVERS = 5;

2) Explicit Dispatcher Configuration (optional)

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

3) Limit Controls

ALTER SYSTEM SET MAX_SHARED_SERVERS = 20;
ALTER SYSTEM SET SHARED_SERVER_SESSIONS = 500;
ALTER SYSTEM SET CIRCUITS = 2000;

4) Disable (Revert to Default)

ALTER SYSTEM SET SHARED_SERVERS = 0;
ALTER SYSTEM SET DISPATCHERS   = '';

Note:
When SHARED_SERVERS=0 is set, new Shared Server connections are no longer possible. Existing sessions remain until they finish, then Shared Server processes shut down.

If you attempt a new connection, you will see this error:

[oracle@orcl19c ~]$ sqlplus app_user/StrongPass123@ORCL_SHARED

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 16 03:23:30 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server

This behavior is expected: since Shared Server is disabled, the listener cannot find a handler for a SHARED connection. Dedicated connections still work.

Client-Side Configuration (tnsnames.ora)

Example for Shared Server

ORCL_SHARED =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <dbhost>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (SERVER = SHARED)
    )
  )

Example for Dedicated Server

ORCL_DEDICATED =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <dbhost>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (SERVER = DEDICATED)
    )
  )

Monitoring and Tuning Basics

  • Connection type check: V$SESSION.SERVER distinguishes DEDICATED / SHARED / NONE.
    • DEDICATED: Dedicated connection, always shown as DEDICATED.
    • SHARED: Actively executing through a Shared Server process.
    • NONE: Connected via Shared Server, but idle (not executing).
  • Queues: Check queue length/delay in V$QUEUE.
  • Dispatchers: V$DISPATCHER, V$DISPATCHER_RATE for utilization.
  • Shared Servers: V$SHARED_SERVER for active process count.
  • SGA usage: V$SGASTAT for Shared Pool / Large Pool consumption.

Example Walkthrough (Create User → Table → Verify Connection)

Environment: Oracle Database 19c / Single Instance

1) Connect as Administrator

sqlplus / as sysdba

2) Create Application User and Grant Privileges

CREATE USER app_user IDENTIFIED BY "StrongPass123";
GRANT CREATE SESSION, CREATE TABLE TO app_user;
ALTER USER app_user QUOTA UNLIMITED ON USERS;

3) Create Sample Table and Insert Data

CONN app_user/StrongPass123@ORCL_DEDICATED
CREATE TABLE t_demo (
  id   NUMBER PRIMARY KEY,
  note VARCHAR2(100)
);
INSERT INTO t_demo VALUES (1,'hello');
COMMIT;

4) Enable Shared Server (DBA)

CONN / AS SYSDBA
ALTER SYSTEM SET SHARED_SERVERS = 5;
ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=3)';

5) Connect with Shared Server and Verify (Run in Separate SYSDBA Session)

-- Session A: Connect app_user via Shared Server
sqlplus app_user/StrongPass123@ORCL_SHARED
SELECT * FROM t_demo WHERE id = 1;  -- run an application query

-- Session B: SYSDBA checks V$SESSION
sqlplus / as sysdba
SET LINES 200
COL username FOR A20
SELECT sid, serial#, username, server
FROM   v$session
WHERE  username = 'APP_USER';
/*
  SID  SERIAL#  USERNAME   SERVER
----- --------- ---------- ---------
   65     27197 APP_USER   NONE      ← idle state
   66     28122 APP_USER   SHARED    ← changes to SHARED while executing
*/

When to Use and When to Avoid (Pros / Cons)

Advantages

  • Fewer processes per connection → more efficient resource usage
  • Better scalability for large numbers of simultaneous connections
  • Can be adjusted dynamically

Disadvantages / Caveats

  • Batch jobs and RMAN are best run on Dedicated connections
  • Requires careful SGA design
  • Must monitor queues for delays
  • Setting SHARED_SERVERS=0 makes new Shared connections fail with ORA-12520

Frequently Asked Questions

Q1. Can Shared Server be used without setting DISPATCHERS?
A. Yes. If SHARED_SERVERS>0, one default TCP dispatcher is started automatically.

Q2. How many Shared Servers should I configure?
A. As a guideline, start with about one Shared Server per 10 connections, then monitor and adjust.

Q3. If V$SESSION shows NONE, is that an error?
A. No. In a Shared connection, it shows SHARED only during active execution. When idle, it shows NONE. This is normal.

Q4. What happens if I set SHARED_SERVERS=0?
A. New Shared connections fail with ORA-12520. Dedicated connections still work.

Summary (Key Points)

  • Shared Server is good for many concurrent connections; heavy workloads should use Dedicated.
  • To enable: set SHARED_SERVERS>0. To control: use DISPATCHERS and limit parameters.
  • Monitor with V$SESSION, V$QUEUE, V$DISPATCHER%, V$SGASTAT.
  • V$SESSION.SERVER changes between DEDICATED / SHARED / NONE depending on the connection state.
  • With SHARED_SERVERS=0, new Shared connections fail (ORA-12520), but Dedicated connections are fine.
  • In tnsnames.ora, toggle between SERVER=SHARED and SERVER=DEDICATED.


[reference]
11 Configuring a Shared Server Architecture

OracleNetを使ったリモート接続の手順：初心者向け解説
Oracleデータベースを使い始めると、「データベースにどうやって接続するか」という課題に直面します。その中で「OracleNet」という仕組みは、データベースと外部から通信するために重要な役割を果たします。この記事では、初心者向けにOra...
oracle-master00000.com

コメント

Copied title and URL