agentleFS
zademyzademy/lu-query/skills/lu-query-benchmark-sql/SKILL.md

lu-query-benchmark-sql

Skill2 starsChanged 29 days agoMITView on GitHub

Measure a SQL statement before and after a rewrite - real execution stats from DBMS_XPLAN, repeated runs, a result fingerprint that proves the rows did not change, and one HTML report with the bottleneck list and the per-run curves. Use when the user asks why a query is slow, wants to compare two versions, or wants a tuning recommendation with numbers behind it.

The file

We split this skill into the rules it gives an agent

12 rules. About 0 are common, 8 are distinctive, and 4 are very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈8

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 4

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Distinctive:

    Ask. Never guess a path, never search the working directory for "something that looks like a query", never start from a query you found in …

    line 33
  2. Distinctive:

    If the user pastes SQL directly instead of giving a path, use it.

    line 38
  3. Distinctive:

    Never modify the original file.

    line 49
  4. Distinctive:

    If it holds more than one statement, say which ones you found and ask which to measure.

    line 54
  5. Very specific:

    If it has Oracle binds (:customer_id), ask for one concrete value for each.

    line 56
  6. Distinctive:

    Do this for the original before you move on.

    line 118
  7. Very specific:

    Read the plan against references/bottlenecks.md and work the checklist in the order that file gives, which starts with statistics and ends …

    line 123
  8. Distinctive:

    If a fix would need a new index or a schema change, do not write it.

    line 129
  9. Distinctive:

    If they do not, the rewrite is wrong, whatever the speed.

    line 137
  10. Very specific:

    Write 04-mediciones.json following references/mediciones.md, then:

    line 142
  11. Distinctive:

    If it exits non-zero, read the message.

    line 151
  12. Very specific:

    ALLSTATS LAST reports the last statement in the session: If you measure something else between the two calls, you get the wrong plan, …

    line 181

≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

lu-query-benchmark-sql

Turns "this query is slow" into a number you can defend: run it, run the rewrite, prove the rows came out the same, and show both on one page.

The point is not the plan. The plan is an estimate. The point is that the rewrite is faster and returns the same thing, measured N times on this database, with the limits stated on the face of the report.

When to use

  • The user asks why a query is slow and wants evidence, not a guess.
  • There are two versions of a query and nobody knows which is faster.
  • A tuning recommendation needs the numbers behind it to be accepted.

When not to use

  • Just want the plan of a statement you will not run. That is explain_plan.
  • Something is locking or hanging right now. That is lu-query-triage-incident.
  • The query is wrong and returns wrong rows. That is a correctness bug, not a tuning job. Fix it first: a benchmark of a wrong query is a benchmark of a bug.

Workflow

1. Ask where the file is. Always.

Ask. Never guess a path, never search the working directory for "something that looks like a query", never start from a query you found in the conversation.

Give me the path to the .sql file with the query to analyze.

If the user pastes SQL directly instead of giving a path, use it. The rule is that you wait for the answer.

2. Establish what "after" means

Two possibilities, and you need to know which before you start:

  • The user has a second file. Ask for its path.
  • There is no second version. Then you propose the rewrite, and you do not write it to disk until the user approves it. Show it in the conversation first.

Never modify the original file. It is the baseline; if it changes, the comparison means nothing.

3. Read the file and settle the details

  • If it holds more than one statement, say which ones you found and ask which to measure. Measure all of them if the user wants, one report each.
  • If it has Oracle binds (:customer_id), ask for one concrete value for each. A benchmark over a bind you never chose tells you nothing about the plan the application will get. Take one representative value and say in the report that it is one.
  • Save both versions verbatim into the output folder before running anything, so the run is reproducible even if the session moves on.

4. Create the output folder

perf-<slug>-<AAAAMMDD-HHMMSS>/
  01-original.sql
  02-optimizado.sql
  03-reporte.html
  04-mediciones.json

<slug> is a short name you make from the query. Create it in the agent's working directory and tell the user where it is.

5. Measure the original, ten times

Each repetition is two tool calls, in this order, with nothing in between:

SELECT /*+ gather_plan_statistics */ <the statement verbatim>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

The hint is not optional. With statistics_level = TYPICAL, which is the default, Oracle does not gather the basic row counts and ALLSTATS LAST comes back with no A-Rows at all.

DISPLAY_CURSOR with NULL, NULL reports the last statement in the session, so these two calls cannot be separated by anything else.

From each repetition record only three numbers, from the root row of the plan: A-Time divided by 1000 for milliseconds, Buffers, Reads. The root row is the last one, the one with no letter in its Id. Do not sum the other rows: they count the same work again.

Keep the full plan text from repetition 1 only. That is the cold-cache plan and it is the one worth reading. The other nine give you three numbers each; pasting ten plans into the conversation is what makes this kind of task fail.

The first 2 repetitions are warmup and are marked as such. Everything is compared on the median of the remaining 8, never the mean: a cold first run drags the mean and hides the result you are actually trying to show.

Repeat 10 times. If the statement takes more than a few seconds, say so before starting, because 10 runs of a 30 second query is 5 minutes.

6. Fingerprint the result

run_query caps at 1000 rows, so comparing rows row by row proves nothing about the 70,000 the statement returned. Ask the database for a summary instead. See references/mediciones.md for the exact statement and the traps in it (no ORDER BY, DATE through TO_CHAR, NULL given a fixed marker).

Do this for the original before you move on. If you only find out at the end that the rewrite changed the rows, the whole run is wasted.

7. Propose the rewrite and get approval

Read the plan against references/bottlenecks.md and work the checklist in the order that file gives, which starts with statistics and ends with indexes.

Show the rewrite in the conversation with the reason for each change. Wait for the user to approve it. Only then write 02-optimizado.sql and measure it.

If a fix would need a new index or a schema change, do not write it. Say it as a recommendation and mark it in the report as something that was not applied.

8. Measure the optimized version the same way

Same 10 repetitions, same 2 warmups, same three numbers, same fingerprint.

Then compare the fingerprints. They must match: same row count and same checksum. If they do not, the rewrite is wrong, whatever the speed. Say so plainly and stop there. A faster query that returns a different answer is not an optimization.

9. Write the measurements and render

Write 04-mediciones.json following references/mediciones.md, then:

node scripts/bench-report.mjs <carpeta>

It prints the absolute path of 03-reporte.html. It computes the medians, the factor and the fingerprint verdict; you do not do arithmetic.

If it exits non-zero, read the message. Do not retry with different values.

10. Fill in the findings

The renderer does not invent bottlenecks: it draws what you write in findings. Go through references/bottlenecks.md and record only what the plan actually shows, each with the node that proves it. A finding with no evidence line is a guess; leave it out.

Strip the two rows run_query adds to every plan (VIEW and COUNT STOPKEY) before you read anything into them.

11. Hand over the path

Give the path to the folder and one sentence on what the numbers came out. Do not paste the HTML into the conversation. Do not summarize numbers you did not read.

Reading the results correctly

  • The statement you measured is not the statement in production. It carries the gather_plan_statistics hint and travels inside the wrapper run_query adds. Its sql_id and plan_hash_value are not the real ones. The report says so at the top.
  • One session, no concurrency. These numbers are for a single session on an idle database. A plan that wins here can lose with real load on top.
  • Warm cache. From repetition 3 on, the buffer cache holds the data, so physical reads in the report read lower than what production would see. That is exactly why the first 2 repetitions are marked and excluded.
  • A matching fingerprint is not a correct result. ORA_HASH is 32 bits and collides. The report says coincide or no coincide and never correct.
  • ALLSTATS LAST reports the last statement in the session. If you measure something else between the two calls, you get the wrong plan, silently.
  • 10 repetitions is not a benchmark of a fleet. It is enough to separate a 10x change from noise. It is not enough to claim a 4% change.

Safety

  • The original .sql is never written to. The optimized one is written only after the user approves it.
  • Nothing here creates an index, changes a schema or runs DDL. Those come out as recommendations in the report, not as actions.
  • The output folder is the only thing created, and the report goes into it.
  • The report embeds the SQL it measured, binds included. It is an internal document: do not publish it or commit it without reading it.

Index recommendations (required before rendering)

For every measured query, review the actual plan predicates and table accesses, existing index columns in order, expressions, uniqueness, status and visibility, table/column statistics and selectivity. Use the existing metadata tools and run_query against ALL_INDEXES, ALL_IND_COLUMNS, ALL_IND_EXPRESSIONS and ALL_CONSTRAINTS when needed. Use suggest_index as a candidate source, not proof. Read references/indexes.md for interpretation and the reporting contract in references/mediciones.md.

Record indexRecommendations in 04-mediciones.json. Explain each finding in the user's language with short sentences and a book-index analogy, followed by the exact evidence in the expandable technical detail. Always state what happens, what to try, its cost, and how to measure success. If metadata or usage history is unavailable, name what is missing. Never label an index bad because a single plan did not use it. Never create, rebuild or drop an index as part of report rendering. Execute a proposed DDL only after explicit approval and measure its effects separately from a SQL rewrite.

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.