skillZs
★ LIVE SKILL TAGS ★
>>> LIVE SKILLS INDEX <<<
* OPEN SOURCE *
NO LOGIN, NO TRACKING
※ REAL INSTALL DATA ※
← back to all skills
thaildhe172591/pythia80 installs

pythia-tune

Use when a query, report or PL/SQL unit is slow, before adding an index or a hint, or when someone says "cham", "toi uu truy van", "optimize this query", "why is this report slow", "full table scan". Measures with the optimizer's own plan and statistics, changes one thing at a time, and proves the gain with the same measurement - license-safe, no AWR/ASH or Tuning Pack.

How do I install this agent skill?

npx skills add https://github.com/thaildhe172591/pythia --skill pythia-tune
view source ↗

Is this agent skill safe to install?

  • Gen Agent Trust Hubpass

    The skill provides a structured methodology for tuning SQL and PL/SQL queries using the 'pythia' toolset. It emphasizes safety through license checks, measurement before and after changes, and owner permissions for administrative tasks. No security threats were detected.

  • Socketpass

    No alerts

  • Snykpass

    Risk: LOW · No issues

What does this agent skill do?

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 planUsual causeUsual fix
Rows estimate far from reality, "dynamic statistics used"missing or stale statisticsask the owner to gather stats (DBMS_STATS)
TABLE ACCESS FULL on a large table with a selective predicatefunction or implicit conversion on the indexed column, leading % in LIKE, no indexrewrite the predicate to be sargable; then consider an index
INTERNAL_FUNCTION, TO_NUMBER("COL") in the predicatescomparing a VARCHAR2 column to a number, DATE to a stringcompare like types; TO_DATE with a format on the literal side
MERGE JOIN CARTESIANa missing join predicateadd it
FILTER with NOT INnullable column under NOT INNOT EXISTS
A fast query called thousands of timesrow-by-row PL/SQL, a function per row in SQLone set-based statement; BULK COLLECT LIMIT + FORALL; scalar subquery caching; RESULT_CACHE for stable lookups
Many child cursors, parse time highliterals instead of binds in dynamic SQLbind variables (USING)

Red Flags — STOP if you catch yourself thinking

ThoughtReality
"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).

Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.

<a href="https://skillzs.dev/skills/thaildhe172591/pythia/pythia-tune">View pythia-tune on skillZs</a>