Interview Question 9 : Dedicated Server vs Shared Server user connectivity

 

1️⃣ Dedicated Server vs Shared Server (DBA Deep Dive)

Dedicated Server

What It Is

• One client session → one server process
• Server process handles only that session

Client ─── Dedicated Server Process ─── Instance

Characteristics

• Fast response time
• Simple architecture
• High memory consumption (PGA per session)

When DBAs Use It

✅ OLTP systems
✅ Low–medium number of concurrent users
✅ High‑performance workloads

Pros / Cons

Pros	Cons
Fast	High memory usage
Easy to manage	Limited scalability
Predictable performance	More OS processes

---

Shared Server

What It Is

• Multiple client sessions share a pool of server processes
• Uses dispatcher processes

Clients
  ↓
Dispatchers
  ↓
Shared Server Processes
  ↓
Instance

Key Components

• Dispatchers (network-facing)
• Shared server processes
• Large pool (SGA usage increases, PGA decreases)

When DBAs Use It

✅ Thousands of users
✅ Call centers
✅ Chatty applications

Pros / Cons

Pros	Cons
Scales well	Slight overhead
Lower memory	More complexity
Fewer processes	Not ideal for large batch jobs

---

DBA Rule of Thumb

Dedicated = performance
Shared = scalability

---

2️⃣ Listener vs Local Listener vs SCAN (Very Important)

Listener (Single Instance)

• Network gateway into Oracle
• Listens on port 1521
• Hands off connection → server process

Client → Listener → Server Process → Instance

✅ Listener does NOT run SQL
✅ Not involved after connection

---

Local Listener (RAC)

• Node‑specific listener
• Bound to node VIP
• Accepts redirected connections from SCAN

One per node.

---

SCAN Listener (RAC)

What Is SCAN?

Single Client Access Name

• Cluster‑level hostname
• Resolves to 3 IPs
• Never changes

Why SCAN Exists

• HA
• Load balancing
• Zero client reconfiguration

Client
  ↓
SCAN Listener
  ↓ (redirect)
Local Listener (Node)
  ↓
Instance

✅ Clients never connect directly to nodes in RAC

---

Comparison Table

Component	Single Instance	RAC
Listener	Yes	Yes
Local Listener	No	Yes
SCAN Listener	No	Yes
Client visibility	Host	SCAN only

---

3️⃣ What Happens During COMMIT in Oracle RAC (GCS / GES)

This is senior DBA / architect‑level knowledge.

---

Key RAC Components

GES (Global Enqueue Service)

• Manages locks across instances
• Ensures consistency

GCS (Global Cache Service)

• Manages data blocks in memory across instances
• Prevents corruption

---

COMMIT Flow in RAC

Step‑by‑Step

1. User issues COMMIT
2. Redo entries written to redo log buffer
3. LGWR writes redo to disk
4. GES confirms global locks
5. GCS ensures all instances see consistent block state
6. Commit acknowledged to user

---

Why RAC COMMIT Is Slower Than Single Instance

Reason
Global lock coordination
Inter‑instance messaging
Cache fusion traffic

✅ Still extremely fast — but architecturally more complex

---

Important DBA Insight

COMMIT works at cluster level in RAC, not just instance level.

---

4️⃣ Session vs Process vs Thread (Top Interview Confusion)

Session

• Logical
• Represents user context
• Stored in SGA
• Contains:
  • User
  • Privileges
  • SQL state

✅ Session exists after login

---

Process

• OS‑level entity
• Executes SQL
• Exists for work execution

Types:

• Server process
• Background process

✅ Can exist without session (background)

---

Thread

• Lightweight execution unit inside a process
• Common in Windows / multithreaded systems
• Rarely visible to Oracle admins

---

Mapping Table

Concept	Nature	Exists Where
Session	Logical	SGA
Process	Physical	OS
Thread	Execution unit	Inside process

---

Relationship (Dedicated Server)

1 Session ↔ 1 Server Process

Relationship (Shared Server)

Many Sessions ↔ Few Server Processes

---

5️⃣ Interview Q&A: Oracle Connectivity

Q1: Do users connect to the database or instance?

✅ Instance

---

Q2: Does listener execute SQL?

❌ No
✅ Only hand‑off

---

Q3: What happens if listener goes down?

✅ Existing sessions continue
❌ New connections fail

---

Q4: What does a service represent?

✅ Logical workload boundary
✅ Not tied to a specific instance

---

Q5: Can one database have multiple services?

✅ Yes (OLTP, Reporting, Batch)

---

Q6: Can one instance serve multiple services?

✅ Yes

---

One‑Line Interview Killers

• “Users connect to services, not instances.”
• “Listener is not in the data path after connection.”
• “Session is logical, process is physical.”
• “RAC commits involve cluster‑wide coordination.”
• “SCAN gives HA without client changes.”

---

Final Mental Model (Everything Together)

Client
 ↓
Service
 ↓
Listener / SCAN
 ↓
Server Process
 ↓
Session
 ↓
Instance
 ↓
Database

1️⃣ Oracle Memory Model in Connectivity (SGA vs PGA)
Understanding who uses what memory and when is critical for DBAs.
SGA (System Global Area)
Shared memory used by all sessions connected to the instance.
Key Connectivity‑Related Components
🔹 Shared Pool
Stores:
Parsed SQL
Execution plans
Data dictionary cache
Shared across all sessions
✅ High shared pool efficiency = faster logins & SQL reuse
🔹 Database Buffer Cache
Stores data blocks read from disk
Impacted heavily by:
Number of users
Access patterns
LRU behavior
✅ Connectivity + workload decides cache pressure
PGA (Process Global Area)
Private memory for each server process.
Usage Depends on Server Mode
Mode
PGA Usage
Dedicated Server
High (1 PGA per session)
Shared Server
Lower (shared execution)
PGA Stores:
Sort areas
Session variables
Cursor state
✅ Too many dedicated sessions → PGA exhaustion
DBA Insight
Connection strategy directly determines memory scalability.
2️⃣ Oracle Cache Fusion (RAC Internals – Must‑Know)
Cache Fusion is why RAC works safely.
What Is Cache Fusion?
Cache Fusion allows instances to share data blocks directly from memory instead of writing to disk.
Scenario Without Cache Fusion (Old Clusters)
Instance 1 → Disk → Instance 2 (slow)
With Cache Fusion
Instance 1 → Interconnect → Instance 2 (fast)
How It Works
Instance A modifies a block
Instance B requests the same block
GCS coordinates ownership
Block transferred over cluster interconnect
No disk I/O required
✅ Faster
✅ ACID‑compliant
✅ No data corruption
RAC Performance Reality
Good Interconnect
Poor Interconnect
RAC is fast
RAC is slow
Low gc waits
High gc waits
Predictable
Unstable
DBA Key Metrics
gc cr block receive time
gc current block busy
Interconnect latency
3️⃣ Real‑World RAC & Connectivity Troubleshooting
Problem 1: Users Can’t Connect, Database Is UP
Possible Causes
Listener down
Service not registered
SCAN misconfigured
DBA Checks
lsnrctl status
srvctl status service
✅ Instance UP ≠ Database Accessible
Problem 2: Connections Are Slow
Root Causes
Shared pool contention
DNS / SCAN latency
Excessive session creation
DBA Fixes
Connection pooling
Increase shared pool
Fix DNS resolution
Problem 3: Sudden RAC Performance Drop
Common Reasons
Cross‑instance block pinging
Hot table accessed from many nodes
Poor service affinity
✅ Bind services to workload patterns
4️⃣ Session Leaks & Connection Pool Issues (Very Common)
What Is a Session Leak?
Application opens sessions
Does not close them
Instance memory grows
Eventually hits process/session limits
Symptoms
Thousands of inactive sessions
Memory pressure
ORA‑00020 (processes exceeded)
DBA Solution
Enforce connection pooling
Idle timeouts
Proper app close logic
✅ DBAs fix symptoms
✅ Developers fix root cause
5️⃣ Full Interview Mock Q&A (Senior DBA / Architect)
Q1: Do users connect to database files?
❌ No
✅ To the instance via a server process
Q2: What breaks existing sessions if listener crashes?
❌ Nothing
✅ Listener is not in the data path
Q3: Why doesn’t RAC cause data corruption?
✅ Cache Fusion + GCS + GES
Q4: Which memory grows with users?
✅ PGA (especially dedicated server)
Q5: When would you avoid shared server?
✅ Long‑running queries
✅ Batch jobs
✅ High PGA workloads
Q6: Can one service run on multiple instances?
✅ Yes (normal RAC design)
Interview Killer One‑Liners
“Listener is only for connection establishment.”
“Users connect to services, not instances.”
“Session is logical, process is physical.”
“Cache Fusion eliminates disk I/O in RAC block transfers.”
“Dedicated server consumes PGA per session.”
6️⃣ Mental Model: End‑to‑End Oracle Connectivity
Client
 ↓
DNS / SCAN
 ↓
Listener
 ↓
Server Process
 ↓
Session
 ↓
SGA + PGA
 ↓
Instance
 ↓
Database
7️⃣ Architect‑Level Takeaway
Connectivity design is not a networking topic — it directly impacts memory, performance, scalability, and RAC stability.