# Clinic Slow Query Coprocessor Localization - Owner: Clinic Provider Executor - Source: TCID-12986 human attribution using full-day Slow Query grouped by plan and Coprocessor address - Implementation status: deployed to staging - Effectiveness status: optimized_verified ## Bounded problem The Clinic Slow Query operation requested latency, scan, wait, memory, and CPU fields but omitted `instance`, `plan_digest`, `cop_proc_max`, `cop_proc_addr`, `cop_wait_max`, and `cop_wait_addr`. The Data Proxy schema exposes those fields, but the Provider Executor neither selected nor materialized them. It also joined decoded-plan profiles by SQL Digest alone, allowing one plan to overwrite rows for other plans of the same SQL. ## Candidate change - Add the instance, plan, Cop process, and Cop wait fields to both Data Proxy and shared-tier NGM Slow Query projections. - Emit only six-character SQL and plan Digest prefixes while retaining the full values inside Provider execution. - Preserve the paired max value and address together in compact rows and the Scenario handoff. Emit each retained row's identity immediately followed by its plan facts, so the representative plan cannot be truncated after several execution summaries and later Copr-localized rows still use remaining space. - Match decoded-plan profiles by SQL Digest plus plan Digest when both exist; never apply one plan profile to every row sharing the SQL Digest. - Apply the same compact field contract to the passive Agent9 Clinic Tool. ## Verification evidence On 2026-08-18, the current full TypeScript suite passed 902/902 tests. Contract tests prove the canonical Data Proxy SQL selects all six localization fields, compact output preserves the paired values and addresses, and the Scenario handoff contains them without exposing complete Digests or raw SQL. A direct production Clinic Data Proxy query over the frozen TCID-12986 window returned HTTP 200 with 10 bounded rows for SQL `bc6171`. The response included `instance`, `plan_digest`, `cop_proc_max`, `cop_proc_addr`, `cop_wait_max`, and `cop_wait_addr`. The three slowest returned `1dd1366f` executions all had both Coprocessor process and wait addresses on TiKV-13; the same result set also contained `2d7c47cf` executions on TiKV-13. This proves the Provider fields are available and can supply the missing per-execution localization after rollout. The first post-deployment read replay, run `b8549088-a9b3-408d-9712-228c2727b24e`, proved the Provider path but exposed a handoff ordering defect: row 2 was `bc6171` / `1dd136` with both Copr addresses on TiKV-13, yet row 1's 32 decoded-plan operators exhausted the bounded factual statement before row 2's address fields. This record remains unverified and the candidate now includes row-summary-first ordering. The follow-up staging replay `b403a792-e319-42a2-b5ca-6bc147c0d1f4` proved the ordering repair: `row_2` retained `bc6171`, plan `2d7c47`, and both Coprocessor addresses on TiKV-13 before any decoded-plan facts. The Scenario Diagnostic Agent used the processing-versus-wait distinction, but the ticket SQL identity and later bad-plan window remained unresolved upstream. The deployed identity replay `290c6ac9-313c-4572-94ce-cef8358e1ccc` then bound the target SQL to `bc6171` and localized plan `1dd136` to TiKV-13 through TopSQL, but its digest-targeted Slow Query refinement returned empty. The SQL had queried the 15-minute finish-time lookahead, ordered by query time, limited to 100, and only then checked incident overlap locally. One hundred later `bc6171` finishes exhausted the limit before incident-overlapping executions. The candidate now applies the equivalent execution-overlap predicate in the Data Proxy query before ordering and limiting; the lookahead remains available for executions that began during the incident and finished afterward. The post-deployment replay `328549d3-bc8a-4678-bedf-1f823ea32a4a` recovered the target `1dd136` decoded plan, including an IndexRangeScan estimate of about 52 rows versus about 333,736 actual rows. However, the profile merge overlaid the same representative profile's timestamp, latency, and resource values onto every primary execution of that plan. Twenty apparently identical row summaries then exhausted the handoff before operator facts, so the Scenario Diagnostic Agent saw scan totals but not `estRows`/`actRows`. The Provider Executor now keeps every primary execution's own fields and attaches only profile-owned plan fields to the first row for each SQL and plan Digest. The next deployed replay `1f96c079-5492-49bd-a00e-375c7543235c` proved that execution identity was no longer overwritten: its handoff retained distinct 36.78, 35.76, 30.22, 28.86, and 26.96 second executions, all with TiKV-13 as the maximum Coprocessor processing and wait address. The compact report also contained plan `1dd136`, including `IndexRangeScan` estimate 51.66 versus 333,736 actual rows. However, the 4,000-character factual statement still placed all five detailed execution summaries before the decoded plan and was truncated first. The Scenario Diagnostic Agent consequently diagnosed heavy scan/processing but could not identify the cardinality error. The final repair now interleaves each execution and its plan facts; its contract test proves both the representative `estRows`/`actRows` and the later TiKV-13 Copr localization survive the same bounded statement. The full suite passes 904/904 tests. The final staging image `abf7cc4f0cafd8e4f102151417aa90e10f81d322` rolled out as generation 458 with 1/1 Ready replicas and zero restarts. Replay `68f24ad3-a76d-49c3-a699-ebcbb4cdf5fd` retained plan `1dd136` before later execution summaries in both Scenario handoffs. Both Scenario Diagnostic Agents then used the approximately 52–103 estimated versus approximately 333,000 actual rows to identify the cardinality error and processing-heavy `IndexHashJoin`; the workload scenario also used TiKV-13 Copr localization and returned `causal_confirmed`. The Router carried that mechanism into the final reply rather than merely describing scan totals. The stable historical initial mapping resolved to Jira comment `23357171`. Before the direct recovery update it was verified as `public=false`; after the in-place PUT, the same ID was read back as `public=false` with the expected `bc6171`, `1dd136`, estimate, actual-row, and TiKV-13 attribution facts. The issue comment count remained unchanged. ## Unresolved gaps Slow Query Coprocessor address is corroborating per-execution localization; it does not replace aggregate TopSQL CPU attribution. This case still lacks a matched normal-window execution-volume baseline and therefore does not prove the human's approximate one-third contribution fraction or capacity headroom. Those are separate quantitative attribution gaps, not missing plan or Copr localization. ## Next validation action Monitor later multi-plan Slow Query cases to ensure each representative plan retains estimate-versus-actual facts while later executions still retain their own time, resource, and Copr localization fields.