---
name: sql-plan-or-query
description: Diagnose SQL plan, access-path, slow-query, TopSQL, and plan-shift root causes from collected TiDB Clinic evidence.
---

# Use When

Use this family subskill when the primary causal driver is a specific SQL/query workload,
inefficient plan, missing access path, full or large scan, plan shift, or query-driven
resource pressure.

# Evidence Inputs

- A concrete SQL text/template, digest, plan digest, or tightly bounded statement family.
- Direct runtime evidence appropriate to the claim: SlowQuery/expensive-query execution for latency, TopSQL for CPU, keys/bytes plus storage metrics for IO, or lock/backoff evidence for contention.
- Decoded plan, statement plan, plan replayer, or plan summary when making operator/access-path claims.
- Table/index/statistics evidence before recommending ANALYZE, SQL binding, or index changes.

Missing plan/schema/statistics evidence blocks concrete optimizer changes. Follow
AutoX's case-contract for the supported validation level; when required evidence
is absent, report an incomplete workflow instead of inventing a classification.

# Classification

- `standard_root_cause_class`: `sql_plan_or_query`
- compatible `primary_class`: `sql_plan_or_query`
- common symptom tags: `slow_query`, `high_cpu`, `tikv_hotspot`, `io_saturation`
- specialist skills: `tidb-query-tuning-autox`

# Specialist Loading Contract

When this family is a task-selected route (Copilot, Autopilot, or preferred
route), include `tidb-query-tuning-autox` in the analyst's required list and load
it before synthesis. This is a hypothesis to test, not a finding to endorse.
Missing query evidence is a gap for that branch, not a reason to silently skip
its required specialist. When SQL is discovered only during analysis, use the
candidate and causal checks below before adding it.

Load the AutoX root, index, `references/case-contract.md`, and
`workflow/diagnosis-classification/SUBSKILL.md` together. Use existing evidence
only; no collection, batch runs, or local validation. Follow its evidence gates
for both classification and recommendations. Record actual loads in
`selected_diagnosis_skills`. An unavailable cataloged AutoX path is a loading
gap, not permission to replace it with the general fallback.

# Decision Rules

- Treat the query as primary only when it is leading, dominant, and timeline-aligned.
- Do not promote a top-ranked SQL to root cause solely because it is ranked first.
- After the SQL specialist candidate and causal checks pass, use `tidb-query-tuning-autox` to classify
  the SQL-internal mechanism, such as scan amplification, cardinality error, wrong
  access path, wrong join plan, plan instability, cop seek amplification, order path
  tradeoff, or non-optimizer bottleneck.
- Route AutoX when SQL is primary or a material contributing mechanism whose classification
  changes remediation. Do not route it merely because SlowQuery or TopSQL contains a ranked row.
- If TiKV or IO pressure is caused by the query scan volume, classify SQL as primary and
  TiKV/IO as contributing or secondary.
- Apply the ANALYZE pitfall guardrail: stale stats are not primary when missing index,
  bad access path, or plan shape explains the cost better.

# Final Response Anchors

- Say `slow query log` or `expensive query log evidence` when those artifacts are present.
- Say `execution plan changed`, `plan shift`, or `plan regression` when plan drift is part
  of the causal chain.
- Name the digest, table/path, missing or ineffective index, and the observed impact.

# SQL Specialist Candidate and Causal Checks

Before writing a conclusion, classify query findings as **evidence only**, a **material contributing mechanism**, or a **primary query cause**.

Default: TopSQL, SlowQuery, and `decoded_plan` are **query evidence items**. They help describe affected workload, amplification, and remediation candidates, but they do **not** trigger root-cause attribution or SQL specialist loading by themselves.

Load `tidb-query-tuning-autox` as the SQL mechanism specialist, not as a generic slow-query reader. Production incidents often have slow queries as downstream symptoms, while query-driven scan, seek, retry, lock, or plan amplification can also create downstream TiKV/IO pressure. Treat SQL and non-SQL classes as competing causal candidates before choosing the route.

SQL specialist candidate conditions (ANY route below, followed by the causal checks):
1. `sql_plan_or_query` is the selected primary root-cause class.
2. A concrete SQL/digest is a contributing candidate with confidence >= 0.60 and deciding whether its plan or runtime behavior is actionable would materially change remediation.
3. The user explicitly requests SQL/plan diagnosis for a concrete SQL, digest, or expensive-query incident.

For analyst-discovered candidates, verify these causal checks before adding the specialist. For a task-selected SQL route, load AutoX first as required above and use these checks to adjudicate it:
1. **Concrete identity:** evidence identifies a SQL text/template, digest, plan digest, or a tightly bounded statement family. A generic "slow queries exist" observation is insufficient.
2. **Direct mechanism signal beyond rank:** at least one available artifact indicates scan/seek amplification, plan/access-path behavior, cardinality divergence, lock/backoff/retry time, read keys/bytes, spill, execution-time concentration, or a workload shift for that SQL.
3. **Claim-appropriate attribution:** use TopSQL on the affected instance when claiming CPU; use Slow Query/expensive-query execution evidence when claiming latency; use keys/bytes plus storage metrics when claiming query-driven IO; use lock/backoff evidence when claiming contention. Do not require TiKV CPU or a fixed TopSQL percentage for non-CPU claims.
4. **Competing-cause check:** compare earlier or independent infra/network/EBS/component failures. If one fully explains the symptom and the SQL only became slow downstream, keep SQL as affected workload; do not add an unselected SQL branch merely for this symptom. An already task-selected SQL branch must still be adjudicated using its required specialist. If query amplification plausibly creates or materially worsens the downstream pressure, load AutoX to adjudicate optimizer-action versus `non_optimizer_bottleneck`.

Non-triggers:
- Slow query `avg_time`, `max_time`, or `exec_count` by itself.
- `decoded_plan` `actRows >> estRows` by itself.
- High TiKV gRPC/coprocessor P99 by itself.
- A top-ranked or globally visible TopSQL row without affected-scope and timeline attribution.
- Any query finding when an earlier independent infra/network/EBS/component failure fully explains the symptom and the SQL has no separate mechanism signal.

Required steps (BLOCKING — must complete before final synthesis):
1. Record which candidate route and causal checks passed, rather than merely saying a slow query ranked first.
2. In one batched read, load `.opencode/.all-skills/tidb-query-tuning-autox/SKILL.md`, its `SUBSKILLS_INDEX.md`, `references/case-contract.md`, and `workflow/diagnosis-classification/SUBSKILL.md`. If AutoX is unavailable, record the loading gap; do not substitute an uncataloged skill.
3. Analyze the existing SQL evidence cited by the handoff: full SQL or normalized SQL, digest/plan digest, representative executions, decoded or statement plan, execution details, schema/index/statistics, TopSQL, and relevant system pressure. **Do NOT re-query** from the diagnosis agent.
4. Use AutoX classification to identify the concrete mechanism and recommendation direction: Binding, Index, TiFlash/MPP, non-optimizer investigation, or no optimizer action.
5. Match precision to evidence. Follow AutoX's case-contract evidence gates and validation levels. Missing plan/schema/statistics never licenses a concrete Binding/index claim; where the contract requires schema for classification, report an incomplete workflow and request the missing evidence.

If SQL was not task-selected and no SQL specialist candidate route passes, do **not** load SQL specialist skills. Keep query findings in the evidence chain as symptoms or affected workload and continue root-cause synthesis from stronger non-query evidence.

Checkpoint cleared when: the SQL candidate has been adjudicated as primary, contributing, affected-only, or rejected; its AutoX mechanism and validation level are recorded when routed; and recommendations do not exceed the available plan/schema/statistics evidence.