# ISS-0044: TopSQL Multi-Hotspot Ranking Lacks Hot-Node Correlation Rule

## Background

In GH-264, two independent Lark bot sessions diagnosed the same O11Y-2426677 expensive query alert and reached **different conclusions**:

- **WS-A** (incorrect): Identified hot nodes db-tikv-67 (90% CPU) and db-tikv-74 (77% CPU) from metrics. Then analyzed TopSQL and prioritized `dh_message_setting` JOIN on **db-tikv-30** (cpu_pct=65.4%) as the main hotspot. Missed that `dh_account_basic` was the SQL running on the actual hot nodes.

- **WS-B** (correct): Same hot node identification, but correctly found `dh_account_basic` as #1 by cpu_ms on the hot nodes, analyzed its execution plan (IndexFullScan, 52M rows), and identified the missing index.

## Root Cause of the Misdiagnosis

The agent has no explicit rule to **correlate hot nodes from metrics with per-query per-node TopSQL data**. The current flow:

1. Metrics phase: identify hot nodes (db-tikv-67/74)
2. TopSQL phase: get all hot SQL across all nodes
3. Analysis: pick "the hottest" SQL — but "hottest" is ambiguous

WS-A picked the SQL with highest `cpu_pct` on any single node (db-tikv-30 at 65.4%), which was a different hotspot entirely. WS-B picked the SQL with highest `cpu_ms` overall, which happened to be on the hot nodes.

## Observed Evidence

From WS-A's conversation:

| TopSQL Metric | SQL | Node | Value |
|---|---|---|---|
| cpu_pct (single node) | `dh_message_setting` JOIN | db-tikv-30 | 65.4% |
| cpu_ms (total) | `dh_pay_order_success_user_summary` | multiple | 3.2M |
| cpu_ms (hot nodes) | `dh_account_basic` digest `9C0FA76B` | db-tikv-67, db-tikv-74 | highest on these nodes |

WS-A prioritized row 1 (wrong) instead of row 3 (correct for explaining db-tikv-67/74 hotspot).

## Fix Plan

### Location

`agentkit/skills/builtin/tidb-clinic-diagnosis/SKILL.md` — Node C (Analysis) section.

### Change

Add a **hot-node attribution rule** after the TopSQL collection step:

```markdown
### Hot-Node Attribution (MANDATORY when multiple hot SQL found)

When TopSQL returns multiple hot SQL patterns across different nodes:

1. **Filter by hot nodes**: From the metrics phase, identify which nodes have
   anomalous CPU/latency (e.g., CPU > 80% or top-2 by CPU). Call these the
   "hot nodes".

2. **Rank within hot nodes**: Filter TopSQL results to only the hot nodes.
   Rank these by `cpu_ms` (total CPU time) on those specific nodes.

3. **The #1 SQL on the hot nodes is the primary suspect**, not the SQL with
   highest cpu_pct on any arbitrary node.

4. **Deep-dive the primary suspect**: Analyze its execution plan (decoded_plan),
   actRows/estRows divergence, and index usage before concluding.

5. **Other hot SQL on other nodes are secondary**: Mention them but do not
   attribute the primary hotspot to SQL running on different nodes.

Example: If metrics show db-tikv-67/74 are hot (90%/77% CPU), and TopSQL shows:
- SQL-A on db-tikv-30 at 65% cpu_pct
- SQL-B on db-tikv-67/74 at 45% cpu_pct but highest cpu_ms on those nodes

→ SQL-B is the primary suspect for the db-tikv-67/74 hotspot, NOT SQL-A.
```

### Verification

1. Re-run O11Y-2426677 regression (Mode F) — agent should identify `dh_account_basic` as the primary suspect on db-tikv-67/74
2. The agent should NOT prioritize `dh_message_setting` on db-tikv-30 as the main hotspot
3. Both workspaces should produce consistent conclusions when run with this rule