pythia-tune

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

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.

开始时声明： “正在使用 pythia-tune——先测量，再进行任何更改。”

阶段： 学习 → 询问 → 执行——先查看执行计划和统计信息，只提出一项更改，然后使用相同的测量方式进行复测

性能方面的民间偏方最容易让人产生虚假的信心。“添加 index”、“使用 hint”、“将其重写为 join”对某些执行计划有效，对另一些则有害，而只有执行计划才能告诉你哪种方法适用。没有前后对比就进行调优，只是把猜测变得更复杂。

The Iron Law

铁律

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

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

The Workflow

工作流

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.
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.
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.
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.
Apply it through the normal doors —
```
pythia apply
```
/
```
pythia deploy
```
— never a hint or an index created by hand.
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.

获取真实语句。 获取应用程序实际执行的准确 SQL——通过
```
pythia src
```
/
```
pythia grep
```
获取，并让绑定变量保持为绑定变量（
```
:id
```
），而不是粘贴的字面量：字面量可能会获得与绑定变量不同的执行计划。
测量。
- ```
pythia explain "<the select>"
```
  ——查看优化器生成的执行计划；读取行数估算（cardinality）、cost、每张表的访问路径，以及 pythia 添加的
```
-- note:
```
  行（full scan、Cartesian join、implicit conversion、dynamic sampling）。
- 对执行计划中的每张表运行
```
pythia stats <TABLE>
```
  ——查看行数、统计信息的收集时间、是否为 STALE、index 及其列，以及没有 index 的外键。
- 对于 DML，解释用于查找目标行的
```
SELECT
```
  。
按照检查清单进行诊断（
```
reference/tuning-checklist.md
```
），并依次检查成本最低、最常见的原因：统计信息、无法使用 index 的谓词、错误或缺失的 index、join 结构，最后检查 PL/SQL 中的逐行处理。用一句话说明原因，并指出能够证明该原因的执行计划行。
提出一项更改，说明它对执行计划的预期影响以及成本：index 会拖慢该表上的每次写入，属于
```
structural
```
变更（使用
```
pythia-table
```
设计）；重写则会改变代码（
```
pythia-write
```
，先执行 impact 分析）。在共享 schema 上收集统计信息应由 DBA 或 schema 所有者决定——只说明这一点，不要自行执行。
通过标准入口应用更改——使用
```
pythia apply
```
/
```
pythia deploy
```
——绝不能使用 hint，也不要手动创建 index。
再次测量，使用与步骤 2 完全相同的命令，并报告更改前后的对比：执行计划结构、cost、cardinality。如果没有改善，就明确说明并回滚——没有带来收益的更改仍然有成本。

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.

```
explain
```
使用 EXPLAIN PLAN +
```
DBMS_XPLAN.DISPLAY
```
，提供估算的执行计划，无需额外许可。运行时实际使用的执行计划可能有所不同（bind peeking、adaptive plans）——
```
DBMS_XPLAN.DISPLAY_CURSOR
```
需要
```
V$
```
访问权限，而 AWR、ASH 和 SQL Tuning Advisor 需要 Diagnostics/Tuning Pack 许可。除非开发人员确认已有相应许可，否则绝不能使用或建议使用这些功能。
真实耗时：
```
pythia sql "<select>"
```
会运行查询；可以在开发 schema 上使用 shell 的
```
time
```
命令包裹它来获取大致耗时，并明确说明该结果仅为近似值。

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` )

执行计划中的症状	常见原因	常见修复方法
行数估算与实际情况相差很大，“dynamic statistics used”	统计信息缺失或已过期	请 schema 所有者收集统计信息（ `DBMS_STATS` ）
对大表使用选择性谓词时出现 `TABLE ACCESS FULL`	在已建立 index 的列上使用函数或发生 implicit conversion、LIKE 以 `%` 开头、缺少 index	重写谓词，使其具备 sargability；然后考虑添加 index
谓词中出现 `INTERNAL_FUNCTION` 、 `TO_NUMBER("COL")`	将 VARCHAR2 列与数字比较，或将 DATE 与字符串比较	比较相同类型的数据；在字面量一侧使用带格式的 `TO_DATE`
`MERGE JOIN CARTESIAN`	缺少 join 谓词	添加 join 谓词
`FILTER` 与 `NOT IN` 组合使用	`NOT IN` 下的列可为空	使用 `NOT EXISTS`
查询本身很快，但被调用了数千次	PL/SQL 逐行处理，或在 SQL 中每行调用一次函数	改用一条 set-based 语句；使用 `BULK COLLECT LIMIT` + `FORALL` ；使用 scalar subquery caching；对稳定查找使用 `RESULT_CACHE`
存在大量子游标，且解析时间较高	动态 SQL 使用字面量而不是绑定变量	使用绑定变量（ `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.

想法	实际情况
“直接加个 hint”	hint 会将今天的执行计划固定下来，无法适应未来的数据。应修复根本原因；hint 仅是由开发人员批准的最后手段。
“加个 index 就能解决”	只有当谓词能够使用该 index 时才成立，而且它会给每次写入增加负担。必须通过执行计划证明效果。
“它很慢，所以问题出在数据库”	执行计划能够说明问题究竟来自 SQL、数据，还是外围执行的 10,000 次调用。
“我自己来收集统计信息”	在共享 schema 上收集统计信息会改变所有人的执行计划，这应由 schema 所有者决定。
“感觉变快了”	更改前后必须使用相同的测量方式，否则就等于没有发生变化。
“让我检查一下 AWR”	需要许可，必须先询问。

When NOT to use this skill

不应使用此 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
```
).

代码写错了，而不是执行缓慢 →
```
pythia-review
```
。
根据诊断结果设计 index →
```
pythia-table
```
。
编写重写后的 PL/SQL →
```
pythia-write
```
（先执行
```
pythia-impact
```
）。