A stable execution plan and stable buffer gets do not make Oracle healthy. Sessions can spend most of their time waiting for another transaction, redo durability, a remote RAC block, or an infrastructure path that no SQL rewrite can repair.

Technology and product capabilities in this series are evaluated as of June 30, 2026.

Situation

The previous article required child-cursor identity, normalized work, executed plans, actual rows, and a reproducible mechanism. Sometimes that investigation finds no meaningful change in work per execution.

That result narrows the incident; it does not end it. Foreground time may have moved into Application, Commit, Concurrency, Cluster, or User I/O. Each class describes where sessions waited, but not one mechanism.

Collectors preserve blocker relationships, redo counters, RAC identity, file I/O, host pressure, and cloud metrics. The LLM ranks causal paths and gaps; the DBA decides what the evidence proves.

The Problem

Wait names tempt investigators into immediate prescriptions:

enq: TX - row lock contention  → kill the blocker
log file sync                  → buy faster storage
gc cr block busy               → tune the interconnect
db file sequential read        → add IOPS

Every arrow can be wrong. A TX wait can involve a duplicate key or bitmap fragment. log file sync includes LGWR coordination and group commit. A global-cache block can await remote processing or redo flush. An access path can request more I/O from a healthy device.

High cloud storage utilization or RAC traffic can also coexist without affecting DB time. Correlation needs scope, timing, rate, latency, and affected sessions.

How do we prove that SQL efficiency stayed stable and identify the non-SQL mechanism without turning a wait-event label into root cause?

Build a Layered Non-SQL Evidence Pack

flowchart TD
    A[application latency throughput errors and retries] --> H[time-aligned incident evidence]
    B[SQL work per execution and returned rows] --> H
    C[blocked sessions final blockers and transactions] --> H
    D[redo rate commits log writes and wait histograms] --> H
    E[RAC block transfers objects services and interconnect] --> H
    F[file I/O latency throughput queue and errors] --> H
    G[cloud compute storage network events and throttling] --> H
    H --> I[deterministic scope and interval checks]
    I --> J[LLM correlation and contradiction analysis]
    J --> K[DBA and platform-owner verification]

Require the model to separate four outputs:

OBSERVATION  final blocker 184 held a transaction through the latency interval
HYPOTHESIS   application transaction scope serialized checkout updates
MISSING      request owner, lock resource, transaction age, and commit path
ACTION       trace the blocker to module and application transaction boundary

Retain instance, container, service, source, counter semantics, interval, sampling rate, licensing class, and redaction status. Never present five-minute cloud averages as one-second database samples.

Symptoms

Evidence patternLeading pathWhat it does not prove
Many waiters converge on one final blockerTransaction serializationThe blocker should be killed
Commit AAS rises with commits per secondCommit-frequency pressureRedo storage is slow
log file sync tail rises with log file parallel write tailLocal redo-write pathThe volume alone caused it
Cluster AAS and remote block service rise on one serviceRAC placement or shared-block pressureRAC is misconfigured
gc current block busy concentrates on one objectCross-instance write contentionInterconnect latency is primary
User I/O time rises across many stable SQL childrenStorage or cache pathMore provisioned IOPS will help
OCI throttled operations overlap file latencyBlock Volume constraintAll database files share that volume
Host pressure rises but DB CPU stays flatNon-database or virtualization pathOracle needs more CPU

First Five Checks

1. Prove that SQL work remained comparable

Compare child-cursor executions, rows, gets, reads, CPU, elapsed time, plans, and bind classes across baseline and incident. Stable work supports a non-SQL path, but the same plan can wait differently or encounter another cache state.

Reconcile application throughput: fewer executions can mean pool waits, while retries amplify locks or commits. Scope by service, module, action, PDB, instance, and phase.

2. Reconstruct the blocking graph

Capture GV$SESSION blocker status, blocking and final-blocking identity, event, transaction, current and previous SQL, service, module, action, and container. V$SESSION_BLOCKERS emits pairs and can report multiple blockers, up to 30 per blocked session.

The final blocker’s current SQL may be unrelated or idle after acquiring the lock. Connect its transaction, previous SQL, request, age, and commit behavior. Distinguish row modification, unique-key coordination, and bitmap-fragment contention before changing code.

Session termination is containment with consistency risk. It removes a holder, not the transaction design.

3. Decompose foreground commit time from redo work

log file sync covers commit redo flush and LGWR’s completion post. log file parallel write measures LGWR I/O until the last parallel write finishes. Compare deltas and histograms with commits, redo size and writes, log switches, archive pressure, synchronous transport, and throughput.

Group commit breaks one-to-one reasoning: one slow redo write can release many sessions. Preserve histogram deltas instead of relying on averages.

Changed commit frequency with stable redo-write latency favors transaction granularity. Aligned redo-write tails, device latency, throttling, and commit latency favor the write path. Asynchronous commit changes durability semantics and requires explicit business approval.

4. Separate RAC transfer, contention, and network paths

Analyze RAC per instance and service. Compare Cluster AAS, blocks received, transfer outcomes, busy and congested events, interconnect traffic, node CPU, and objects behind file and block parameters.

Cache Fusion uses the private interconnect. A completed gc cr block 2-way is not automatically faulty. Busy events mean the serving instance could not ship immediately; remote pinning, redo flush, or concurrency can contribute. Lost-block events more directly implicate loss, congestion, hardware, overload, or network path.

Ongoing requests are placeholders renamed after the outcome. Do not compare them directly with completed-event totals. Test hot objects, service placement, cross-instance writes, and node load before changing GCS processes or network configuration.

5. Join Oracle I/O with host and cloud evidence

Separate foreground User I/O from background System I/O. Correlate files, operations, and latency with OS queues, errors, throughput, multipathing, ASM or Exadata, backups, and the exact cloud attachment.

OCI database metrics expose instance IOPS and throughput. Block Volume adds operations, throughput, guaranteed performance, and VolumeThrottledIOs by attachment or resource. Different intervals and scopes matter: a five-minute mean hides short stalls, and a busy volume may host unrelated files.

On Exadata, include cell and storage-server evidence. Across clouds, preserve service events, maintenance, scaling, and topology changes; provider metrics complement Oracle file and wait evidence.

Decision Tree

flowchart TD
    A[latency rises with stable SQL efficiency] --> B{Application waits dominate}
    B -->|yes| C[build blocker graph and transaction timeline]
    B -->|no| D{Commit waits dominate}
    D -->|yes| E[compare commit demand redo writes tails and transport]
    D -->|no| F{Cluster waits dominate}
    F -->|yes| G[segment instances services blocks and outcomes]
    F -->|no| H{I/O waits dominate}
    H -->|yes| I[join file device and cloud evidence]
    H -->|no| J[inspect concurrency configuration network and scheduler]
    C --> K[test application transaction mechanism]
    E --> K
    G --> K
    I --> K
    J --> K
    K --> L[smallest reversible response and validation]

In Practice

Oracle’s wait-class reference separates Application, Commit, Cluster, Concurrency, System I/O, and User I/O. Its enq: TX - row lock contention documentation lists row modification, unique-key coordination, and shared bitmap-index fragments as possible causes. The documented behavior requires resource and transaction evidence, not a generic locking diagnosis.

Oracle defines log file sync as foreground commit coordination with LGWR and log file parallel write as redo-log I/O completion. Its redo transport troubleshooting guidance shows why group commit and outliers can make average foreground wait time misleading. The derived operational rule is to correlate histograms, redo writes, transaction rate, and application impact.

Oracle’s RAC performance guidance states that many high-total global-cache waits are normal and distinguishes block, message, contention, and load events. Its RAC instance architecture describes Cache Fusion as block transfer through the private interconnect. This supports object, service, instance, and outcome analysis before an interconnect conclusion.

OCI’s database metrics include instance dimensions for IOPS, throughput, RAC block receipt, interconnect traffic, DB time, and wait time under their documented management options. Block Volume metrics separately expose operations, throughput, guaranteed performance, and throttling. Their different namespaces and intervals make metric lineage mandatory.

AWR, ASH, ADDM, Performance Hub, and historical views require Diagnostics Pack; SQL Monitoring requires Tuning Pack and Diagnostics Pack. Confirm entitlement. Dynamic views, OS telemetry, and cloud metrics require least-privilege access and data handling controls.

Remediation Options

Proven mechanismCandidate responseValidation
Application transaction holds rows too longShorten transaction scope or correct access orderBlocking time and request latency fall
Duplicate-key coordination creates TX waitsRedesign key allocation or idempotency pathWaiters fall without duplicate writes
Commit frequency drives healthy redo path hardBatch commits only where semantics permitCommits fall while durability and throughput hold
Redo write path has verified tail latencyRepair storage, transport, or log placementBoth redo-write and commit tails recover
RAC service placement causes hot-block transferRebalance service or partition ownershipRemote block demand falls without node overload
Interconnect loss or congestion is provenRepair private-network path or capacityLost events and transfer tails recover
File latency aligns with cloud throttlingRaise approved performance or move the pathFile latency and throttled operations fall
Backup or maintenance overlaps foreground I/OReschedule or rate-limit the workForeground latency improves in repeated window

Rollback Plan

Define a reversal for every intervention: restore service placement, transaction batch size, log or storage configuration, volume performance, job schedule, network policy, or application release. Record previous values and ownership before change. Capacity changes may be reversible technically but still alter cost commitments.

Do not use session termination, instance restart, cache flush, or RAC relocation as an unlabelled diagnostic test. If emergency containment requires one, preserve evidence first, define application consistency checks, and distinguish recovery from root-cause remediation.

Stop when errors, recovery risk, commit durability, cross-instance imbalance, queue depth, or application latency worsens. After rollback, verify the active configuration and repeat the database, host, and cloud measurements; a successful API response is not proof that the data path reverted.

Where It Breaks

Failure modeWhy the conclusion failsBetter evidence
Blocker is killed and called root causeHolder removal does not explain transaction designWait chain, request ownership, and lock resource
log file sync average drives storage purchaseGroup commit and outliers distort averagesHistogram deltas, LGWR I/O, commits, throughput
High RAC transfer rate is called contentionUseful work can transfer many blocks normallyResponse time, busy outcomes, objects, baseline
Cluster wait is blamed on networkRemote CPU, pinning, redo flush, and hot blocks contributePer-instance outcome and infrastructure evidence
User I/O means cloud storage faultSQL demand and cache state affect foreground readsStable work plus file and device latency
Cloud mean is aligned to a short spikeAggregation dilutes or shifts the eventRaw interval metadata and phase overlap
LLM receives object and bind detailsDiagnostic data can expose sensitive design or valuesRestricted original and sanitized evidence pack

What the LLM Cannot Do

Explicit operational boundaries govern the LLM:

  • No Raw Customer Data: The LLM must not receive bind capture values from V$SQL_BIND_CAPTURE, literal SQL text, or row data. Send SQL_ID, force-matching signature, plan hash values, wait-event timings, and DB Time attribution only.
  • No Autonomous Production Execution: The LLM cannot run ALTER SYSTEM, ALTER SESSION on other sessions, kill sessions, apply SQL profiles or baselines, or gather statistics. It proposes; a human authorizes, applies, validates, and holds the rollback condition. Note also that AWR and ASH access carries Diagnostics Pack licensing implications — confirm entitlement before any collector reads them.
  • No Causal Conclusion Without Contradicting Evidence: Every hypothesis must cite both the telemetry that supports it and the telemetry that would falsify it. A ranked hypothesis with no disconfirming test attached is an assertion, not a diagnosis.
  • No Silent Gap-Filling: Where the evidence pack lacks a required signal, the LLM must name it explicitly under missing evidence rather than inferring a plausible value. “Not collected” and “collected and normal” are different findings and must never be merged.
  • No Change Without a Human Gate: Production changes require human authorization, a stated validation signal, and a defined rollback condition agreed before the change is applied.

What to Do Next

  • Problem: Stable SQL work can coexist with severe Oracle latency when sessions serialize on transactions, durability, remote blocks, storage, or infrastructure.
  • Solution: Preserve blocker graphs, redo distributions, RAC outcomes, file-level I/O, and cloud lineage in one time-aligned evidence pack while keeping each causal path distinct.
  • Proof: Show that the selected mechanism explains affected sessions, incident timing, scope, and application impact, then demonstrate recovery after the smallest reversible intervention.
  • Action: Add blocker-pair, commit-histogram, RAC-instance, file-attachment, cloud-namespace, interval, and contradiction fields to the Oracle collector before asking an LLM for remediation.

The next Oracle article defines diagnostic access, redaction, licensing, audit, and production-action boundaries.

Sources