pythia-tune

Tuning a Query or a Unit

Announce at start: "Using pythia-tune — measuring before changing anything."

Phase: Learn → Ask → Do — the plan and the statistics first, one proposed change, the same measurement after

Performance folklore is the most confident kind of wrong. "Add an index", "use a hint", "rewrite it as a join" — each is right for some plans and harmful for others, and only the plan says which. Tuning without a before and after is guessing with extra steps.

The Iron Law

NO TUNING CHANGE WITHOUT A MEASUREMENT BEFORE AND THE SAME ONE AFTER

The Workflow

1. Get the real statement. The exact SQL the application runs — from pythia src / pythia grep, with its bind variables as binds (:id), not pasted literals: a literal can get a different plan than the bind.
2. Measure.
   • pythia explain "<the select>" — the optimizer's plan; read rows (cardinality), cost, the access path per table, and the -- note: lines pythia adds (full scans, Cartesian joins, implicit conversions, dynamic sampling).
   • pythia stats <TABLE> for each table in the plan — row counts, when statistics were gathered, STALE, the indexes and their columns, unindexed foreign keys.
   • For DML, explain the SELECT that finds its rows.
3. Diagnose with the checklist (reference/tuning-checklist.md), in order — cheapest and most common causes first: statistics, a predicate that cannot use an index, the wrong or missing index, join shape, then PL/SQL row-by-row work. Name the cause in one sentence with the plan line that proves it.
4. Propose ONE change with its expected effect on the plan, and its cost: an index slows every write on that table and is a structural change (designed with pythia-table); a rewrite changes code (pythia-write, impact first). Gathering statistics on a shared schema is the DBA's or owner's call — say so, do not run it.
5. Apply it through the normal doors — pythia apply / pythia deploy — never a hint or an index created by hand.
6. Measure again with the exact step-2 commands, and report before vs after: plan shape, cost, cardinality. If it did not improve, say so and revert — a change that did not help still costs.

What this kit can and cannot measure

• explain is EXPLAIN PLAN + DBMS_XPLAN.DISPLAY: the estimated plan, license-safe. The plan actually used at runtime can differ (bind peeking, adaptive plans) — DBMS_XPLAN.DISPLAY_CURSOR needs V$ access, and AWR, ASH and the SQL Tuning Advisor need the Diagnostics/Tuning Pack licences. Never use or recommend them without the developer confirming the licence.
• Real timings: pythia sql "<select>" runs a query; wrap it in your shell's time for a rough number, on a dev schema, and say it is rough.

The usual causes, by frequency

Symptom in the plan	Usual cause	Usual fix
Rows estimate far from reality, "dynamic statistics used"	missing or stale statistics	ask the owner to gather stats (DBMS_STATS)
TABLE ACCESS FULL on a large table with a selective predicate	function or implicit conversion on the indexed column, leading % in LIKE, no index	rewrite the predicate to be sargable; then consider an index
INTERNAL_FUNCTION, TO_NUMBER("COL") in the predicates	comparing a VARCHAR2 column to a number, DATE to a string	compare like types; TO_DATE with a format on the literal side
MERGE JOIN CARTESIAN	a missing join predicate	add it
FILTER with NOT IN	nullable column under NOT IN	NOT EXISTS
A fast query called thousands of times	row-by-row PL/SQL, a function per row in SQL	one set-based statement; BULK COLLECT LIMIT + FORALL; scalar subquery caching; RESULT_CACHE for stable lookups
Many child cursors, parse time high	literals instead of binds in dynamic SQL	bind variables (USING)

Red Flags — STOP if you catch yourself thinking

Thought	Reality
"Just add a hint"	A hint freezes today's plan against tomorrow's data. Fix the cause; hints are a last resort the developer approves.
"An index will fix it"	Only if the predicate can use it — and it taxes every write. Prove it with the plan.
"It's slow, so it's the database"	The plan says whether it is the SQL, the data, or the 10,000 calls around it.
"I'll gather stats myself"	On a shared schema that changes every plan for everyone. The owner's call.
"It feels faster"	Same measurement, before and after, or it did not happen.
"Let me check AWR"	Licensed. Ask first.

When NOT to use this skill

• The code is wrong, not slow → pythia-review.
• Designing the index the diagnosis asked for → pythia-table.
• Writing the rewritten PL/SQL → pythia-write (after pythia-impact).