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.

ComponentPurposeWhat breaks if starved
Buffer CacheCaches 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 PoolLibrary 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 BufferCircular 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 PoolRMAN I/O buffers, shared server session memory, parallel query message buffers.RMAN backup failures, parallel query degradation
Java Pool / Streams PoolJava 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

ProcessWhat it doesWhat breaks if it stalls
DBWnWrites dirty buffers from buffer cache to datafilesBuffer cache fills, free buffer waits appear, all DML stalls
LGWRWrites redo log buffer to online redo logsEvery COMMIT hangs (log file sync), entire application stalls
CKPTSignals DBWn to write, updates datafile headersRecovery time grows; checkpoint not complete warnings
SMONInstance recovery, coalesces free extents, cleans tempDead transactions not rolled back; temp not reclaimed
PMONCleans up failed user processes, releases their locksOrphaned locks persist indefinitely
ARCnCopies filled online redo logs to archive destinationRedo logs cannot be reused, database hangs
MMON/MMNLAWR snapshots, ASH samplingDiagnostics blind; not operationally critical
RECOResolves in-doubt distributed transactionsDistributed transaction locks not released
CJQ0/JnnnJob schedulerScheduled jobs stop executing
LCKn/LMSn/LMDRAC only: Global Cache/Enqueue ServiceRAC cluster communication fails

The connection lifecycle

  1. Client connects to the Listener (typically port 1521).
  2. The Listener spawns a dedicated server process (or routes to a shared server dispatcher).
  3. Each dedicated server gets its own PGA and one OS process.
  4. 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 , 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 classWhat it means
CommitLGWR bottleneck. Look at log file sync and log file parallel write.
User I/OStorage. Look at db file sequential read and db file scattered read.
ConcurrencyLocks or latches. Look at enq: TX and library cache: mutex X.
ApplicationEnqueue waits from application logic. Look for uncommitted transactions.
IdleNot 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

ArchetypeWhat it looks likeWhich layer
LGWR cannot keep upEvery committing session waits on log file sync. The most common Oracle performance emergency.Redo path
Archive destination fullARCn cannot write, online redo logs fill, database hangs silently. Existing sessions freeze, new non-SYSDBA connections get ORA-00257.Archive path
Space exhaustionTablespace full (ORA-01653/01654), temp full (ORA-01652), undo full (ORA-30036). Cliff-edge, no graceful degradation.Storage
Lock contention cascadeOne uncommitted transaction holds row locks, sessions queue, process/session limits approached.Concurrency
Parse stormLiteral SQL causes hard parses, library cache mutex contention, shared pool fragmentation, eventually ORA-04031.Shared pool
Plan regressionOptimizer picks a bad plan after stats collection. A query goes from 10ms to 10 minutes.Optimizer
Connection exhaustionPROCESSES/SESSIONS limit hit. ORA-00020 refuses new connections.Process slots
Memory pressure / OOMSGA + PGA exceed physical RAM. Linux OOM killer targets Oracle processes. Random session deaths.Memory
RAC inter-node thrashingHot 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

SignalWhy it mattersWarning sign
log file sync average waitCommit latency. The single most important Oracle performance signal.>5ms on SSD, >20ms on SAN sustained
log file parallel write average waitLGWR’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 coresPrimary load measure. Equivalent to load average on Linux.Active sessions sustained at more than 2x CPU cores
Redo log switch frequencyDirect indicator of redo throughput pressure.More than 6 switches per hour with current redo log sizing
Archive destination status and spaceSilent-hang risk when it fills.STATUS = ERROR or destination more than 95% full
Undo tablespace ACTIVE extentsWrite-path failure risk.ACTIVE undo more than 85% of tablespace
Session and process utilization vs limitsCliff-edge at ORA-00020.Current utilization above 85% of limit
Hard parse rateShared pool health.Hard parses above 100/sec sustained or above 5% of total parses
enq: TX waits with INACTIVE blockerLock 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.