[Performance]: Bound memory amplification for join-heavy SQL and preserve pre-OOM attribution

## Is there an existing issue for performance?

- [x] I have checked the existing issues.

## Environment

- Version or commit-id: observed on a recent MatrixOne multi-CN development deployment; deployment identifiers are intentionally omitted
- Hardware parameters: a CN shared a memory-constrained node with another large CN workload
- OS type: Linux / Kubernetes
- Others: the evidence below is sanitized; no tenant, SQL literal, object name, endpoint, pod, or service identifier is included

## Details of Performance

### Problem statement

A join-heavy prepared statement can create an unbounded intermediate result and grow one session-scoped MPool until the node is out of memory. In the observed incident:

- total off-heap memory increased by about 10.3 GiB in the final minutes;
- the MPool category accounted for essentially all of that increase, while the memory cache remained stable;
- one session pool grew from 1 GiB to 10 GiB in roughly 140 seconds and represented about 91% of the global MPool at its last high-water crossing;
- the process was killed by node-level memory exhaustion before a terminal statement record was persisted;
- Go heap and the available custom allocator profile did not cover the growing MPool allocator, so they could not identify the allocation stack;
- repeated synchronous high-water logs caused temporary logger lock contention, which may have amplified latency and delayed cleanup but was not the allocation trigger.

The candidate SQL shape contains multiple joins, nested `DISTINCT`, a `UNION`, expression/cast-based join keys, and a final `DISTINCT`. The evidence strongly links execution of this shape to the MPool growth, but the killed execution has no final plan/runtime-statistics record. The exact cardinality error and dominant operator therefore still need a data-safe reproduction.

This should not be fixed by only raising a memory limit. MatrixOne needs a closed contract: the query either completes inside a coordinated resource envelope, spills, or terminates with an actionable error before it can take down the CN or node.

### Proposed work

#### 1. Reproduce and optimize the SQL/operator plan

- Build a synthetic, data-safe reproduction of the same operator shape, including skew and many-to-many join expansion.
- Capture estimated and actual row counts, live/peak memory, and spill bytes at every join, set, sort, and dedup boundary.
- Check statistics quality, join order, build/probe selection, and parallel exchange cardinality.
- Push filters, projections, and semantically safe deduplication before expanding joins.
- Avoid expression/string-cast join keys when normalized, typed, and indexable keys are available.
- Benchmark the optimized plan against the original for both ordinary and worst-case cardinalities; do not trade the OOM for a broad latency or multi-CN regression.

#### 2. Bound execution memory systemically

- Coordinate per-operator, per-query/session, process, container, and node budgets rather than configuring independent limits that can contradict one another.
- Add or verify spill support for the responsible join/dedup/set/sort operators.
- Make admission account for existing cache residency, concurrent queries, and node headroom.
- When safe execution is impossible, cancel with a stable resource-exhausted error before process or node OOM.
- Guarantee deterministic MPool and spill cleanup on success, error, cancellation, timeout, client disconnect, failed prepare/execute, and partial multi-CN failure.
- Detect runtime resize/configuration mismatches between the deployment limit, Go runtime limit, cache budget, and query-memory budget.

#### 3. Make the next incident directly attributable

Logs and durable statement history:

- persist a lightweight statement-start record before execution and finalize it as success, error, cancelled, or aborted-by-restart;
- link session pool, statement/query, pipeline, and operator identifiers without putting SQL text or tenant data in public logs;
- emit current bytes, high-water bytes, configured cap, admission decision, and spill state;
- make high-water logging structured, rate-limited, and non-blocking so a slow log sink cannot stall allocation or cleanup paths.

Metrics and profiles:

- expose low-cardinality gauges/counters/histograms for query-memory current/high-water distributions, admission rejection, spill, cancellation, and budget mismatch;
- do not use statement, session, tenant, or SQL as Prometheus labels; provide bounded diagnostic top-N data through logs or a debug endpoint instead;
- make MPool/SimpleCAllocator allocations visible in an in-use allocation profile, or explicitly report profiler coverage versus exported live bytes;
- trigger rate-limited pre-OOM capture from outside the hot path when RSS/off-heap crosses staged thresholds;
- persist heap, MPool allocator, goroutine, process/node memory, pod generation, and timestamp metadata outside the pod so evidence survives OOM.

### Acceptance criteria

- The synthetic join-heavy workload no longer causes container or node OOM.
- It either completes within the declared envelope, spills with bounded memory, or fails early with an actionable error while unrelated sessions remain available.
- Peak memory can be reconciled from operator/query MPool to process RSS and node headroom without double-counting.
- A killed or restarted execution retains a durable start and terminal/aborted state that can be correlated with its pool and operators.
- Normal-cardinality latency and throughput show no material regression; multi-CN execution remains correct and scalable.
- Tests cover empty/normal input, severe skew, many-to-many expansion, concurrent large queries, cancellation, timeout, disconnect, failed prepare/execute, restart, spill disk exhaustion/I/O failure, slow logging, dynamic resize, and partial multi-CN failure.

## Additional information

### Evidence and confidence

Confidence is **high** that session-scoped MPool growth from the unfinished prepared statement was the immediate trigger: independent node, process, off-heap, per-pool, statement-lifecycle, and goroutine evidence agree. Confidence is lower about the exact responsible operator because the process died before terminal runtime statistics were persisted and the available allocator profile did not include the growing allocator.

The implementation may be split into focused PRs (plan optimization, execution guardrails, and observability), but they should be validated against the same end-to-end resource contract and test matrix.