# Query Latency And Workload Hotspot Dual Scenario

<!-- auto-oncall-optimization-record
{
  "schemaVersion": "auto-oncall-optimization-record-v1",
  "optimizationId": "latency-hotspot-dual-scenario-2026-08-11",
  "problems": [
    {
      "problemId": "query-latency-and-workload-hotspot-conflated",
      "summary": "One query-execution scenario used TopSQL workload concentration as a gate for both resource-hotspot and per-request wall-clock diagnosis, so a valid Slow Query identity could be treated as unresolved when it was absent from TopSQL top-N.",
      "status": "optimized_verified",
      "issueKeys": ["APID-29296"],
      "sourceRefs": [
        "https://tidb.atlassian.net/browse/APID-29296#problem:latency-hotspot-scenario-conflation"
      ],
      "signalTextIncludesAny": [
        "Slow Query digest absent from TopSQL",
        "query latency and workload hotspot conflated",
        "TopSQL used as a query-latency completion gate",
        "overall latency did not schedule both branches"
      ],
      "observedThrough": "2026-08-11",
      "effectiveAt": "2026-08-11T19:52:04+08:00",
      "verifiedAt": "2026-08-11T20:13:34+08:00",
      "attemptedAction": "Split new routing into query-latency and workload-hotspot scenarios, require latency symptoms to schedule both branches, make Slow Query primary for wall-clock attribution, and make aligned TopSQL primary for hotspot attribution while retaining the legacy query-execution identifier only for compatibility.",
      "evidenceUrl": "https://tidb.atlassian.net/browse/APID-29296?focusedCommentId=23399342"
    }
  ]
}
-->

- Optimization ID: `latency-hotspot-dual-scenario-2026-08-11`
- Owner: auto-oncall-services Scenario Router and diagnostic orchestration
- Source reference: `APID-29296` staging replay
- Implementation status: deployed to staging in image
  `d5b86d91700f9eaa523985ae830c427f106a2645`
- Effective status: `optimized_verified`

## Bounded problem

The legacy `query-execution` scenario combined two different questions:
whether one request was slow in wall-clock time and whether a concentrated
CPU/RU/QPS/scan workload explained the incident. In `APID-29296`, Slow Query
identified a 294.252-second request, but its digest was absent from the aligned
TopSQL top-N response. The combined scenario treated that absence as unresolved
workload identity instead of preserving the Slow Query request identity and
classifying only the independent hotspot branch as unmatched.

## Candidate change

- Add `query-latency`, led by exact Slow Query request identity and supported
  execution/wait/result-response fields.
- Add `workload-hotspot`, led by an aligned instance/resource signal and
  TopSQL workload contribution.
- Deterministically select both scenarios for a specific slow query/request or
  an overall runtime/P99/P999 latency increase. Select only workload-hotspot for
  a resource-hotspot report without latency.
- Start both scenario pipelines through the existing multi-scenario scheduler;
  preserve serial Wetech and Manus Provider Executor operations.
- Keep `query-execution` readable for persisted compatibility but prohibit it
  for new routes.

## Verification evidence

Focused route, prompt, and skill-contract tests verify dual scheduling for a
294-second query and an overall runtime P999 increase, hotspot-only routing for
a CPU-only report, Slow Query-first acquisition for query latency, and
TopSQL-first acquisition for workload hotspot.

The staging rollout reached 1/1 Ready with zero restarts, and `/healthz` plus
`/readyz` both returned HTTP 200. Same-cutoff `mode=write` replay
`018689fd-dff1-490f-9e05-da68a123b352` / DB run `120077` completed in
1013.7 seconds. Its canonical route selected `query-latency` as primary and
`workload-hotspot` as secondary; both scenario workspaces, Evidence Agent
plans, Provider Executor batches, handoffs, and Scenario Diagnostic Agent
decisions were materialized. Wetech execution remained serial: the hotspot
batch completed before the latency batch acquired the provider lock.

The latency branch preserved 294.252-second, 41.681-second, and 38.417-second
Slow Query requests independently of TopSQL. The hotspot branch independently
materialized instance-labelled QPS, TiDB CPU, duration P99, KV-request P99,
TopSQL, and adjacent-baseline evidence. The existing Jira internal comment
`23399342` was updated in place and read back with `public=false`. These facts
pass the bounded scenario-conflation criterion; they do not prove the case's
final root cause.

## Unresolved gaps

- The final draft remained `human_required`: it established an unaccounted
  TiDB request residence phase and a correlated transient workload burst, but
  did not identify the statistics-collection statement and single TiDB heap
  spike as the root cause found by the later human investigation.
- Five optional round-4 PromQL refinements returned Grafana HTTP 400 because
  the generated instance regex used an invalid `\.` escape. This is a query
  construction error, not a 500/502 provider instability signal.
- The historical replay target is before the first acknowledgement, so later
  customer supplements that the human used are correctly absent from replay
  input. Provider evidence still found the slow requests, but the answer must
  not be compared as though it received the later customer timeline.

## Next validation action

Track the PromQL regex construction failure as a separate query-planning
problem. For the remaining case-quality gap, test whether memory-sorted Slow
Query fields plus per-TiDB heap alignment can identify the `ANALYZE` statement
without using post-cutoff customer comments; do not reopen or broaden this
verified scenario-splitting problem.