DROP … FROM PLANCACHE
On this page
Forces a plan to be optimized and generated from scratch the next time it is run.
DROP [<plan_id>] FROM PLANCACHE ON NODE <node_id>DROP PLAN FROM PLANCACHE [ON AGGREGATORS] FOR QUERY <query_text>
<plan_- ID of the query plan to remove from memory and the disk.
id> You can find the
DROP PLAN FROM PLANCACHEsyntax can be used to drop plans for specific queries.
<node_- the ID of the aggregator node to remove from memory and the disk.
id> You can find the
node_for an aggregator by running
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.
DROP 123 FROM PLANCACHE ON NODE 2;Query OK, 0 rows affected (0.00 sec)
DROP PLAN FROM PLANCACHE FOR QUERY SELECT * FROM testtable;
DROP PLAN FROM PLANCACHE ON AGGREGATORS FOR QUERY SELECT * FROM testtable;
A DBA wants to create some new indexes and performs the following actions (not necessarily in the specified order):
ANALYZE TABLEto 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.
ANALYZE TABLErequires a minimum threshold of changes in the table statistics to invalidate the existing plans for the table.
ANALYZE TABLEmay not invalidate the plans unless the minimum threshold is reached.
Hence, there is still a need to generate a new plan.
DROP FROM PLANCACHEcommand 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
The impact of recent index and stats changes are reflected in all the plans for all the queries, across the cluster.
The first time a query of a particular shape is run, it is compiled asynchronously for future invocations.
DROP FROM PLANCACHE command on all nodes (aggregators and leaves) across the cluster.
Last modified: May 2, 2023