When a stored routine's body is executed repeatedly with different parameter values, how does the database handle execution plans for the statements inside it, and what problem can that cause?

## Why plans are cached inside routines Parsing, rewriting, and optimizing a nontrivial statement can cost more than executing it. A routine that runs thousands of times a minute cannot afford to replan every call, so every engine caches the plans for statements inside routine bodies and reuses them. The cache key is the routine and its statement, not the argument values — that is the whole point, and also the whole problem. A plan is chosen from *estimates*, and estimates depend on the *values* in the predicates. Cache the plan, and you have frozen the value assumption. ## How each engine frames it - **SQL Server — parameter sniffing.** On first compile, the optimizer "sniffs" the actual parameter values and builds a plan tailored to them. That plan stays cached for later calls with any values. - **Oracle — bind peeking**, with *adaptive cursor sharing* layered on: the engine notices when a bind-sensitive cursor performs badly for other values and starts maintaining several plans keyed by selectivity ranges. - **PostgreSQL — custom vs generic plans.** A prepared statement (and a plpgsql statement, which is prepared behind the scenes) is planned per-value for the first executions; if the generic plan's estimated cost is not worse than the average custom plan, it switches to a generic plan built without value knowledge. `plan_cache_mode` forces either behaviour. Different names, one phenomenon: reuse trades per-call optimality for compilation savings. ## The skew failure, concretely One table, one predicate: `WHERE tenant_id = ?`. Ninety-nine tenants have a few hundred rows each; one has ten million. Two sensible plans exist — an index seek with nested loops for the small tenants, a parallel scan with a hash join for the big one. Only one gets cached. If it compiles on a small tenant, the big tenant's call performs ten million index lookups with random I/O. If it compiles on the big tenant, every small call scans and builds a hash table it does not need, and grabs a large memory grant that starves concurrency. What makes it maddening operationally is the trigger: nothing in the code changes. A server restart, a plan-cache eviction, a failover, or an automatic statistics update invalidates the plan, the next call happens to arrive from the wrong end of the distribution, and the system is suddenly slow for everyone. Bisecting the deploy history finds nothing. ## Recognising it The fingerprint is *the same statement, wildly different runtimes correlated with parameter values, and a plan whose estimated rows differ hugely from actual rows for the current call*. If you can capture the cached plan and compare the compiled-for parameter values with the current ones, the diagnosis is immediate. "It got slow after a restart with no deploy" is the operational tell. ## Remedies **Recompile per execution.** Recompiling only the one problematic statement per call (rather than the whole routine) pays optimization cost each time and gets an optimal plan each time. Correct when the statement is expensive relative to planning and the skew is severe. **Force a generic / average plan.** Optimize for an average or explicitly chosen representative value. This deliberately sacrifices the best case to eliminate the disastrous case — usually the right call for latency-SLO workloads where predictability beats peak speed. **Split the routine.** If the body branches on an input flag and each branch wants a different shape, separate routines get separate cache entries. Similarly, routing whale tenants to a different routine than long-tail tenants gives each a plan that fits. **Restructure so skew stops mattering.** A covering index that makes both cases a seek, a partitioning scheme aligned with the skewed column, or splitting one query into two simpler ones can remove the dependence entirely. This is the most durable fix because it does not rely on planner hints. **Local variable indirection** (assign the parameter to a local variable before use) defeats sniffing in some engines by making the value invisible at compile time. It works, but it is obscure and it silently opts you into average-case estimation — document it or prefer an explicit hint. ## Two adjacent effects **Temporary tables inside routines** interact with caching: because a temp table is per-session and its statistics change per execution, engines may recompile statements that reference it, which both costs compilation and, sometimes, saves you from a stale plan. **Cache pollution** is the mirror image. Dynamic SQL built by string concatenation with literal values produces a distinct cache entry per value, filling the cache with single-use plans and evicting useful ones. Parameterize to get one entry — and then live with the sniffing tradeoff above. The two problems pull in opposite directions, and choosing between them is exactly the judgment this question is testing.