When a prepared statement is executed repeatedly with different parameter values, a database can either keep one parameter-independent plan or optimize afresh for each execution's values. Compare the two strategies, and describe how an engine can decide between them automatically.

## The underlying tension Plan reuse saves optimization CPU; value-specific planning buys plan quality. For a parameterized statement executed thousands of times per second, these pull in opposite directions, and the right choice depends entirely on how much the best plan varies across parameter values. ## Custom plans A custom plan is compiled for the specific values of one execution. The optimizer substitutes the actual values into the predicates before estimating, so it can consult histograms and most-common-value lists and derive a realistic row count for *this* call. - **Strength:** best possible plan per execution. Essential when selectivity varies by orders of magnitude across values, when the predicate involves ranges whose width varies, or when a value hits a most-common-value entry. - **Cost:** full optimization on every execution. For a three-way join this is milliseconds of CPU - trivial next to a two-minute report, ruinous next to a 0.2 ms primary-key lookup executed 20,000 times a second. - **Secondary cost:** compilation touches shared structures; heavy concurrent compiling produces latch/mutex contention that reduces throughput more than the raw CPU number suggests. ## Generic plans A generic plan treats each parameter as an unknown and estimates using aggregate statistics - for equality on a column with `n` distinct values, roughly `rows/n`; for an unbounded range, a fixed default fraction. - **Strength:** compiled once and reused, so per-execution overhead is essentially the cost of execution alone. It is also *stable*: latency does not depend on which value happened to compile the plan, so behaviour is predictable and capacity planning is easier. - **Weakness:** on a skewed column the average is a fiction that describes no real value. A generic plan may choose a full scan that is right for the one dominant value and wasteful for the thousands of rare ones, or vice versa. ## How an engine chooses automatically A well-known adaptive approach: for the first several executions of a prepared statement, always build custom plans and remember each one's *estimated* cost. After that sample, build a generic plan and compare its estimated cost against the average of the custom costs, adding the known optimization cost to the custom side of the ledger. If the generic plan is not meaningfully more expensive, adopt it permanently - the engine has evidence that value-specific planning is not buying anything. If it is clearly worse, keep planning per execution. The elegance is that the decision is made from measurements of the actual statement rather than from a global setting, and it is conservative: uniform data quickly converges to generic, while skewed data keeps paying for accuracy. Most engines also expose a manual override to force one mode, which is how you respond to an incident quickly while designing the real fix. Note what the heuristic cannot see: it compares *estimated* costs. If the statistics that produce those estimates are wrong, both sides of the comparison are wrong, and the engine may adopt a generic plan that its own cost model wrongly believes is fine. ## Relationship to parameter sniffing These are three distinct behaviours, and interviewers listen for the distinction: - **Sniffed-and-cached:** compile once *using the first values seen*, then reuse. The plan is value-specific but for someone else's value - the classic bimodal-latency failure. - **Generic:** compile once using *no* values. Nobody's plan is ideal, but nobody's plan is arbitrary either; the behaviour is uniform and predictable. - **Custom every time:** compile per execution with each call's values. Always appropriate, always paid for. A generic plan is therefore one of the standard *remedies* for a sniffing problem: it removes the dependence on which value happened to compile first, at the price of ceiling performance. ## Choosing in practice Favour **generic** when: the predicate columns are close to uniformly distributed; the statement runs at high frequency with short execution times; predictable latency matters more than peak speed; or optimization CPU is already a visible share of server load. Favour **custom** when: predicates hit skewed columns such as status flags, tenant IDs, or soft-delete markers; parameter values include ranges of hugely varying width; the statement is long-running so optimization time is noise; or the cost gap between the right and wrong plan is catastrophic rather than marginal. And note the third option that beats both: if two classes of executions genuinely deserve different plans, giving them different statement texts gives each its own cache entry and its own stable plan, with one compile each. That converts a runtime dilemma into a design decision - usually the answer worth proposing at senior level.