Forces a plan to be optimized and generated from scratch the next time it is run.

Syntax

DROP [plan_id | ALL] FROM PLANCACHE

Remarks

• plan_id - ID of the query plan to remove from the memory and disk. You can find the plan_id by running SHOW PLANCACHE.
• ALL - Invalidates all previously compiled query plans, both in-memory and on-disk. You get a fresh compiled plan for any new query. Note that invalidated query plans are not deleted immediately. They remain in place until the query is recompiled, at which time invalid plans are marked for eventual deletion by the garbage collector.
• This command will only drop plans stored on the node where it is run.
• Dropping plan cache entries on an aggregator node will not drop the corresponding leaf-only plans from the leaf plan caches.
• Avoid running this command frequently since all queries are recompiled, resulting in temporary decrease in workload performance.

Example

DROP 123 FROM PLANCACHE;
Query OK, 0 rows affected (0.00 sec)

Use Cases

Example 1: DBA app tuning

A DBA wants to create some new indexes and performs the following actions (not necessarily in the specified order):

• Runs ANALYZE TABLE to invalidate all plans for the table based on the statistics that are significantly different from the current statistics, but does not invalidate all other plans. However, ANALYZE TABLE requires a minimum threshold of changes in the table statistics to invalidate the existing plans for the table. Therefore, ANALYZE TABLE may not invalidate the plans unless the minimum threshold is reached. Hence, there is still a need to generate a new plan.
• Runs the DROP ALL FROM PLANCACHE command to ensure that plans are generated with the latest statistics and all the previously compiled query plans are invalidated and purged, both in-memory and on-disk. This command updates the stats and changes the indexes. It can be run either before or after ANALYZE TABLE.

The impact of recent index and stats changes are reflected in all the plans for all the queries, across the cluster.

Example 2: Testing first-run performance

The first time a query of a particular shape is run, it is compiled asynchronously for future invocations. This speeds up query execution time for long and complex queries, while at the same time providing efficient query plans for later use. See Code Generation for more information on query compilation. To test compilation and measure the first run performance accurately, run the DROP ALL FROM PLANCACHE command on all nodes (aggregators and leaves) across the cluster.

Related Topics

• Managing Plancache Memory Usage
• Understanding the Plancache

Was this Article Helpful?

Thanks For Your Feedback!