Skip to main content

Wait Activity (Oracle): Metric Reference

This page is the source of truth for the in-app Explain this panels on the Activity Explorer Wait Activity tab. Each section is written once as a content partial under _explain/ and rendered both here and inside the dashboard's info panel (scripts/build-explain.mjs compiles the registry).

The Wait Activity tab appears only for Oracle databases. It is driven by Oracle's Active Session History (ASH), which requires the Diagnostics Pack — see the requirements note under Active session time.

Active session time​

The total time sessions spent actively working — on CPU or in a wait — during the selected window, derived from Oracle's Active Session History (ASH).

How it's calculated​

  • Oracle samples every active session roughly once per second into v$active_session_history; each sample represents about one second of active session time.
  • Active session time = the number of ASH samples in the window. One session active for 60 seconds contributes ~60; three sessions active for 20 seconds each contribute the same.
  • The Logstag agent exports the ASH buffer once a minute, reading a trailing five-minute slice each time.

Requirements​

ASH is part of Oracle's Diagnostics Pack. This tab has data when the database edition and the control_management_pack_access parameter permit it (Enterprise and Free editions qualify; Standard, Express, and Personal do not). Without it, the tab reports no ASH data — that is a licensing state, not a collection failure.

Reading it​

This is Oracle's own measure of database load, in units of "session-seconds of work." Divide it by the window length to get average active sessions — the number Oracle tuning literature compares against CPU count. The CPU vs Wait split tells you whether that time was productive or spent waiting.

CPU vs Wait​

The share of active session time spent on CPU versus in a wait, over the selected window.

How it's calculated​

  • Each ASH sample records whether the session was on CPU or waiting on an event at the sampling instant.
  • The two percentages are the CPU-sample count and the wait-sample count divided by all samples in the window.

Reading it​

There is no universally "good" ratio — an OLTP system doing index lookups can healthily sit at high CPU, and a batch system streaming from disk will naturally show more IO wait. What matters is your baseline: a workload that flips from mostly-CPU to mostly-wait without a code change means something beneath it changed — storage latency, a lost index, or new contention. When wait dominates unexpectedly, the Top Wait Classes card names the culprit category.

Completed-wait sum (reference)​

The summed duration of waits that finished within the sampled window, shown in microseconds. It is a reference figure, deliberately secondary to Active session time.

How it's calculated​

  • Each ASH sample carries time_waited — the duration of the wait, filled in only once the wait has completed. While a wait is still in progress, Oracle reports it as zero.
  • This tile sums those completed-wait durations across the window's samples.

Why it's "reference only"​

Summing time_waited systematically undercounts: the longest, most interesting waits are precisely the ones still in progress when sampled, and they contribute zero. That is why Logstag measures activity by sample count (Active session time) instead, and keeps this sum visible only as a cross-check. If the completed-wait sum is small while wait-side Active session time is large, long in-flight waits are dominating — usually the signal to look at the Top Wait Events card's in-progress column.

Top Wait Classes​

The wait classes that consumed the most active session time in the selected window, as a horizontal bar per class.

How it's calculated​

  • Every ASH sample taken during a wait carries Oracle's wait class for that event — Oracle's own native taxonomy (User I/O, Concurrency, Commit, Network, and so on), not Logstag's shared five-category model used on the Wait Events tab.
  • Each class's bar is its sample count (≈ seconds of active session time), with the percentage of all wait samples alongside.
  • Samples taken on CPU belong to no wait class; when every sample was on CPU, this card reports no waits — that is a healthy result, not missing data.

Reading it​

This is the standard first cut of Oracle wait tuning: name the dominant class, then drill into its events. User I/O dominance points at storage or missing indexes; Concurrency at latch and lock contention; Commit at redo-log write latency; Network at round-trips to slow clients. The Top Wait Events card breaks the leading class into its specific events.

Top Wait Events​

The individual wait events that consumed the most active session time in the selected window — the event-level detail behind the wait-class bars.

Columns​

  • Event — the Oracle event name, exactly as Oracle reports it. This is the string to look up in Oracle documentation.
  • Wait class — the class the event belongs to.
  • Active s — active session time attributed to the event (ASH sample count, ≈ seconds).
  • Avg in-progress wait — the average age, in microseconds, of this event's waits at the moment they were sampled, for samples where the wait was still running.
  • Completed — how many sampled waits of this event had already finished when sampled.
  • Completed sum — the summed duration of those completed waits, in microseconds.

How it's calculated​

  • Aggregated from ASH samples in the window; ranked by active session time. The card shows the top events, not an exhaustive list.
  • The in-progress and completed columns exist because Oracle fills a wait's duration in only after it ends — a long wait shows up in the in-progress column while it runs, and moves its time into the completed sum only once done.

Reading it​

Rank by Active s and treat the top two or three rows as the story of the window. A large Active s with a small Completed sum means few, long waits still in flight — one stuck resource. A large Active s built from a large Completed count means many short waits — a throughput pattern, usually tuned in the application or schema rather than by hunting a single session.

Top SQL by ASH activity​

The SQL statements that accumulated the most active session time in the selected window, identified by Oracle SQL_ID.

Columns​

  • SQL_ID — Oracle's identifier for the statement. Use it to find the same statement in the Query Explorer or in Oracle's own views (v$sql, execution plans).
  • Active s — active session time attributed to the statement (ASH sample count, ≈ seconds), covering both CPU work and waits while it ran.

How it's calculated​

  • Each ASH sample that was executing SQL carries the statement's SQL_ID; samples are counted per SQL_ID and the top statements are shown.
  • Samples taken outside a SQL statement — background operations, idle-in-call time — carry no SQL_ID and are not attributed to any row. When no samples carried a SQL_ID, the card reports that nothing was captured.

Reading it​

This ranking answers "which statement is the database spending its time on?" — which is not always the slowest statement, but the one whose frequency × duration is largest. A statement at the top with a modest per-execution time is a volume problem; the Query Explorer's statistics view shows its call rate and history. A statement at the top of both this card and the wait-event ranking is the one to tune first.