Dedicated Server Connection -For Those Who Want to Solve “Heavy Workloads” with Dedicated Server Connections

Introduction

“When the number of connected users increases, server processes multiply too much.”
“The response of batch jobs becomes unstable.”

If these problems sound familiar, the first step is to properly understand Oracle’s Dedicated Server connection. A dedicated server follows the simple rule of 1 session = 1 server process. Its behavior and configuration are straightforward, making it ideal for heavy or long-running workloads.

This article explains the mechanism of dedicated server connections, how to configure and verify them, the differences from shared servers, their pros and cons, execution examples, and frequently asked questions. With diagrams and command examples included, you can use this as a practical reference for real operations.

What is a Dedicated Server Connection?

A Dedicated Server connection assigns a dedicated server process to each client session. It is well-suited for typical workloads such as batch jobs, long-running SQL, PL/SQL execution, and data migration. Tuning and troubleshooting are also intuitive.

[Diagram 1: Flow of Dedicated Server Connection]

 Client   →   Listener(1521)   →   Dedicated Server Process   →   SGA/Datafiles
   │                                               │
   │—————— One process per session ——————│

 Points:
 ・Each session secures one OS process (dedicated server)
 ・The more concurrent sessions, the more OS processes

Default: In most environments, if shared server is not explicitly enabled, dedicated server is the default.
Forcing Dedicated: By specifying SERVER=DEDICATED in the client’s connect descriptor, you can force dedicated connections even in a shared server environment.

Difference from Shared Server Connections (Quick Comparison)

[Diagram 2: Dedicated vs Shared Server]

 Dedicated Server:  1 session = 1 process (clear visibility, suited for heavy workloads)
 Shared Server:     Multiple sessions ⇔ Fewer shared servers (suited for many concurrent connections)

Dedicated Server
- More stable for long-running or high-load processing
- Clear isolation per session (easier troubleshooting)
- Large numbers of concurrent connections cause OS processes to grow significantly
Shared Server
- Efficient for many simultaneous connections (when many are idle)
- Requires dispatcher/shared server design and monitoring
- Heavy queries mixed in can degrade throughput

Configuring a Dedicated Server Connection

Forcing Dedicated on the Client Side (Recommended)

In tnsnames.ora, specify SERVER=DEDICATED. This is the simplest and safest approach.

# Example: tnsnames.ora
ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orclpdb1)
      (SERVER = DEDICATED)
    )
  )

SQL*Plus example:

$ sqlplus demo_app/Password#1@ORCLPDB1

Forcing “Dedicated Only” on the Server (Disabling Shared Server)

If you want to restrict the database so that only dedicated servers are used, disable dispatchers and set SHARED_SERVERS to 0.

-- Disable shared server (SPFILE example)
SQL> ALTER SYSTEM SET DISPATCHERS='' SCOPE=BOTH;
SQL> ALTER SYSTEM SET SHARED_SERVERS=0 SCOPE=BOTH;
-- Restart the instance if necessary

Note: Even if you do this, the best practice remains specifying SERVER=DEDICATED in the client connect descriptor. This allows each application to explicitly define its connection method and ensures flexibility for future changes.

Verifying and Monitoring Dedicated Server Connections

Checking if Your Session is Dedicated

The V$SESSION.SERVER column indicates the connection type (e.g., DEDICATED, SHARED).

-- Check your own session type
SQL> SELECT sid, serial#, server
       FROM v$session
      WHERE audsid = USERENV('SESSIONID');

-- List sessions of a specific user
SQL> SELECT sid, serial#, server, machine, program
       FROM v$session
      WHERE username = 'DEMO_APP';

Mapping to OS Processes (SPID)

SQL> SELECT s.sid, s.serial#, s.username, s.program,
            p.spid AS os_spid
       FROM v$session s
       JOIN v$process p ON p.addr = s.paddr
      WHERE s.username = 'DEMO_APP';

OS_SPID is the PID of the dedicated server process. You can observe it on the OS with ps -ef | grep <SPID>.

Checking from the Listener

$ lsnrctl services

Service "orclpdb1" has 1 instance(s).
  Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
        LOCAL SERVER

If a “DEDICATED” handler is listed, the connection type is dedicated.

Execution Example (Including User Creation, Privileges, and Table Creation)

The following demonstrates creating an application user in a CDB with PDB (example: ORCLPDB1), connecting with SERVER=DEDICATED, and running SQL. Replace object names as appropriate for your environment.

1) Create User and Grant Privileges (as SYS in PDB)

-- Connect as SYSDBA to the PDB
SQL> CONNECT sys/******@ORCLPDB1 AS SYSDBA;

-- Create application user (default tablespace USERS assumed)
SQL> CREATE USER demo_app IDENTIFIED BY "Password#1"
       DEFAULT TABLESPACE users
       QUOTA 200M ON users;

-- Grant minimum required privileges
SQL> GRANT CREATE SESSION, CREATE TABLE TO demo_app;
SQL> GRANT UNLIMITED TABLESPACE TO demo_app;  -- or use QUOTA control

2) Create Table and Insert Data as Application User

SQL> CONNECT demo_app/Password#1@ORCLPDB1;

-- Sample table
SQL> CREATE TABLE orders (
       id          NUMBER       PRIMARY KEY,
       item        VARCHAR2(50) NOT NULL,
       qty         NUMBER       NOT NULL,
       ordered_at  TIMESTAMP    DEFAULT SYSTIMESTAMP
     );

SQL> INSERT INTO orders (id, item, qty) VALUES (1, 'HDMI-CABLE', 2);
SQL> INSERT INTO orders (id, item, qty) VALUES (2, 'SSD-1TB',    1);
SQL> COMMIT;

SQL> SELECT * FROM orders ORDER BY id;

        ID ITEM         QTY ORDERED_AT
---------- --------- ------ ------------------------------------
         1 HDMI-CABLE    2  2025-08-16 15:12:34.123456 +09:00
         2 SSD-1TB       1  2025-08-16 15:12:34.223456 +09:00

3) Verify Dedicated Connection

SQL> SELECT sid, serial#, server
       FROM v$session
      WHERE audsid = USERENV('SESSIONID');

       SID   SERIAL# SERVER
---------- --------- ---------
       131      5123 DEDICATED

4) Map to OS Process

SQL> SELECT s.sid, p.spid AS os_spid, s.program
       FROM v$session s
       JOIN v$process p ON p.addr = s.paddr
      WHERE s.audsid = USERENV('SESSIONID');

       SID OS_SPID PROGRAM
---------- ------ ---------------------------------
       131  28451  sqlplus@dbhost (TNS V1-V3)

From OS:

$ ps -fp 28451
UID        PID  PPID  C STIME TTY          TIME CMD
oracle   28451 28410  0 15:12 ?        00:00:00 oracle@ORCLPDB1 (DEDICATED)

Pros, Cons, and Considerations

Pros

Clarity: 1 session = 1 process, making troubleshooting easier
Stable for heavy workloads: Batch, ETL, long-running SQL/PLSQL run more reliably
Simple configuration: Just specify SERVER=DEDICATED in the connect descriptor
Predictable resource usage: Easy to estimate per-session upper limits

Cons

Not suitable for massive concurrent connections: Processes grow proportionally with sessions
OS resource pressure: Must watch PROCESSES / SESSIONS parameters and OS limits
Overhead during connection storms: Rapid process spawning may occur

Considerations

Define policies per application: Heavy workloads → dedicated, lightweight/mass concurrent → shared or connection pools
Manage limits: Regularly check processes / sessions in V$PARAMETER and adjust
Use pooling when needed: Middleware (UCP/OCI) or DRCP can smooth connection load

Frequently Asked Questions

Q1. Is dedicated server the default?
A. Yes, in most environments, unless shared server is explicitly configured. For certainty, specify SERVER=DEDICATED in the connect descriptor.

Q2. Can I use dedicated only for some applications even if shared server is configured?
A. Yes. Provide an alias in tnsnames.ora with SERVER=DEDICATED for those applications.

Q3. How many concurrent connections can a dedicated server handle?
A. It depends on CPU/memory, OS process limits, and PROCESSES/SESSIONS parameters. Perform load testing, monitor results, and if insufficient, consider shared server or pooling.

Q4. How is DRCP (Database Resident Connection Pooling) different?
A. DRCP is a server-side connection pooling mechanism, separate from dedicated/shared modes. It benefits workloads with many short-lived connections (e.g., PHP). For heavy, long-running queries, dedicated is often easier to manage.

Q5. What should I monitor?
A. Monitor V$SESSION, V$PROCESS, listener services/status, OS process counts, memory, and load averages. Define thresholds and automate alerts for stable operations.

Summary (Key Points)

Dedicated server = 1 session per process, suitable for heavy or long-running workloads
Fastest way to enforce dedicated: Specify SERVER=DEDICATED in connect descriptors
Disable shared server: DISPATCHERS='' and SHARED_SERVERS=0
Verify connections: Use V$SESSION.SERVER, V$PROCESS SPID, lsnrctl services
Operations: Monitor processes/sessions, consider pooling, and design monitoring properly

[参考]
Net Services Administrator’s Guide