{question}
When should a plan be removed from the plancache?
{question}
{answer}
A plan should be removed from the plancache in the following situations:
-
When you want to ensure query plans are generated with the most recent statistics.
-
When significant schema changes are made, such as adding new columns or indexes, plans are automatically invalidated.
-
When you run
ANALYZEbut the automatic invalidation threshold isn’t met. Dropping plans manually ensures that queries don’t use outdated statistics. -
When queries demonstrate poor performance due to misestimated statistics. Although running
ANALYZEcan help, plans are only dropped automatically if the underlying data has changed significantly (e.g., more than 50% of the rows added or inserted). -
When you want to test the performance of a first-run query by forcing the plan to recompile (helpful in debugging or benchmarking).
-
To free up memory by removing plans from the plancache, especially when you notice high memory usage from plan accumulation.
Note:
The plan_expiration_minutes engine variable controls how long a plan remains in the in-memory plancache before it expires, in case it is not reused. Once expired, the plan is removed from memory, but it is still retained on disk according to the disk_plan_expiration_minutes setting, allowing it to be reloaded without recompilation.
Related Reference:
Managing Plancache Memory and Disk Usage
{answer}