PGA, Work Areas, Session/Process Models, Dedicated vs Shared Server, and Connection Lifecycle

Learning outcomes

ServiceHub now has several client connections: SQLcl, a runtime application, and an administration session. A process list appears to show one Oracle server process per client, so the team concludes that “session,” “connection,” and “process” are synonyms. That model works just long enough to fail when connection pooling, shared server, or operating-system threading enters the picture.

1. Connection, session, and server process are different objects

A connection is the communication path from a client to Oracle. A database session is the logical database state associated with authenticated work: user identity, transaction state, session settings, open cursors, and other context. A server process executes database calls on behalf of sessions. In the common dedicated server model, one server process is associated with one client session for its lifetime. That is a deployment choice, not a universal Oracle law.

With shared server, clients connect through dispatchers and requests are handled by a pool of shared server processes. Session state that any shared server must access is kept in shared memory. Database Resident Connection Pooling (DRCP) and application-side connection pools add additional reuse boundaries. Later application-integration lessons cover those pools in depth.

SELECT sid, serial#, username, status, server,       machine, program, service_name, con_id, paddrFROM   v$sessionWHERE  sid = TO_NUMBER(SYS_CONTEXT('USERENV','SID'));

V$SESSION.SERVER can report values such as DEDICATED, SHARED, or POOLED. Never infer the model only from the program name or from the number of client windows you have open.

2. PGA and UGA: process-private versus session state

The Program Global Area (PGA) is nonshared memory associated with an Oracle process/thread. A server process uses PGA memory for process-private state, private SQL areas, and work areas used by operations such as sorts and hash joins. The User Global Area (UGA) is session state. In a dedicated-server session, the UGA is normally in the PGA. In shared server, the UGA must be accessible by different shared server processes and therefore resides in shared memory such as the large pool/SGA.

A private SQL area stores execution state and bind values for a session’s cursor. Multiple private SQL areas can reference a shared execution plan in the SGA. This is the first glimpse of the separation between shared SQL and session-specific cursor state that Chapter 10 develops fully.

SELECT s.sid, s.serial#, s.server, s.status,       p.spid, p.program,       ROUND(p.pga_used_mem/1024/1024,2)     AS pga_used_mb,       ROUND(p.pga_alloc_mem/1024/1024,2)    AS pga_alloc_mb,       ROUND(p.pga_freeable_mem/1024/1024,2) AS pga_freeable_mb,       ROUND(p.pga_max_mem/1024/1024,2)      AS pga_max_mbFROM   v$session sLEFT JOIN v$process p ON p.addr = s.paddrWHERE  s.sid = TO_NUMBER(SYS_CONTEXT('USERENV','SID'));

The numbers are a momentary allocation snapshot, not a per-session quota and not proof of a leak. A process can allocate and release work-area memory over time; shared-server mappings also change the interpretation of process ownership.

3. Dedicated server: simple mapping, real resource cost

Dedicated server is straightforward: a client’s database calls are handled by a server process dedicated to that client connection. The simplicity is valuable, but large numbers of idle pooled connections can still consume session/process memory and process slots. Application connection pools should therefore be sized from workload and queueing evidence, not from “one connection per user” or “more connections are always faster.”

SELECT server, status, COUNT(*) AS sessionsFROM   v$sessionWHERE  type = 'USER'GROUP  BY server, statusORDER  BY server, status;SELECT name, valueFROM   v$pgastatWHERE  name IN ('total PGA allocated',                'total PGA inuse',                'maximum PGA allocated',                'over allocation count')ORDER  BY name;

V$PGASTAT is instance-level evidence. It cannot tell you by itself which application pool is correctly sized. Correlate session metadata, workload concurrency, application latency, process limits, and memory pressure before changing connection counts or PGA settings.

4. Shared server: requests move between workers

Shared server adds dispatcher processes (Dnnn) and shared server processes (Snnn). The listener directs a shared-server client to a dispatcher. The dispatcher places requests into a shared request queue; an available shared server executes the call and returns the response through the appropriate dispatcher response queue. Because different shared servers can execute successive calls from one session, session state cannot live only in one shared server’s private PGA.

Oracle’s current guidance is not “shared server is faster.” It adds complexity and can have feature/restriction tradeoffs. Use it when the number of concurrent connections creates a genuine process/resource problem that this architecture addresses. A database can support dedicated and shared connections at the same time.

SELECT name, value, issys_modifiableFROM   v$parameterWHERE  name IN ('shared_servers','max_shared_servers','dispatchers')ORDER  BY name;SELECT name, network, status, accept, messages, bytesFROM   v$dispatcherORDER  BY name;SELECT name, status, requests, messages, bytesFROM   v$shared_serverORDER  BY name;

Depending on the Free image and XDB configuration, you may see dispatcher/shared-server infrastructure even if ordinary ServiceHub SQL sessions are dedicated. Interpret V$SESSION.SERVER for the actual session rather than assuming the instance-wide presence of a dispatcher means every connection is shared.

5. Failure case: kill the OS PID because the “session is stuck”

An operator sees a long-running session, joins it to V$PROCESS.SPID, and immediately kills that OS process. This bypasses Oracle’s normal session-termination semantics and can trigger cleanup/recovery work. It is also dangerous if the process mapping was misread, the session is shared, or the problem is actually a blocked call that should be investigated first.

The safer workflow is: identify the exact SID,SERIAL#, inspect current SQL/wait/blocking/transaction state, decide whether termination is justified, and use supported database mechanisms such as ALTER SYSTEM KILL SESSION or DISCONNECT SESSION with the appropriate semantics. This chapter does not kill any ServiceHub session; Chapter 07 teaches blocking/deadlock diagnosis before termination.

SELECT s.sid, s.serial#, s.username, s.status, s.server,       s.sql_id, s.event, s.state,       p.spid, p.programFROM   v$session sLEFT JOIN v$process p ON p.addr = s.paddrWHERE  s.type = 'USER'ORDER  BY s.sid;

The SID alone is insufficient because Oracle can reuse it after a session ends. Pair SID with SERIAL# for session-level administrative commands so a recycled SID does not target the wrong session.

6. Hands-on lab: trace three ServiceHub sessions

Open three client connections to FREEPDB1: one SYSDBA/admin connection, one SERVICEHUB_OWNER connection, and one SERVICEHUB_APP connection. In each client, capture its SID and service context. From the admin connection, correlate those sessions with server processes.

SELECT USER AS database_user,       SYS_CONTEXT('USERENV','SID') AS sid,       SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name,       SYS_CONTEXT('USERENV','CON_NAME') AS con_nameFROM dual;

SELECT s.sid, s.serial#, s.username, s.server, s.status,       s.machine, s.program, s.service_name,       p.spid,       ROUND(p.pga_alloc_mem/1024/1024,2) AS pga_alloc_mbFROM   v$session sLEFT JOIN v$process p ON p.addr = s.paddrWHERE  s.username IN ('SERVICEHUB_OWNER','SERVICEHUB_APP')ORDER  BY s.username, s.sid;

Verification checklist:

• Each client can report its own session identifier and service/container.
• The admin session can distinguish sessions from OS server processes.
• You recorded SERVER rather than assuming dedicated mode.
• You can explain where UGA/session state lives in dedicated versus shared server.
• You did not grant SELECT_CATALOG_ROLE or broad dynamic-view access to the application users merely for the lab.

7. Production judgment

Connection architecture is a capacity and correctness decision. Pool sizes interact with PROCESSES/SESSIONS, authentication, memory, application concurrency, transaction duration, failover behavior, and network round trips. Shared server can reduce process pressure but adds dispatcher/queue behavior and restrictions. DRCP can provide another pooling boundary. Measure the workload and preserve service/session observability so a pool does not turn hundreds of user requests into anonymous database sessions.

Never size PGA_AGGREGATE_TARGET, process counts, or pool sizes from a single session snapshot. Oracle AI Database Free’s resource cap is intentionally small. Lesson 3 next explains how initialization parameter values are sourced, changed, and persisted so configuration changes do not disappear—or unexpectedly survive—after restart.

8. Summary and next step

A connection transports calls, a session holds logical database state, and a server process executes work. PGA is process-private; UGA is session state whose location depends on server architecture. Dedicated, shared, and pooled models change the mapping between clients, sessions, and workers. You now have a safe evidence workflow for correlating sessions and processes without treating an OS PID as the primary database identity.

Authoritative references

• Oracle AI Database Concepts — Memory Architecture — PGA, UGA, work areas, and session memory
• Oracle AI Database Concepts — Application and Networking Architecture — dedicated/shared-server request flow
• Oracle AI Database Reference — V$SESSION — session identifiers, server model, and process address
• Oracle AI Database Administrator’s Guide — Managing Processes — shared server and dispatcher configuration
• Oracle AI Database Reference — V$PGASTAT — instance PGA statistics