Ready-to-paste recipes

§1Argument order (positional)

./run_awr_trend.sh <connect> [target_end] [win_hours] [weeks_back] \
                              [top_n] [inst_num] [step] [step_unit] \
                              [template] [debug] [marker_file]

Trailing args you don't care about can be omitted. To pin step / step_unit / template you must also supply every preceding arg — use AUTO / 0 as placeholders (see the examples). The total compared span is weeks_back × step + win_hours of step_unit.

Don't want to deal with the positional order? Use the configurator

./run_awr_trend.sh --configure      # or -c / -i / --interactive
./run_awr_trend.sh                  # no args at a terminal → offers it too

The configurator is an interactive question-and-answer mode. It asks for each option in turn — connection, window end/width, a friendly cadence menu (weekly / daily / hourly / custom), prior-window count, Top-N, RAC instance, template, debug, marker file — each with a short explanation, a [default] you accept with Enter, and validation that re-asks on bad input. When you're done it prints a summary plus two copy-paste artifacts: a minimal ./run_awr_trend.sh … command (only the args that differ from defaults) and the equivalent pure-SQL*Plus DEFINE … @@awr_trend.sql block for hosts without the shell wrapper. Then it offers [r]un / re-[e]dit / [q]uit: run generates the report, edit loops back with your current answers as the new defaults, quit just leaves the two blocks on screen. Prefer point-and-click? The web configurator does the same in your browser.

§2Weekly cadence (the original behaviour)

Default — last 4 same-hour-of-week windows

./run_awr_trend.sh user/pw@svc

Compares now−1h → now against the same hour 7, 14, 21 and 28 days ago.

Specific Monday morning vs the four prior Mondays

./run_awr_trend.sh user/pw@svc '2026-04-13 09:00'

Monday 09:00–13:00 across the last 4 Mondays

./run_awr_trend.sh user/pw@svc '2026-05-25 13:00' 4 3 10 0 1 w

Four 4-hour windows ending Mon 13:00 each, stepping back weekly. Use weeks_back=4 instead of 3 for "current Monday vs 4 prior Mondays" (5 windows total). If you're running before 13:00 on a Monday, push target_end back one week so the current window is fully populated. AWR retention must cover ~29 days — see the retention check.

Two-hour window over twelve weeks of history

./run_awr_trend.sh user/pw@svc AUTO 2 12

Every other week (fortnightly), 6 windows back

./run_awr_trend.sh user/pw@svc AUTO 1 6 10 0 2 w

Spans 12 weeks; useful when alternating weeks have different workloads (payroll Tue, batch Wed, …).

Approximate monthly comparison — every 4th week, 6 windows

./run_awr_trend.sh user/pw@svc AUTO 1 6 10 0 4 w

Spans ~24 weeks. AWR retention must cover that range.

§3Daily cadence

Each of the last 7 days at this hour

./run_awr_trend.sh user/pw@svc AUTO 1 7 10 0 1 d

Detects “today is unusual vs the rest of the week”.

Same hour, every other day for 14 days

./run_awr_trend.sh user/pw@svc AUTO 1 7 10 0 2 d

Daily 09:00 → 10:00 windows for the past 5 business days

./run_awr_trend.sh user/pw@svc '2026-05-08 10:00' 1 4 10 0 1 d

Run on a Friday morning to compare Fri/Thu/Wed/Tue/Mon at 09:00.

Daily 4-hour batch window for a week

./run_awr_trend.sh user/pw@svc '2026-05-08 06:00' 4 6 10 0 1 d

Six prior days at 02:00–06:00 vs today.

§4Hourly cadence (straight line back)

Last 4 hours, hour by hour

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 0 1 h

Five 1-hour windows: the current hour + 4 hours back.

Last 12 hours, hour by hour

./run_awr_trend.sh user/pw@svc AUTO 1 11 10 0 1 h

Twelve 1-hour windows = a 12-hour span. Good for “what happened this morning?” walk-throughs.

2-hour windows over the past day

./run_awr_trend.sh user/pw@svc AUTO 2 11 10 0 2 h

Every 4 hours for the past 24 hours

./run_awr_trend.sh user/pw@svc AUTO 1 5 10 0 4 h

Six 1-hour windows separated by 4 hours — sample points across the day.

Pin the comparison to a specific hour

./run_awr_trend.sh user/pw@svc '2026-05-08 14:00' 1 6 10 0 1 h

The 14:00 hour vs 13:00, 12:00, 11:00, 10:00, 09:00, 08:00 of the same day.

§5RAC / multi-instance

Aggregate across all RAC instances (default)

./run_awr_trend.sh user/pw@svc

inst_num=0 sums per-instance values for cumulative stats and aggregates SYSMETRIC correctly (sums rates, averages ratios).

Just instance 1

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 1

Instance 2, hourly cadence, last 6 hours

./run_awr_trend.sh user/pw@svc AUTO 1 6 10 2 1 h

§6Templates (which metrics & waits to render)

Templates pick the curated set of stats, metrics and wait events the report renders. They don't change cadence or compared windows — they just trim or expand the rows.

comprehensive (default) — every curated metric and the full firehose of waits

./run_awr_trend.sh user/pw@svc

27 load stats, 23 SYSMETRIC metrics, all wait events ranked by time.

simple — triage-friendly subset for a quick glance

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 0 1 w simple

9 load stats, 8 SYSMETRIC metrics, ~10 commonly-watched wait events. The masthead renders a template: simple pill so you can tell reports apart at a glance.

dev — application-developer view

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 0 1 w dev

17 load stats, 13 metrics, 14 wait events: transaction throughput, query work, cursor/parse behaviour, sorts, SQL*Net chattiness, response time and app-caused contention — no host/OS or storage-engine internals.

Simple template + hourly cadence — fast walkthrough of the last few hours

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 0 1 h simple

Roll your own

Drop a directory under sql/lib/templates/<name>/ with three files — sysstat_load_targets.sql, sysmetric_targets.sql, wait_event_targets.sql — and add the name to the whitelist CASE in awr_trend.sql. A single '*' row in wait_event_targets.sql means “no filter, top-N firehose”; any other rows form an allowlist.

§7Progress markers (debug=Y)

On a busy DB some sections take minutes; with no terminal output it looks hung. debug=Y prints one timestamped line per section to stdout — the HTML report is byte-identical with debug on or off.

Defaults + debug

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 0 1 w comprehensive Y

Simple template + debug (the lightest combo)

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 0 1 w simple Y

You'll see output like:

[awr_trend 08:38:42.369] section 00 params (header + nav)
[awr_trend 08:38:42.594] section 01 windows (aligned begin/end snap pairs)
[awr_trend 08:38:42.600] section 02 load_profile (SYSSTAT deltas)
[awr_trend 08:38:44.188] section 03 sysmetric (SYSMETRIC_SUMMARY averages)
…
[awr_trend 08:38:45.142] all sections rendered; writing HTML epilogue

Diff two consecutive timestamps to find your slowest section. Any case-insensitive truthy value enables debug (Y, YES, 1, ON, TRUE, T).

Tee into a log so you keep the timings

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 0 1 w comprehensive Y \
  | tee reports/last_run_markers.log

§8Timeline markers (marker_file / MARKERS)

Annotate the dated charts (masthead strip, ASH timeline, DB-time summary, per-SQL ASH cards) with your own milestones — a patch, an index rebuild, a stats gather, an incident — so a spike lines up with a known change. Optional: no marker_file means no markers and no change to the report.

Write a config file, one milestone per line (YYYY-MM-DD HH24:MI + label). Copy markers.example.sql and edit:

-- my_markers.sql
@@sql/lib/marker '2026-04-20 14:00' 'Applied patch 19.22'
@@sql/lib/marker '2026-05-01 02:00' 'Index rebuild on SALES'
@@sql/lib/marker '2026-05-10 09:30' 'Optimizer stats gather'

Defaults + markers

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 0 1 w comprehensive N my_markers.sql

Hourly walkthrough with markers

./run_awr_trend.sh user/pw@svc AUTO 1 12 10 0 1 h comprehensive N my_markers.sql

File-free — inline markers, no file

Pass the same milestones inline as WHEN|LABEL pairs joined by ;; in the MARKERS env var (the configurator builds this for you):

MARKERS='2026-04-20 14:00|Applied patch 19.22;;2026-05-01 02:00|Index rebuild' \
    ./run_awr_trend.sh user/pw@svc

• Keep the path exactly @@sql/lib/marker even if your config lives elsewhere — SQL*Plus resolves nested @@ paths from the project root.
• A marker outside a chart's span is dropped for that chart; markers snap to the nearest point on the axis.
• A malformed datetime is skipped (it becomes an HTML comment), not fatal. Labels with a single quote must double it ('Bob''s change').
• Inline (MARKERS) labels must avoid a straight single quote, |, ;; and ~ — use a marker_file for labels that need those. marker_file wins when both are set.

See it live: example report with timeline markers.

§9Larger Top-N

Top-25 SQL with the default weekly comparison

./run_awr_trend.sh user/pw@svc AUTO 1 4 25

Useful when the top-10 doesn't capture a noisy outlier.

Top-50 SQL, hourly straight-back

./run_awr_trend.sh user/pw@svc AUTO 1 6 50 0 1 h

§10Invocation without the wrapper

If you can't run a shell script (Windows shop, restricted host), drive sqlplus directly:

SQL> @sql/defaults.sql
SQL> DEFINE step       = 1
SQL> DEFINE step_unit  = 'h'
SQL> DEFINE weeks_back = 6
SQL> DEFINE template   = 'simple'
SQL> @awr_trend.sql

Or in one shot, embedded in a sqlplus heredoc:

sqlplus -S -L user/pw@svc <<'SQL'
DEFINE target_end = '2026-05-08 14:00'
DEFINE win_hours  = 1
DEFINE weeks_back = 6
DEFINE top_n      = 10
DEFINE inst_num   = 0
DEFINE step       = 1
DEFINE step_unit  = 'h'
DEFINE template   = 'comprehensive'
DEFINE debug      = 'N'
DEFINE marker_file = ''
DEFINE markers = ''
DEFINE echarts = ''
@@awr_trend.sql
EXIT
SQL

§11Self-contained / offline report (echarts)

The report is almost entirely self-contained already — inline CSS, inline SVG sparklines, inline marker JS. Its one network dependency is the Apache ECharts library that draws the larger charts (hero strip, wait-class bars, findings heatmap, top-SQL bump chart, ASH timeline). When it can't load, the tables still render every number and an amber “Charts hidden” banner explains why. The echarts var (env var ECHARTS in the wrapper) controls where it loads from:

Single self-contained file (wrapper does the inlining)

Supply your own echarts.min.js — none ships in the repo; grab it from the CDN URL once, or from an npm tarball — and point ECHARTS at it:

ECHARTS=vendor/echarts.min.js ./run_awr_trend.sh user/pw@svc

The wrapper splices the library into the finished HTML, so the one .html opens and renders every chart with no network at all (it adds roughly the size of echarts.min.js, ~1 MB, to the file).

Internal mirror (no inlining; works on the pure-SQL*Plus path too)

ECHARTS='https://artifacts.corp/echarts@5/echarts.min.js' \
    ./run_awr_trend.sh user/pw@svc

Straight from SQL*Plus

The inlining step lives only in the wrapper, so a bare sqlplus run can't produce the single self-contained file on its own. Set the echarts var to pick the source:

sqlplus -S -L user/pw@svc <<'SQL'
@sql/defaults.sql
DEFINE echarts = 'vendor/echarts.min.js'   -- or an https:// mirror URL
@awr_trend.sql
SQL

A mirror URL yields a single file that needs the mirror; a local path emits <script src="vendor/echarts.min.js">, which renders offline only if you keep that .js beside the report. To collapse it into one self-contained file, run the same splice the wrapper does — from the repo root, after generation:

html="$(ls -t reports/*.html | head -1)"
lib='vendor/echarts.min.js'   # same path you put in DEFINE echarts
line="$(grep -nF "src=\"$lib\"" "$html" | head -1 | cut -d: -f1)"
{ head -n $((line-1)) "$html"; echo '<script>'; cat "$lib"; echo '</script>'; tail -n +$((line+1)) "$html"; } \
  > "$html.tmp" && mv "$html.tmp" "$html"

§12Scheduling & comparison playbooks

“What's different this morning vs my normal Monday?”

# Compare the current hour vs four prior Mondays at this hour.
./run_awr_trend.sh user/pw@svc

“Walk me through the last 12 hours hour by hour”

./run_awr_trend.sh user/pw@svc AUTO 1 12 10 0 1 h

Open the report and focus on the ASH timeline and DB time stacked areas — those cover the full span at native snap resolution.

“Did yesterday's batch differ from the previous five Tuesdays?”

# Set target_end to the end of the batch window.
./run_awr_trend.sh user/pw@svc '2026-05-12 06:00' 4 5 25 0 1 w

4-hour windows ending at 06:00, current Tue plus the 5 prior Tuesdays.

“Quarter-end vs the last three quarter-ends”

./run_awr_trend.sh user/pw@svc '2026-03-31 23:00' 1 3 25 0 13 w

Steps back 13 weeks at a time (≈ a quarter). AWR retention must cover ~9 months of history.

“Is this hour unusually slow compared to its immediate neighbors?”

./run_awr_trend.sh user/pw@svc AUTO 1 4 10 0 1 h

§13AWR retention check

Before you run any of the longer-lookback recipes, verify your AWR retention covers the span you ask for:

SELECT retention FROM dba_hist_wr_control;

Default retention is 8 days on 19c. The report still runs — windows with no history are flagged as skipped — but the full default weekly comparison wants ~29 days, and monthly-cadence recipes 30+. Bump it with:

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 60*24*60); -- 60 days

Defaults reset

The canonical defaults live in sql/defaults.sql. Editing them changes the site-wide behaviour for every wrapper invocation that omits the matching positional argument:

DEFINE target_end = 'AUTO'
DEFINE win_hours  = 1
DEFINE weeks_back = 4
DEFINE top_n      = 10
DEFINE inst_num   = 0
DEFINE step       = 1
DEFINE step_unit  = 'w'
DEFINE template   = 'comprehensive'
DEFINE debug      = 'N'
DEFINE marker_file = ''
DEFINE markers = ''

§14Fleet report (many databases)

A separate wrapper, run_awr_fleet.sh, runs the same comparison across a whole fleet.conf (one alias|connect per line) and stitches every database into a single worst-first ops-console page. No inst_num argument — a fleet pass is always inst_num=0. See the README fleet section for the full reference.

Default sweep — every DB, prior full hour vs 4 back

./run_awr_fleet.sh fleet.conf

Explicit window, hourly cadence

./run_awr_fleet.sh fleet.conf '2026-05-08 14:00' 1 4 10 1 h

Mark a fleet-wide event on every DB's 24h ASH chart

MARKERS='2026-05-08 09:30|Applied patch 19.22' ./run_awr_fleet.sh fleet.conf

Positional order is fleet.conf [target_end] [win_hours] [weeks_back] [top_n] [step] [step_unit]. MARKER_FILE=<file> of WHEN|LABEL lines wins over MARKERS.