# Slow Query Execution Window Semantics

<!-- auto-oncall-optimization-record
{
  "schemaVersion": "auto-oncall-optimization-record-v1",
  "optimizationId": "slow-query-execution-window-2026-08-11",
  "problems": [
    {
      "problemId": "slow-query-finish-time-treated-as-onset",
      "summary": "Slow Query Time/timestamp could be treated as query onset and compared directly with an incident spike, even though it is the finish-side log print time and the execution can span the incident window.",
      "status": "candidate",
      "issueKeys": ["APID-29296"],
      "sourceRefs": ["https://tidb.atlassian.net/browse/APID-29296#problem:slow-query-finish-time-window"],
      "signalTextIncludesAny": [
        "timestamp treated as ANALYZE onset",
        "Slow Query finish time compared as start time",
        "query duration omitted from incident overlap"
      ],
      "observedThrough": "2026-08-11",
      "effectiveAt": null,
      "verifiedAt": null,
      "attemptedAction": "Query a bounded finish-time lookahead, derive each Slow Query execution interval from Time minus Query_time, filter candidates by overlap with the canonical incident window before Top-N, and constrain diagnosis to correlation unless same-row mechanism evidence supports more.",
      "evidenceUrl": "https://github.com/tidbcloud/aisupport/tree/main/auto-oncall-services/docs/opts/2026-08-11-slow-query-execution-window.md"
    }
  ]
}
-->

- Optimization ID: `slow-query-execution-window-2026-08-11`
- Owner: auto-oncall-services Provider Executors and diagnostic contracts
- Source: APID-29296 replay review on 2026-08-11
- Implementation status: local candidate
- Effective status: `candidate`

## Bounded problem

The prior replay retained `timestamp` and `query_time`, but the diagnosis called
the timestamp the ANALYZE onset. TiDB Slow Query `Time` is emitted when the
completed statement is printed to the slow log. Comparing that value directly
with an incident spike can invert the temporal conclusion and misses a long
execution that finishes shortly after the incident window.

## Candidate change

- Keep the canonical incident window unchanged.
- Extend only the Provider Executor's Slow Query finish-time candidate request
  by 15 minutes and fetch at least 100 internal candidates.
- Derive `start_time=finish_time-query_time`, interval status, overlap status,
  and overlap seconds for Clinic, Wetech, and Manus legacy results and the
  Agent9 Manus managed Tool path that exposes Slow Query directly.
- Exclude definitely non-overlapping candidates before applying the requested
  result limit; retain unclassifiable rows with an explicit unavailable status.
- Treat interval overlap as correlation. Treat same-row `memory_max` as direct
  peak-memory evidence for that execution, not automatic process or cluster
  causality.

## Verification evidence

Local validation on 2026-08-11 passed:

- TypeScript typecheck.
- All 738 repository tests across 102 suites.
- 65 Wetech helper tests and 26 Manus helper tests.
- `quick_validate.py` for both changed provider skills.

The tests cover finish-time lookahead, candidate expansion, interval derivation,
overlap filtering, non-overlap exclusion, Clinic handoff admission, and the
Agent9 Manus package digest. Deployment and case replay validation are pending.

## Unresolved gaps

- The candidate is not yet deployed.
- APID-29296 and ST-2863 have not been replayed with the changed interval
  semantics and Router-owned final diagnosis.

## Next validation action

Run the complete repository and skill suites, deploy staging, then replay
APID-29296 and ST-2863 with their historical cutoffs.