Handing AWR to the Agent

In the last article I argued that Oracle's wait-interface discipline — rank where the time goes, attack the top class, ignore the rest — transfers directly to profiling LLM pipelines. Several readers noticed the title cuts both ways, and they were right. This piece is the other direction: instead of applying AWR thinking to the LLM, I handed AWR itself to the LLM and watched what a competent agent does with thirty years of my job.

Short version: given the right tool surface, an agent found a planted plan regression from a one-line complaint, worked out the likely cause, and prescribed the same SQL Plan Management pinning I would have. It also tried things no tool author would predict, broke my SQL twice in ways only a live database can, and left a perfect audit trail of itself in v$sql. Every one of those outcomes taught me something about what "AI for databases" actually requires.

Why not just give the agent SQL?

The obvious move is to hand the agent a connection and let it write SQL. Oracle now ships exactly that: SQLcl 26.1.2 includes an MCP server with five tools — list saved connections, connect, run SQL, run SQLcl commands. It works, and I use it below. But for diagnosis I wanted the opposite shape: a small, curated surface the agent cannot wander off.

So the diagnostic layer is a probe registry: 25 SELECT-only queries against V$ and DBA_HIST views, each with declared bind parameters, typed output schemas, row caps, and a license tier tag (license-safe vs Diagnostics Pack — because an agent that innocently queries DBA_HIST_ACTIVE_SESS_HISTORY on an unlicensed production system has just created a compliance problem no one will notice until the audit). On top of the probes sit eight MCP tools; the three that matter here are list_regressions (find SQL whose execution plan changed in an AWR window, scored by severity), diff_plan (structural diff of two plan trees), and explain_regression (a deterministic DBA-readable narrative). Every tool has a fixture mode, so the whole stack tests offline without a database.

The contract is the point. The agent does not compose SQL against the dictionary; it picks typed tools with vetted queries behind them. Where the open-SQL escape hatch is genuinely needed — acting on a finding — that is a different tool, a different connection, and a different privilege level. More on that below.

Fixture-green is not live-green

The registry passed its self-checks for two days. The first full live run found three real bugs in under an hour — all invisible to fixtures, because fixtures do not bind variables against a real parser:

• The probe runner injected a default max_time bind into every probe's parameters, but only two probes' SQL actually declared :max_time. Every other probe failed live with a phantom-bind error. The fix took one line; the lesson took longer: the self-check now verifies, offline, that declared parameters and SQL binds cover each other in both directions, for all 25 probes.
• A datetime construct raised ORA-30084 in two places in the same file; the first fix named only the one in the error message and failed the rerun. When you find an anti-pattern, fix every instance of it.
• And my favorite, because it is pure DBA knowledge: V$SYSMETRIC_HISTORY returned zero rows. Not broken — empty by design, because inside a pluggable database that view populates only in CDB$ROOT. The container-scoped V$CON_SYSMETRIC_HISTORY (12.2+, and note the metric group id changes from 2 to 18) had 9,089 rows sitting right there. No amount of agent intelligence substitutes for knowing which V$ view tells the truth in which container. The fix ships with a fallback for pre-12.2 and non-CDB targets, because mixed estates are reality.

The experiment: plant a regression, see if anyone notices

The test was honest: create a 200k-row table with skewed data, run a marked query against the rare value (index range scan), take an AWR snapshot, drop the index, run the identical statement again (full scan, 75× the elapsed time per execution), snapshot again. Classic plan flip, the kind that pages a DBA at 2am.

list_regressions found it: both plan hash values, the metric deltas, a MEDIUM severity. Then it found something better — a second regression nobody planted. A recursive dictionary query had flipped its own plan during my statistics gathering, a genuine HASH JOIN → NESTED LOOPS flip, scored HIGH. The detector caught an organic regression in the wild on its first night out.

Two transcripts

The first demo was scripted — call these three tools in order against this sql_id — and its most interesting moment was unscripted: the metrics showed the "regression" ran 45–71× faster after its plan change. The agent (a small, cheap model) said so plainly: "a performance improvement disguised as a plan regression — no action required." The tool flags plan changes; the judgment about direction came from the model. That division of labor — deterministic detection, model judgment on top — is exactly where I want the boundary.

The second demo was the real test. Open-ended prompt, no sql_id, no tool sequence: application owners report a query got dramatically slower in the last few hours — investigate. The agent listed the probe catalog, swept the window, and found the planted sql_id on its own. Then came the moment that sold me: it reached for the plan diff and got nothing back — AWR had not captured plan rows for either plan hash. A lesser tool-user stops there. This agent fell back to the per-execution metrics in the regression record — buffer gets up from 9.2 to 5,892 per execution, I/O wait from zero to 69 ms — and reasoned the way a DBA reads SQLSTAT: that signature is an index lookup degenerating into a full scan. Likely cause, a dropped or unusable index, or a statistics regather; immediate mitigation, pin the good plan with a SQL Plan Baseline — with syntactically correct SPM steps and the follow-up checks a senior DBA would run. The actual cause was the dropped index.

Just as instructive is what the agent tried that my tools refused. It passed start_time='-4h', end_time='now' — natural to any human, an error in my strict ISO interface. It complained, politely, that the probe set does not expose SQL text, then handed the operator follow-up queries to fetch it. Both gaps became features the same night: relative time windows, an awr_sql_text probe with text previews in regression records, and a list_snapshots tool so agents anchor windows on real snapshot boundaries instead of guessing timestamps. Agents are users. Their first instincts are free API design reviews.

Diagnosis reads, action writes

The probes are SELECT-only by construction. The agent's recommendation — create an SPM baseline — is a write, and it should be: the same session can carry both, through different tools with different blast radii. SQLcl's MCP server is the action arm, and the privileges question is where thirty years of DBA paranoia earn their keep. Three independent controls, all verified live:

Who can be reached. SQLcl MCP exposes every connection saved in the launching user's connection store — my first trial run cheerfully listed all six of mine, including the DBA ones. There is no allow-list flag. There is, however, a clean isolation trick: SQLcl resolves its store from the JVM's user.home, so launching the MCP server with JAVA_TOOL_OPTIONS=-Duser.home=$HOME/.sqlcl-agent points it at a separate store containing exactly the connections you chose to expose. After the switch, the agent sees one connection. My working DBA connections do not exist in its universe.

What can be done. Read-only is not a connection property — it is a user property, and there are two enforcement tiers. On any version: a dedicated user with SELECT-only grants. On 23ai and later, the hard ceiling: ALTER USER probe_smoke READ ONLY. When the agent attempted CREATE TABLE through MCP, the database answered ORA-28194 — "Can perform read operations only" — before any privilege evaluation. No grant mistake, no clever prompt, no MCP client behavior writes through that flag. (Local PDB users only, and not available on 19c estates, where grants discipline remains the whole story.)

What happened. The part that delighted me most: SQLcl's MCP server tags every statement it executes with a comment — /* LLM in use is claude-haiku-4-5 */ — and that comment travels into the shared pool. Which means agent activity is attributable inside the database with the same tooling I have used since Oracle 8i:

SELECT parsing_schema_name, sql_text
FROM   v$sql
WHERE  sql_text LIKE '%LLM in use%';

The agent's own workload shows up in AWR, wait classes and all. The title of this pair of articles closes its own loop: profile the LLM with AWR discipline, hand AWR to the LLM, and the LLM's fingerprints become AWR content you profile the same way.

What it proves

One more Oracle habit transferred along the way: right-sizing. The hunt ran on a mid-tier model, the trivial verifications on the cheapest one — the SQLcl log recorded each model name per statement. Same instinct as not giving every session its own gigabyte of PGA.

The overview article ended by asking whether the missing layer — ranked, classified, time-share diagnosis for LLM systems — resonates. This experiment answers a narrower, harder question for my own field: can an agent do real Oracle performance diagnosis today? Yes — if you build the surface a DBA would build: curated read-only probes for evidence, a privilege-scoped action arm, and audit trails the database itself enforces. The intelligence was never the bottleneck. The contract was.