# Clinic TopSQL Global Rank Bound

<!-- auto-oncall-optimization-record
{
  "schemaVersion": "auto-oncall-optimization-record-v1",
  "optimizationId": "clinic-topsql-global-rank-bound-2026-08-18",
  "problems": [
    {
      "problemId": "clinic-supplied-sql-candidate-reduces-global-topn-and-lacks-tail-bound",
      "summary": "Clinic TopSQL split one row limit between supplied-SQL candidates and global competitors, so a requested Top20 contained only ten global ranks and absence from that partial list could not bound the target workload contribution.",
      "status": "optimized_verified",
      "applicability": "Automatic Clinic TopSQL discovery with customer-supplied SQL text and no exact Digest.",
      "issueKeys": ["TCID-12986"],
      "sourceRefs": ["https://tidb.atlassian.net/browse/TCID-12986#problem:clinic-topsql-global-rank-bound"],
      "signalTextIncludesAny": ["outside Top20", "rank 21", "TopSQL tail", "global TopN split"],
      "observedThrough": "2026-08-18",
      "effectiveAt": "2026-08-18T13:37:31Z",
      "verifiedAt": "2026-08-18T13:52:48Z",
      "attemptedAction": "Keep a fixed SQL-level global Top20 independent of the supplied-SQL candidate/plan lookup even when an Agent requests a larger limit; derive deterministic target membership and a conservative cutoff share upper bound; retain only target rows, the bound, and a few global comparators in the Scenario handoff.",
      "evidenceUrl": "https://github.com/tidbcloud/aisupport/pull/995"
    }
  ]
}
-->

- Owner: Clinic Provider Executor
- Source: TCID-12986 human-versus-AI attribution review
- Implementation status: merged and deployed to staging
- Effectiveness status: optimized_verified

## Bounded problem

The ticket-directed candidate query and global comparison query shared one
limit. A requested Top20 therefore returned only ten global ranking rows. Even
when a target candidate was found, the compact result did not state whether the
target was in the real global Top20 or what its maximum aggregate contribution
could be if it was absent.

## Candidate change

- Rank the global comparison at SQL plus component granularity so all plans for
  one SQL are counted before assigning rank.
- Give the global query a fixed Top20 even when an Agent requests a larger
  limit. Run the bounded supplied-SQL candidate/plan lookup independently.
- When the supplied SQL is absent from the complete Top20, use the last ranked
  SQL's CPU time divided by observed Top20 CPU time as a conservative share
  upper bound.
- Keep the handoff at five rows. Carry the derived bound instead of rank-21 and
  deeper rows.

## Verification evidence

- PRs [#994](https://github.com/tidbcloud/aisupport/pull/994) and
  [#995](https://github.com/tidbcloud/aisupport/pull/995) merged. The staging
  image `6472edca899b10f144fc31b4dd7f59c57e61cfb5` became Ready at
  2026-08-18T13:37:31Z; the later Ready image
  `1cf8865c0c9227bb9477b259203301510a41d904` has that commit as its parent.
- TCID-12986 read replay `e03b724f-6bbe-4e41-8ddc-b85f61f99a38` used the
  frozen `2026-07-30T18:20:09+08:00` cutoff. Both scenario plans requested
  `limit=20`; both compact results reported `globalTopNLimit=20`,
  `globalTopNReturned=20`, and `globalTopNComplete=true`.
- SQL-level aggregation showed that `bc6171` was in the TiKV Top20 at
  8,433,616 CPU-ms and at most 4.3041% of the observed TiKV Top20 CPU. It was
  outside the TiDB Top20, where the deterministic cutoff bound was at most
  5,720,320 CPU-ms and 0.8405% of observed TiDB Top20 CPU. No rank-21 or deeper
  rows crossed the compact handoff.
- The corrected paired draft was written directly to the existing Jira
  internal comment `23357171` after verifying `public=false`. Readback kept
  `public=false`, kept the ticket comment count at 20, and contained the
  verified 4.30% contribution bound.

## Unresolved gaps

The bound applies to captured aggregate TopSQL CPU. It does not reject a rare,
wait-heavy Slow Query execution or compensate for TopSQL sampling gaps.

## Next validation action

Monitor a later Clinic case whose supplied SQL is outside the TiKV Top20 and
confirm that the cutoff upper bound remains sufficient without a tail query.