MCPDBWizard

Writing  · 

Detecting ad-hoc SQL in Oracle: proving what an AI agent actually sent

Somebody tells you their agent does not write SQL. Or they tell you it does, but only safe SQL. Both are claims about a running system, and Oracle has been keeping the receipts the whole time.

Ad-hoc SQL is a statement composed for the occasion rather than one that already existed and was given values — and Oracle can tell you which of the two it has been running, using views every DBA already has. This works whoever built the thing, including us.

Signal 1: does the set of distinct SQL_IDs stop growing?

Every distinct statement text gets its own SQL_ID. A server built from a fixed set of statements has a fixed set of texts, so its SQL_ID count climbs while each tool is used for the first time and then flattens. An agent composing dynamic SQL produces a new text — and a new SQL_ID — every time it phrases something differently, which is most times.

SELECT COUNT(DISTINCT sql_id) AS distinct_statements,
       SUM(executions)        AS total_executions
FROM   v$sql
WHERE  parsing_schema_name = 'MCP_AGENT';

Run it, wait a day of normal use, run it again. What you are reading is the shape of the change, not the number. Executions climbing while distinct statements stay flat is a bounded system. Both climbing together, roughly in step, is something composing a new statement per question.

Signal 2: how many SQL_IDs share one FORCE_MATCHING_SIGNATURE?

This is the sharper test, and it is the classic Oracle bind-variable diagnostic pointed at a new question. FORCE_MATCHING_SIGNATURE is the signature of a statement after literals are replaced, so two statements that differ only in their values share a signature while having different SQL_IDs.

SELECT   force_matching_signature,
         COUNT(DISTINCT sql_id) AS variants,
         MIN(sql_text)          AS an_example
FROM     v$sql
WHERE    parsing_schema_name = 'MCP_AGENT'
AND      force_matching_signature != 0
GROUP BY force_matching_signature
HAVING   COUNT(DISTINCT sql_id) > 1
ORDER BY variants DESC
FETCH FIRST 20 ROWS ONLY;

Empty result: every statement shape exists once, which is what binds look like. Rows with variants in the hundreds: the same query rewritten with different values pasted in, which is what a language model pasting values looks like. The an_example column shows you exactly which question it was, which is usually the moment somebody stops arguing.

Four things that will fool you

CURSOR_SHARING. If it is not EXACT, Oracle rewrites literals into binds before the statement is cached, and both signals collapse — signature variants disappear and the SQL_ID count flattens, whatever the application is doing. Check first:

SELECT value FROM v$parameter WHERE name = 'cursor_sharing';

V$SQL is a cache, not a log. Statements age out, a flush or a restart empties it, and “it isn’t there” never means “it never ran”. For a record that survives, DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT hold weeks — but AWR needs the Diagnostics Pack licensed, which is a real cost and not everyone has it. If you do not, sample V$SQL on a schedule and keep the results yourself.

MODULE is stamped at first parse. A statement already in the shared pool keeps whoever parsed it first, so a query your agent shares with an existing application may be attributed to that application. Session management has the detail, and it is why filtering on PARSING_SCHEMA_NAME is the more reliable starting point.

An agent that uses binds properly passes signal 2 and fails signal 1. The two tests do not measure the same thing: signature variants catch literal-pasting, and the growth curve catches new shapes. A careful SQL-composing client binds its values and still invents a new query per question. Run both.

What a curated server looks like from here

A bounded set of SQL_IDs that stops growing, one per shape, each with executions climbing — and, in our case, every one of them starting with the same marker so you can find them without knowing what to look for:

SELECT sql_id, executions, sql_text
FROM   v$sql
WHERE  sql_text LIKE '/* Created By MCPDBWizard */%';

That comment goes into every statement, whatever the surface — table lookups, sequences, PL/SQL calls, and the statements you wrote yourself.

What this does not tell you

Whether the SQL was right. A bounded set of statements that all return the wrong number is a bounded set of statements that all return the wrong number, and no view in the data dictionary has an opinion about business meaning. This measures what arrived, which is the part people argue about and the part that is checkable.

It also says nothing about who asked. Oracle sees one service account no matter which agent, team or person was behind the call — attribution lives in the audit trail, which is not the same thing as the server’s log, and the account the server connects as is a separate piece of work.

If what you find is a long list of signature variants, the question stops being how do I detect it and becomes how do I stop it.

How does MCP DB Wizard fit into all this?

MCP DB Wizard is a generator: you point it at an Oracle schema, tick the tables, PL/SQL routines, sequences and tested SQL statements of your own that one job needs, and it builds an MCP server exposing those and nothing else. It is the reason we know what these two signals look like on a server that is not composing anything — we had to check our own.

It says so twice, in two places a DBA already looks. The first is the statement text: every statement a generated server runs carries the comment /* Created By MCPDBWizard */, which is what the query above matches. It survives into the shared pool exactly as written, costs a few bytes per cursor, and cannot change a plan.

The second is MODULE. Both halves of the product call DBMS_APPLICATION_INFO.SET_MODULE when they connect, so one query separates them — and note this one filters on the module rather than the comment, because the generator’s own dictionary reads are not statements it emitted and do not carry the marker:

SELECT   module, action, COUNT(DISTINCT sql_id) AS statements, SUM(executions) AS execs
FROM     v$sql
WHERE    module IN ('MCPDBWizard', 'DaoFactory')   -- your factory name, if you renamed it
GROUP BY module, action;

The generator reports MCPDBWizard with an action of Introspect — those are its data dictionary reads at design time, and they should stop when generation does. A running server reports its DAO factory’s class name instead, DaoFactory unless you renamed it, which is deliberate: what a DBA wants in that column is which application, not which vendor built it. Give each config its own factory name and the column tells your servers apart; leave them all at the default and it cannot. The session management page has the full table.

It should score zero on signal 2 and flatten on signal 1. Every statement it emits is fixed text with real bind variables, one per tool, so the distinct SQL_ID count converges on the number of tools you selected and stops. A signature with variants coming from a generated server would mean a statement was being built from values rather than bound to them, which is a bug rather than a trade-off, and we would want to hear about it.

Which is the whole argument for publishing this. Anyone can claim their server does not compose SQL; the useful thing is a test you can run yourself, on any server, that does not depend on believing the person who wrote it.

How do I get MCP DB Wizard?

It’s available from our GitHub repo as a Docker image, and the quickstart will get you a running server against your own schema.