---
name: clinic-data-proxy
description: |
  Tier-aware slow query, TopSQL, and Statements guidance through clinic-cli.
---

# Slow Query, TopSQL, and Statements

Use `sql` only for supported persisted non-BYOC data. The CLI rejects BYOC SQL
instead of silently falling back to warehouse tables.

Use the parent CLI command:

```text
python3 .opencode/skills/builtin/clinic-cli/scripts/clinic_cli.py sql --help
```

Typical use:

```text
python3 .opencode/skills/builtin/clinic-cli/scripts/clinic_cli.py sql \
  --cluster-id <cluster_id> \
  --query-type slow_queries \
  --name slow_query_top_digest \
  --sql "<sql>"
```

For BYOC customer-side O11y, use the neutral command names:

```text
python3 .opencode/skills/builtin/clinic-cli/scripts/clinic_cli.py slow-query list \
  --cluster-id <cluster_id> --start <epoch> --end <epoch> \
  --limit 20 --order-by query_time --desc

python3 .opencode/skills/builtin/clinic-cli/scripts/clinic_cli.py top-sql instances \
  --cluster-id <cluster_id> --start <epoch> --end <epoch>

python3 .opencode/skills/builtin/clinic-cli/scripts/clinic_cli.py top-sql summary \
  --cluster-id <cluster_id> --start <epoch> --end <epoch> \
  --window 1m --top 20 --instance <instance> --instance-type tikv

python3 .opencode/skills/builtin/clinic-cli/scripts/clinic_cli.py statements plans \
  --cluster-id <cluster_id> --start <epoch> --end <epoch> --digest <sql_digest>
```

Use `slow-query available-fields` before assuming an optional field exists.
Statements also provide `available-fields`, `statement-types`, `list`,
`plans`, and `plan-detail`. List/plans/detail evidence is persisted to
`queries.json.statements`; discovery output is not persisted.

## Query types

| `--query-type` | Use for |
| --- | --- |
| `slow_queries` | Slow query logs and statement-level latency analysis. |
| `topsql` | CPU, keys, logical IO, network, and statement aggregates by SQL / plan digest. |

## SQL rules

- For top slow-query digests in a bounded incident window, prefer
  `clinic_cli.py ad-hoc slow-query-top` before writing SQL manually.
- Use MySQL-standard SQL by default.
- Always include a date partition filter.
- `slow_query_logs` date format is `YYYYMMDD`.
- `topsql` date format is `YYYY-MM-DD`.
- Prefer bounded time predicates in addition to date partition filters.
- `rows` may be empty; treat empty results as a valid diagnostic signal.
- Numeric fields may arrive as strings; cast before comparing or formatting.

## Slow query examples

Top digests by max query time:

```sql
SELECT
  digest,
  any_value(query) AS sample_sql,
  COUNT(*) AS exec_count,
  MAX(query_time) AS max_query_time,
  AVG(query_time) AS avg_query_time
FROM clinic_data_proxy.slow_query_logs
WHERE date = '20260405'
GROUP BY digest
ORDER BY max_query_time DESC
LIMIT 20
```

Find statements overlapping an incident window:

```sql
SELECT
  digest,
  any_value(query) AS sample_sql,
  COUNT(*) AS exec_count,
  MAX(query_time) AS max_query_time,
  SUM(process_time) AS total_process_time,
  SUM(wait_time) AS total_wait_time
FROM clinic_data_proxy.slow_query_logs
WHERE date = '20260405'
  AND time >= '2026-04-05 11:30:00'
  AND time <= '2026-04-05 12:30:00'
GROUP BY digest
ORDER BY total_process_time DESC
LIMIT 20
```

## TopSQL examples

Top SQL by CPU:

```sql
SELECT
  sql_digest,
  any_value(normalized_sql) AS sample_sql,
  SUM(topsql_cpu_time_ms) AS cpu_ms,
  SUM(exec_count_per_sec) AS exec_rate
FROM clinic_data_proxy.topsql
WHERE date = '2026-04-05'
GROUP BY sql_digest
ORDER BY cpu_ms DESC
LIMIT 20
```

Top SQL by read pressure:

```sql
SELECT
  sql_digest,
  any_value(normalized_sql) AS sample_sql,
  SUM(topsql_kv_total_keys) AS total_keys,
  SUM(topsql_kv_processed_keys) AS processed_keys
FROM clinic_data_proxy.topsql
WHERE date = '2026-04-05'
GROUP BY sql_digest
ORDER BY total_keys DESC
LIMIT 20
```

## Interpretation notes

- High p99 latency is not automatically a CPU hotspot. Correlate slow query, TopSQL, CPU, and KV metrics.
- For hot TiKV diagnosis, compare SQL digests with TiKV CPU, gRPC duration, scheduler latch, storage async write, and region hotspot signals.
- Avoid pasting full customer SQL into user-facing answers when a digest or redacted sample is sufficient.