ASH Blocking Sessions — Architecture

How one shell script and one read-only PL/SQL block turn DBA_HIST_ACTIVE_SESS_HISTORY blocking samples into a single self-contained interactive HTML report. No application server, no database objects, no server-side files.

The report pipeline

Everything runs on the machine where the repo lives. sqlplus can connect to a local instance (/ as sysdba) or a remote one (EZConnect/TNS) — the database only ever answers queries; the HTML is assembled client-side, so the report always lands in the local reports/.

The stdout contract

The interface between sql/20_emit_html.sql and run_report.sh is plain text on stdout. Three rules make it robust across SQL*Plus quirks and AIX line-length limits — if you change one side, change the other:

• # sentinels per chunk line — SQL*Plus strips trailing whitespace from output lines; a chunk ending in a space would silently lose it (this corrupted wait-event names in testing).
• ≤500 characters per chunk — stays under POSIX LINE_MAX (2048 bytes) even at 4-byte UTF-8, so AIX awk never sees an over-long record.
• Chunks join with no newline — boundaries can fall mid-token; safe because Oracle's JSON generators escape control characters, so the payload never contains a literal newline. Relatedly, SET TAB OFF in sql/00_settings.sql is load-bearing: the default TAB ON rewrites runs of spaces into literal TABs — unescaped control characters that break JSON.parse.

Window : 2026-07-02T11:00:00 .. 2026-07-02T12:00:00      <- progress (echoed to user)
Blocking samples found: 206
__META_JSON_BEGIN__
#{"dbName":"CDB1","dbId":1183716798,"beginTime":"2026-07#   <- ≤500-char chunks,
#-02T11:00:00", ...                                     #      # sentinels
__META_JSON_END__
__DATA_JSON_BEGIN__
#[{"t":"2026-07-02T11:00:05","sid":34, ...              #
__DATA_JSON_END__

On the template side, the two placeholders sit alone on their own lines — that line-based layout is the splice contract (verified before splicing):

<script>
  window.ASH_META =
__META_JSON__          <- each line replaced by the concatenated chunks
  ;
  window.ASH_DATA =
__DATA_JSON__
  ;
</script>

Demo / seed flow (test databases only)

Everything under demo/ is deliberately invasive — it manufactures blocking so the report has something to show — and is gated behind a confirmation prompt (type seed; ASH_SEED_FORCE=1 skips it for scripted runs).

The report itself

assets/template.html is a single file: one IIFE, ECharts from CDN, data injected at build time (no fetches at runtime). It renders key-findings KPIs and auto-generated verdict sentences, a per-session Gantt timeline grouped by wait chain (filled cell = waiting, outlined = holding a lock), and a click-to-drill blocking tree at any 10-second ASH sample. reports/ash_blocking_demo.html is the same code with a rich synthetic dataset inlined, for offline UI work.

Repository layout