$ guides / oracle-database / how-oracle-database-works-in-production ▌

Operations Guides

How Oracle Database actually works in production: a mental model for operators

Oracle is dense, but four abstractions make most production incidents legible: the shared memory region (SGA), per-process private memory (PGA), the background processes that move data to disk, and the wait-event model that tells you where time is going. When you see log file sync climbing, or free buffer waits appearing, or the database hanging while basic health checks still pass, you should know immediately which subsystem is involved and what it competes for.

What it is and why it matters

Oracle is a multi-process architecture on Unix/Linux (multi-threaded on Windows) built around a single shared memory region called the System Global Area (SGA) and per-session private memory called the Program Global Area (PGA). In dedicated server mode, every connected session gets its own server process and PGA. Background processes run alongside, moving data between the SGA and disk.

Every Oracle incident maps back to one of these layers. A “slow database” complaint is almost always one of three root causes: LGWR cannot flush redo fast enough, DBWn cannot write dirty buffers fast enough, or a session is holding a lock others need. Knowing which layer owns the symptom is the difference between a five-minute fix and a two-hour war room.

How it works

The SGA: shared memory

The SGA is the shared workspace all sessions see. It is the largest memory consumer and the source of most tuning complexity.

Component	Purpose	What breaks if starved
Buffer Cache	Caches data blocks from datafiles. Every read and write checks here first.	Physical I/O floods storage; `db file sequential read` / `db file scattered read` waits spike
Shared Pool	Library cache (parsed SQL plans), data dictionary cache, result cache. Hard parses require exclusive latches.	`library cache: mutex X` waits, ORA-04031 shared pool OOM, CPU spike from parsing
Redo Log Buffer	Circular buffer for change vectors before LGWR flushes to online redo logs. Small (typically 16-256MB), but on the critical commit path.	Redo log buffer space waits (rare; LGWR usually keeps up)
Large Pool	RMAN I/O buffers, shared server session memory, parallel query message buffers.	RMAN backup failures, parallel query degradation
Java Pool / Streams Pool	Java stored procedures, GoldenGate integrated capture.	Only relevant if these features are active

The buffer cache is the single most important cache. The shared pool is the single most fragile, because hard parsing requires exclusive latches and fragmentation leads to ORA-04031.

PGA: per-process memory

Each dedicated server process gets its own PGA for sorting, hashing, bitmap operations, and session state. PGA is managed via PGA_AGGREGATE_TARGET (a soft target) and PGA_AGGREGATE_LIMIT (a hard limit, 12c+). When PGA is insufficient for a sort or hash join, the operation spills to the temp tablespace, visible as direct path read temp and direct path write temp waits. On analytics workloads, PGA aggregate can rival the SGA in size.

Critical background processes

Process	What it does	What breaks if it stalls
DBWn	Writes dirty buffers from buffer cache to datafiles	Buffer cache fills, `free buffer waits` appear, all DML stalls
LGWR	Writes redo log buffer to online redo logs	Every COMMIT hangs (`log file sync`), entire application stalls
CKPT	Signals DBWn to write, updates datafile headers	Recovery time grows; `checkpoint not complete` warnings
SMON	Instance recovery, coalesces free extents, cleans temp	Dead transactions not rolled back; temp not reclaimed
PMON	Cleans up failed user processes, releases their locks	Orphaned locks persist indefinitely
ARCn	Copies filled online redo logs to archive destination	Redo logs cannot be reused, database hangs
MMON/MMNL	AWR snapshots, ASH sampling	Diagnostics blind; not operationally critical
RECO	Resolves in-doubt distributed transactions	Distributed transaction locks not released
CJQ0/Jnnn	Job scheduler	Scheduled jobs stop executing
LCKn/LMSn/LMD	RAC only: Global Cache/Enqueue Service	RAC cluster communication fails

The connection lifecycle

Client connects to the Listener (typically port 1521).
The Listener spawns a dedicated server process (or routes to a shared server dispatcher).
Each dedicated server gets its own PGA and one OS process.
PROCESSES and SESSIONS parameters hard-limit concurrency. Hitting them causes ORA-00020.

Each dedicated server process consumes one OS process and one PGA allocation. OS limits (ulimit, pid_max) can be hit before Oracle’s own limits. The listener is a single point of failure for new connections: if it is down, existing sessions are unaffected but no new connections can be established. Background processes consume a variable number of process slots — the count depends on enabled features such as RAC or Data Guard, and is visible in V$BGPROCESS — reducing what is available for users.

The redo and undo write path

This is the mechanism that connects most background processes into a single chain. Understanding it explains why one slow link freezes everything.

Every data change generates two things:

A redo vector (for recovery) written to the redo log buffer in the SGA.
An undo record (for rollback and read consistency) written to undo segment blocks in the buffer cache, persisted to the undo tablespace by DBWn.

COMMIT triggers LGWR to flush the redo log buffer to the online redo logs. LGWR must finish writing before the commit returns to the client. This means write throughput is gated by LGWR performance, and redo log I/O is the most latency-sensitive path in the system.

flowchart LR
  S[Session DML] -->|dirty block| BC[Buffer Cache]
  S -->|redo vector| RB[Redo Log Buffer]
  S -->|undo record| BC
  RB -->|LGWR on commit| OL[Online Redo Logs]
  OL -->|ARCn after switch| AD[Archive Dest]
  BC -->|DBWn checkpoint| DF[Datafiles]
  CKPT[CKPT] -.->|signals| DBWn[DBWn]

Two consequences follow. First, if LGWR is slow, every committing session waits uniformly. Second, if ARCn cannot archive filled online redo logs, LGWR cannot switch to a new log group, and every session needing redo space freezes.

Undo has its own failure modes. Long-running queries need undo blocks to remain available for read consistency. If undo is overwritten before the query finishes, you get ORA-01555 (snapshot too old). If the undo tablespace fills with active extents, you get ORA-30036 and writes fail.

The wait event model

Oracle’s primary diagnostic framework is wait events. Every session is either ON CPU or waiting for something. The views V$SESSION_EVENT (per-session accumulated), V$SYSTEM_EVENT (system-wide since startup), and V$ACTIVE_SESSION_HISTORY (sampled, requires Diagnostics Pack) expose this.

Wait times tell you where time is being spent. Hit ratios do not. Oracle’s performance methodology since 10g explicitly de-emphasizes cache hit ratios in favor of wait event analysis. When you triage, the fastest first step is to look at the dominant wait class among active sessions:

Wait class	What it means
Commit	LGWR bottleneck. Look at `log file sync` and `log file parallel write`.
User I/O	Storage. Look at `db file sequential read` and `db file scattered read`.
Concurrency	Locks or latches. Look at `enq: TX` and `library cache: mutex X`.
Application	Enqueue waits from application logic. Look for uncommitted transactions.
Idle	Not a performance problem. `SQL*Net message from client` means the database is waiting for the client.

Always filter idle waits (WAIT_CLASS != 'Idle') in performance analysis. SQL*Net message from client dominates V$SYSTEM_EVENT by count on most systems and is just the database waiting for the client to send the next request.

Where it shows up in production

Archetype	What it looks like	Which layer
LGWR cannot keep up	Every committing session waits on `log file sync`. The most common Oracle performance emergency.	Redo path
Archive destination full	ARCn cannot write, online redo logs fill, database hangs silently. Existing sessions freeze, new non-SYSDBA connections get ORA-00257.	Archive path
Space exhaustion	Tablespace full (ORA-01653/01654), temp full (ORA-01652), undo full (ORA-30036). Cliff-edge, no graceful degradation.	Storage
Lock contention cascade	One uncommitted transaction holds row locks, sessions queue, process/session limits approached.	Concurrency
Parse storm	Literal SQL causes hard parses, library cache mutex contention, shared pool fragmentation, eventually ORA-04031.	Shared pool
Plan regression	Optimizer picks a bad plan after stats collection. A query goes from 10ms to 10 minutes.	Optimizer
Connection exhaustion	PROCESSES/SESSIONS limit hit. ORA-00020 refuses new connections.	Process slots
Memory pressure / OOM	SGA + PGA exceed physical RAM. Linux OOM killer targets Oracle processes. Random session deaths.	Memory
RAC inter-node thrashing	Hot blocks bounced between instances. `gc buffer busy` waits dominate.	RAC interconnect

The most dangerous pattern is the archive destination full scenario. It masquerades as “database up.” The instance is OPEN, the listener responds, TCP checks pass. But every session needing redo is frozen on log file switch (archiving needed), and applications with existing connections get no error. They just hang.

Common misuses of the mental model

Monitoring hit ratios instead of wait events. Buffer cache hit ratio is the most over-relied-upon Oracle metric. A 99% ratio means nothing if log file sync is 50ms. A system doing nothing but SELECT * FROM dual in a loop has a 99.99% hit ratio. A system doing massive parallel analytics might have 60% and be performing perfectly. Focus on where time is spent.

Treating all ORA- errors equally. ORA-00600 (internal error) and ORA-07445 (segfault in Oracle code) are always critical and require Oracle Support engagement. ORA-01555 (snapshot too old) is an undo tuning signal. ORA-04031 (shared pool OOM) is urgent but fundamentally different from corruption. Triage by error code.

Using instance status as the only availability signal. An instance that is OPEN but hung passes basic health checks. A SELECT 1 FROM DUAL from an existing connection pool may succeed even during a total LGWR stall because it generates no redo. A meaningful health check must execute a DML statement with a COMMIT to exercise the full write path.

Shared pool flushes as a fix. ALTER SYSTEM FLUSH SHARED_POOL causes a hard parse storm and is almost never the right action. It creates more problems than it solves. The exception is a one-time intervention for documented shared pool corruption, not a recurring maintenance task.

Static thresholds on baseline-dependent metrics. TPS, logical reads, and redo generation rate are all workload-dependent. Static thresholds generate noise. Use baseline deviation, ideally a 7-day rolling baseline by hour of day.

Signals to watch in production

Signal	Why it matters	Warning sign
`log file sync` average wait	Commit latency. The single most important Oracle performance signal.	>5ms on SSD, >20ms on SAN sustained
`log file parallel write` average wait	LGWR’s actual I/O time. Compare with `log file sync` to isolate storage from scheduling.	Elevated alongside `log file sync` means storage problem
Active sessions vs CPU cores	Primary load measure. Equivalent to load average on Linux.	Active sessions sustained at more than 2x CPU cores
Redo log switch frequency	Direct indicator of redo throughput pressure.	More than 6 switches per hour with current redo log sizing
Archive destination status and space	Silent-hang risk when it fills.	STATUS = ERROR or destination more than 95% full
Undo tablespace ACTIVE extents	Write-path failure risk.	ACTIVE undo more than 85% of tablespace
Session and process utilization vs limits	Cliff-edge at ORA-00020.	Current utilization above 85% of limit
Hard parse rate	Shared pool health.	Hard parses above 100/sec sustained or above 5% of total parses
`enq: TX` waits with INACTIVE blocker	Lock cascade from uncommitted transactions.	Any blocker with more than 10 waiters

V$ACTIVE_SESSION_HISTORY, AWR, and ADDM require the Diagnostics Pack license (Enterprise Edition only). Querying these without a license is a compliance violation that Oracle audits catch.

How Netdata helps

Correlating log file sync with log file parallel write across the same time window isolates redo storage latency from LGWR scheduling overhead.
Trending redo log switch frequency and archive destination free space together surfaces the approach to an archive hang before the database freezes.
Active session count tracked per second, broken down by dominant wait class, gives the fastest triage signal: Commit means LGWR, User I/O means storage, Concurrency means locks.
Tablespace and undo utilization trends with configurable thresholds catch cliff-edge failures (ORA-01653, ORA-30036, ORA-01555) before they hit.
OS-level signals (CPU run queue, I/O latency per device, memory pressure, OOM killer activity) alongside database wait events make it obvious whether a bottleneck is inside Oracle or underneath it.
Session and process utilization against PROCESSES limits, tracked over time, prevents the ORA-00020 connection exhaustion cliff.

See Oracle Database monitoring with Netdata for per-second metrics collection and alerting.

The Netdata solution

Oracle Database monitoring with Netdata

Netdata monitors Oracle Database with per-second metrics and automatic dashboards. Watch wait events, redo and archive-log activity, tablespace and undo space, and session and lock activity so the failure modes in these runbooks surface before the instance hangs.

See Oracle monitoring → Start monitoring free