Baseline discipline: capturing artifacts before you need them

July 29, 2026

Every plan-regression writeup, including diffing a baseline plan against a current one, assumes you have a baseline. Most shops don’t, until it becomes a habit — the “before” state gets captured after something has already gone wrong, at which point it’s not a baseline, it’s a reconstruction. The fix is mechanical: export a fixed set of artifacts on every release, before you need them, and file them somewhere they’ll still be there when a regression shows up three weeks later.

Here’s what to capture and where it lands once you’re comparing against it in OraTune.

Execution plans for anything on the critical path. Generate these with SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'YOUR_SQL_ID', format => 'ALL ALLSTATS LAST')) against the SQL_IDs that matter, or an EXPLAIN PLAN output for queries not yet running in production. Save the pipe-delimited output as-is. OraTune’s XPLAN parser reads both the standard full-column format and the simplified format without a Name column, extracts predicate information and any Note section, and feeds the result into the Execution Plans tab for side-by-side comparison later.

The SQL and PL/SQL source itself. Every .sql, .pls, .pks, .pkb, .prc, .fnc, and .trg file behind the release. The Code Diff tab does line-level and structural comparison — it will catch a hint that got added or removed, or an index reference inside a hint that changed, even when the surrounding logic looks identical.

A SQLT dump for the objects behind your high-load statements. This is the one most often skipped, and it’s the one that answers “why” instead of “that it happened.” A SQLT (SQLTXPLAIN) dump captures optimizer parameters, table and index statistics, histograms, and execution stats at the moment it’s pulled. OraTune’s DMP findings thresholds turn a later diff against this baseline into concrete flags automatically: a table’s row count drifting past 20% or 50%, an index clustering factor degrading past 50%, a histogram disappearing from a column, or a changed optimizer parameter. None of that is visible from the plan or the SQL text alone.

AWR or TKPROF around the release window. An AWR text or HTML report, or a TKPROF listing from the raw .lst/.txt output of the tkprof utility (not a spooled session log). This gives you the instance- and statement-level “did anything get slower” signal — DB Time, elapsed time, wait events from AWR; elapsed time, CPU, logical and physical reads from TKPROF.

ADR traces, if you’re chasing a specific error or wait event. OraTune’s dump parser auto-detects an ADR/diagnostic trace by session ID, dump-file, and ORA- markers in the first 4KB. Rename the .trc file to .dmp before uploading — that’s the extension the parser expects.

For anything past a handful of statements, capture and compare through Batch Analysis rather than one pair at a time. Point it at a baseline folder and a current folder; files are matched by exact filename and classified by extension, so a whole release’s worth of SQL, plans, and dumps gets diffed in one run. This only works if naming is consistent across both folders — report_q1.sql on the baseline side has to be report_q1.sql on the current side too, or it lands in the unmatched list instead of getting analyzed.

A minimal per-release checklist, in rough order of scripting effort:

None of this prevents a regression. What it does is turn the next one from “the query is slow, no idea why” into a diff against a known-good state — a side-by-side plan, a findings list, and a specific stat or parameter that changed, instead of a guess.