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
- User issues
COMMIT - Redo entries written to redo log buffer
- LGWR writes redo to disk
- GES confirms global locks
- GCS ensures all instances see consistent block state
- 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 timegc 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
✅ 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.
