Oracle can report high host CPU, many connected sessions, and a long list of wait events while none of those facts identifies why users are slow. The first useful question is where database time went during the incident.

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

Situation

Oracle exposes deep evidence, but depth creates a sequencing problem. An investigator can jump from latency to AWR, a top wait, SQL ID, or parameter before proving that the database consumed the missing time.

The stable starting point is the Oracle time model. DB time is cumulative CPU time and non-idle wait time spent processing user requests. Divide its increase by elapsed wall time and the result is average active sessions (AAS). Ten AAS means that, on average, ten foreground sessions were on CPU or waiting for non-idle events during the interval; it does not mean ten sessions were connected.

That decomposition constrains the LLM. Collectors calculate interval deltas and preserve scope; the model correlates application, host, workload, and change evidence.

The Problem

Most Oracle triage mistakes concern denominator or scope:

  • reading cumulative V$SYS_TIME_MODEL and V$SYSTEM_EVENT values as incident totals;
  • treating connected sessions as active database load;
  • ranking waits by count instead of time impact;
  • including idle waits such as clients waiting for work;
  • calling the event in V$SESSION a current wait without checking session state;
  • averaging RAC instances or CDB containers into one misleading value;
  • blaming Oracle CPU without reconciling database CPU with host capacity and other processes;
  • using AWR, ASH, ADDM, or Performance Hub without first proving Diagnostics Pack entitlement.

Application latency can rise while DB time stays flat. Calls may not reach the database, connection acquisition may stall, or throughput may collapse. Low DB load does not prove a healthy service.

Did the incident add work, make work more expensive, serialize sessions, starve Oracle at the host, or occur outside database calls?

Build a Time-Aligned Oracle Evidence Pack

flowchart TD
    A[application latency throughput errors and pool time] --> H[incident timeline and scope]
    B[DB time DB CPU and user calls] --> H
    C[foreground wait-class and event deltas] --> H
    D[active sessions services modules actions and SQL IDs] --> H
    E[blocking sessions and transaction context] --> H
    F[host CPU run queue memory and storage latency] --> H
    G[deployments jobs parameter and topology changes] --> H
    H --> I[deterministic interval calculations]
    I --> J[LLM evidence correlation]
    J --> K[ranked hypotheses contradictions and gaps]
    K --> L[DBA and application-owner verification]

Every counter needs start and end values, timestamps, startup identity, instance number, and container scope:

delta_C = C_end - C_start
AAS = delta_DB_time_microseconds / interval_microseconds
CPU_AAS = delta_DB_CPU_microseconds / interval_microseconds
Wait_AAS = AAS - CPU_AAS

Reject negative deltas and intervals crossing restart or reset boundaries. Preserve each RAC instance separately before calculating a cluster total. Preserve CON_ID where the source supplies it; root-level instance values and PDB-attributed values answer different questions.

Symptoms

Evidence patternLeading classificationWhat it does not prove
Application latency rises; DB time and calls stay flatConnection, network, queue, or application pathOracle is healthy end to end
DB time rises mostly as DB CPUCPU-demand growth or less efficient workHost CPU is exhausted
CPU AAS approaches available CPUs and host run queue risesCPU saturation or scheduling pressureOne SQL statement caused it
User I/O AAS rises with storage latencyRead path, access path, or storage pressureStorage is the root cause
Application wait AAS and blockers riseLock serializationKilling the blocker is the durable fix
Commit wait AAS risesForeground commit path pressureRedo device latency alone caused it
Cluster wait AAS rises on one RAC instanceGCS or GES work and workload placementRAC itself is misconfigured
AAS rises while business throughput fallsMore time per unit of work or serializationMore capacity will restore efficiency

First Five Checks

1. Freeze the incident contract

Record UTC start, transition, peak, recovery, database unique name, database and instance IDs, startup time, RAC role, service, CDB and PDB scope, version, platform, and known changes. Capture user-facing latency, completed work, errors, retries, connection-pool wait, and request volume.

Define success in application terms. Oracle’s method starts with throughput or response time, not an internal ratio. Without completed work, lower DB time after an outage can look like improvement.

2. Measure DB time, DB CPU, and workload as deltas

Take bounded snapshots from dynamic performance views when permitted:

SELECT stat_name, value
FROM v$sys_time_model
WHERE stat_name IN ('DB time', 'DB CPU');

V$SYS_TIME_MODEL reports microsecond accumulations. Subtract matched snapshots, then compare AAS with calls, executions, commits, and business throughput. Increased AAS with proportional throughput may be legitimate load growth. Increased AAS with flat or falling throughput indicates lost efficiency or serialization.

Do not force a perfect identity between DB time, DB CPU, and summed waits. Sampling boundaries, publication delay, background work, nested timing, and scope differences create gaps. Treat reconciliation variance as a quality signal to explain, not a number to conceal.

3. Decompose non-idle foreground wait time

Capture TIME_WAITED_MICRO_FG and TOTAL_WAITS_FG from V$SYSTEM_EVENT at both boundaries, excluding Idle, then rank deltas by time. Wait count without duration overweights frequent short events. Instance-wide lifetime totals overvalue old history.

Begin with classes: Application, Commit, Concurrency, Configuration, Cluster, Network, Scheduler, System I/O, and User I/O. Then expand the dominant class into events and parameters. A wait event describes where time accumulated, not automatically why. For example, User I/O can reflect an inefficient access path, lost cache locality, changed workload, or storage latency.

4. Reconcile Oracle CPU with the host

Compare CPU AAS, database CPU per second, CPU count available to the instance, host busy and idle CPU, run queue, cgroup or VM limits, and non-Oracle consumers. V$OSSTAT exposes selected OS statistics, but Oracle notes that availability is platform-dependent; retain native OS or cloud telemetry.

If DB CPU rises but host idle capacity remains, investigate SQL and workload efficiency. If CPU AAS presses against usable CPU while the run queue and latency rise, saturation is plausible. If host CPU is high but DB CPU is flat, look for other instances, agents, backup or compression work, storage polling, or a virtualization boundary.

5. Attribute active time without overclaiming

For a live incident, inspect active sessions by instance, container, service, module, action, SQL ID, state, wait class, event, and blocker. V$SESSION_WAIT reports the current or last wait; when STATE is not WAITING, its event is historical context, not evidence that the session is blocked now.

Where licensed, ASH samples active sessions once per second and preserves dimensions such as SQL ID, module, state, and event. It is a sampled workload distribution, not an exact statement ledger: long-lived activity is more likely to appear than very short activity. Use it to rank hypotheses, then verify with session, SQL, plan, and application evidence.

Decision Tree

flowchart TD
    A[user-visible slowdown] --> B{DB time per second increased}
    B -->|no| C{database calls or throughput fell}
    C -->|yes| D[inspect pool network routing and application]
    C -->|no| E[verify window scope and telemetry gaps]
    B -->|yes| F{CPU share dominates}
    F -->|yes| G{host CPU capacity constrained}
    G -->|yes| H[classify database and non-database CPU demand]
    G -->|no| I[find service module and SQL driving CPU]
    F -->|no| J[rank non-idle foreground wait deltas]
    J --> K{one class dominates}
    K -->|no| L[segment by instance service container and phase]
    K -->|yes| M[expand events sessions blockers and workload]
    M --> N[collect missing proof and validate hypothesis]

In Practice

Oracle defines DB time as cumulative CPU and non-idle wait time processing user requests. Its user-activity guidance defines AAS as DB time divided by elapsed time and decomposes AAS by CPU, wait class, instance, service, module, action, session, and SQL ID. The documented model supports correlation; it does not license a shortcut from top wait to root cause.

Oracle’s V$SYSTEM_EVENT includes foreground wait counts and microsecond wait time, while V$SESSION_WAIT distinguishes WAITING from a completed previous wait. These behaviors make interval arithmetic and session state mandatory evidence-quality checks.

The Oracle performance method recommends confirming a bottleneck, changing one thing where possible, and validating the user’s experience. That maps directly to this series: deterministic observation, ranked hypotheses, additional proof, controlled remediation, and post-change validation.

Licensing is part of collector design. Oracle’s 26ai licensing manual places AWR, ADDM, ASH, V$ACTIVE_SESSION_HISTORY, DBA_HIST_* data with listed exceptions, and Performance Hub within Diagnostics Pack. Direct SQL access still requires entitlement. Real-time SQL Monitoring belongs to Tuning Pack, which also requires Diagnostics Pack. CONTROL_MANAGEMENT_PACK_ACCESS controls availability; it is not evidence that the organization purchased the required licenses.

Remediation Options

Proven conditionCandidate responseValidation
Calls stall outside DB while DB time is flatRepair pool, routing, listener, or application pathEnd-to-end latency and completed calls recover
CPU demand grew with one workload dimensionReduce, defer, or tune that workloadDB CPU per business operation falls
Host scheduling constrains OracleRemove competing load or add approved capacityRun queue falls and throughput improves
User I/O is access-path drivenTune SQL or indexing after plan proofReads and DB time per execution fall
Lock serialization is provenCorrect transaction scope or access orderBlocked time and business latency fall
Commit time is workload drivenBatch safe commits or reduce redo demandCommit AAS falls without durability loss
Evidence window is incompleteCollect another bounded intervalCounters reconcile within documented scope

Rollback Plan

Triage collection should be read-only. If it adds pressure, stop the collector, close its sessions, and remove temporary sampling frequency changes. Restore logging, monitoring, and snapshot settings through the normal change path. Preserve the incident window and collector manifest.

For remediation, define rollback before execution: workload-routing reversal, job re-enable, parameter restoration, capacity reversal, or application release rollback. Reversing an index or SQL-plan intervention may require a forward correction rather than an immediate inverse. Stop when application health, throughput, or a guardrail worsens, then remeasure DB time and its decomposition.

Where It Breaks

Failure modeWhy the conclusion failsBetter evidence
Highest lifetime wait is called the incidentCumulative history crosses the windowMatched foreground deltas
AAS is compared only with CPU countWait AAS can legitimately exceed coresSeparate CPU and wait components
High CPU utilization is called saturationUtilization lacks run-queue and capacity contextCPU AAS, host idle, queue, throughput
Top wait is called root causeWait location has several causal pathsEvent parameters, sessions, workload, host
Current-session snapshot explains the pastTransient activity disappearedLicensed ASH or external bounded sampling
AWR endpoints bracket the incident looselyAverages dilute the transition and peakPhase-aligned windows and finer evidence
RAC totals hide instance skewCluster sum removes placement contextPer-instance evidence before aggregation
LLM receives a report without scope metadataIt cannot distinguish deltas, resets, or containersTyped evidence manifest and contradictions

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: Oracle exposes many plausible symptoms, but cumulative counters, idle waits, current-session snapshots, and mixed scopes can produce a confident diagnosis of the wrong interval.
  • Solution: Begin with user impact, calculate DB time and CPU as interval deltas, decompose non-idle foreground waits, reconcile the host, and attribute activity by instance, container, service, module, and SQL ID.
  • Proof: Recalculate the same incident independently from source snapshots. The AAS decomposition, workload change, wait deltas, host evidence, and application outcome should support the same ranked cause while contradictory evidence remains visible.
  • Action: Build two collectors now—time-model deltas and foreground wait deltas—with startup, instance, container, and licensing metadata before sending Oracle evidence to an LLM.

The next article moves from a ranked SQL ID to proof: execution history, plan hash changes, cardinality estimates, runtime rows, bind behavior, and controlled plan remediation.

Sources