Turns an AWR or ASH report into evidence-backed, code-correlated findings without putting the raw report into a model's context.
The design rule that governs every module: numbers are parsed, never
recalled. Anything the parser could not extract is reported as
Not present in the supplied AWR report. rather than inferred.
Python 3.9+. Standard library only — no installs, so it runs on a locked
down DBA workstation. pytest is needed only to run the tests.
cd tools
# Analyse one AWR report, correlating top SQL to repository code
python -m oracle_diag analyze awrrpt_1_48120_48121.html --repo .. --format md
# Refuse to analyse a report that does not cover the complaint window
python -m oracle_diag analyze awr.html --problem-window '2026-08-10 09:00..2026-08-10 10:00'
# Baseline vs current: is this a regression, or did the workload grow?
python -m oracle_diag compare before.html after.html --repo ..
# Direction across a series of consecutive windows
python -m oracle_diag trend snap1.html snap2.html snap3.html
# ASH: spikes, blocking chains, plan-line attribution
python -m oracle_diag ash ashrpt_1_48120_48121.html
# ADDM: ingest its findings and state agreement or disagreement explicitly
python -m oracle_diag addm addmrpt.txt --awr awrrpt.html --repo ..
# Execution plan: cardinality misestimates and work concentration
python -m oracle_diag plan xplan_allstats.txt
# One execution in full / PL/SQL time by subprogram
python -m oracle_diag sqlmon sqlmon_report.txt
python -m oracle_diag hprof plshprof_output.txt
# Validate the parser against a report from YOUR Oracle version
python -m oracle_diag conformance awrrpt.html --write-expect expect.json
python -m oracle_diag conformance awrrpt.html --expect expect.jsonpython -m oracle_diag scan --repo .. --fail-on high # unsafe dynamic SQL
python -m oracle_diag graph --repo .. --object BILLING_CHARGES # blast radius
python -m oracle_diag lint --repo .. --target 19c --strict # version compatibility
python -m oracle_diag index --repo .. # SQL_ID correlatability
python -m oracle_diag sqlid "select * from dual" # -> a5ks9fhw2v9s1Exit codes: 0 clean, 1 findings that should block, 2 window
unrepresentative (refused), 3 bad input. scan and lint are wired as CI
gates against this repository's own SQL — the solution passes the checks it
enforces on user code.
parse → validity gate → redact → rank by DB Time → correlate SQL_ID to code
→ findings → compact report (md or json)
| Stage | Module | What it guarantees |
|---|---|---|
| Parse | awr_parser.py, html_tables.py, numbers.py |
Section-targeted extraction from AWR HTML and text; version drift handled by keyword matching, never fixed indexes. Missing sections are listed, not invented. |
| Validity gate | validity.py |
Refuses an instance restart, a 24h window, an idle window, a non-overlapping period, or a single-instance report for a RAC problem |
| Redact | redact.py |
Host/DB/instance names, IPs, emails and SQL literals tokenised before anything leaves the machine; every substitution logged |
| Rank | dbtime.py, waits.py |
DB Time is the currency; idle events excluded; CPU-bound vs wait-bound classified; per-execution normalisation |
| Ratio guard | ratios.py |
Raises RatioTuningViolation if a finding rests on Buffer Hit % and friends |
| Correlate | correlate.py |
Recomputes Oracle's own SQL_ID from repository text — an exact match is cryptographic proof, not resemblance |
| Findings | findings.py |
Every finding carries section, metric, % DB Time, root cause, code location, risk, confidence, validation |
| Report | report.py |
The Section 17.16 output contract, including the mandatory "Not Determinable" block |
| Compare | compare.py |
Per-execution normalisation before any regression claim; PLAN_HASH_VALUE change detection |
| ASH | ash_parser.py |
Spike isolation, blocking chains, plan-line attribution, with the sampling caveat attached |
| ADDM | addm_parser.py |
Findings with impact %; agreement/disagreement stated against our own ranking; Tuning Pack flagged |
| Plan | xplan_parser.py |
Per-start cardinality misestimates; self-cost (children excluded); refuses EXPLAIN PLAN output |
| Profilers | profilers.py |
SQL Monitor (one execution in full) and DBMS_HPROF (self vs subtree time) |
| Conformance | conformance.py |
Extraction manifest with raw-cell provenance, so a DBA can validate the parser in minutes |
| Security | security_scan.py |
Classifies dynamic SQL SAFE/REVIEW/UNSAFE per variable, not by proximity |
| Graph | depgraph.py |
Object dependency graph and blast radius; comments and literals stripped so no phantom edges |
| Version lint | version_lint.py |
Features exceeding the target release; deprecated constructs |
| Method | Confidence | Basis |
|---|---|---|
sql_id-exact |
HIGH | Repository text hashes to the SQL_ID AWR reported |
normalized-text |
MEDIUM | Equal after folding case, whitespace, literals and binds |
shingle-similarity |
LOW | Token-shingle overlap — a candidate for human confirmation |
Unmatched SQL is always reported. Oracle recursive/internal SQL (OBJ$,
WRH$_, /* SQL Analyze */) is excluded from application recommendations.
cd tools && python -m pytest tests -q96 tests covering: numeric extraction per AWR section (HTML and text), the
SQL_ID algorithm against Oracle's published values, negative fixtures
(instance restart, idle 24h window, truncated report, non-AWR file, wrong
period), the ratio-based-tuning regression test, redaction leak tests, and the
output contract. CI runs them on Python 3.9 and 3.12 via
.github/workflows/diagnostics-toolchain.yml.
AWR, ASH, ADDM, DBA_HIST_*, SQL Monitor and SQL Tuning Advisor require the
Oracle Diagnostics Pack (Tuning Pack for the advisors) on Enterprise
Edition. The toolchain states this in every report it produces. It does not
connect to a database — it reads report files you already generated.