# SHOW PLANCACHE Shows all query statements that SingleStore has compiled and executed, as well as cumulative query execution statistics associated with each plan. ## Syntax ``` SHOW PLANCACHE ``` ## Remarks * `SHOW PLANCACHE` is a SingleStore extension. * All counters shown by `SHOW PLANCACHE` are cleared when SingleStore is restarted. * This command can be run on any SingleStore node (see [Cluster Management Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md)). * See the [Permission Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permission. **Output** | Column | Description | | ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `Database` | Context database selected with`USE `when query was compiled. | | `QueryText` | Query text with all numeric and string parameters replaced by tags (depending on parameter and/or query type).`@`is used for integers and`^`for string parameters.`?`is always used for INSERT queries. | | `PlanID` | ID of the plan. Please note, when executing the same query from different clients, two plans are generated with a different`PlanID`in the plancache. | | `IsProfile` | Indicates whether the plan was compiled with profiling enabled. | | `ProfileCodeGenMode` | The profiling code generation mode for the plan.Possible values: | | `ReoptimizeState` | The current Feedback Reoptimization (FR) state of the plan.Possible values: | | `ReoptimizeId` | Specifies the Feedback Reoptimization (FR) iteration at which this plan was compiled. It can have the following values: | | `PlanType` | Plan type, interpreted or compiled.Potential values:`MBC`,`LLVM`,`COMPILE_LITE`, or`FAILURE`. Refer to[Code Generation](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation.md)for more information. | | `PlanSource` | The source of the query plan. For example,`LOCAL`(local compilation) or`DPC`(Distributed Plan Cache). | | `Commits` | Number of successful executions of the query. | | `Rollbacks` | Number of unsuccessful executions of the query (i.e. if the query was aborted or it encountered runtime errors). | | `RowCount` | Cumulative number of rows returned by a`SELECT`query or the cumulative number of rows inserted, updated, or deleted for an`INSERT`,`UPDATE`, or`DELETE`query. | | `ExecutionTime` | The cumulative time, in milliseconds, spent executing a given query (from the time the query arrived at the server to the time the result was sent to the client). This includes time spent waiting while queued. To find the working time of a query use:`execution_time - queued_time`. | | `AverageExecTime` | Average plan execution time in milliseconds. | | `LogBufferTime` | NULL for`SELECT`queries, otherwise the cumulative time (in milliseconds) spent waiting to reserve space in the transaction buffer for this query. A larger transaction buffer and faster disk can help reduce it. | | `LogFlushTime` | NULL for`SELECT`queries, otherwise the cumulative time (in milliseconds) spent waiting until changes made by this query are flushed to disk. A faster disk can help reduce it. | | `RowLockTime` | NULL for`SELECT`queries, otherwise the cumulative time (in milliseconds) spent waiting to acquire exclusive row locks. | | `StreamedRows` | Cumulative number of rows at theclusterlevel streamed from leaves and processed by a given`SELECT`query. | | `LeafNetworkTime` | Cumulative time, in milliseconds, at theclusterlevel spent waiting for results from leaves. This includes the time spent executing queries on the leaves. | | `WorkloadManagementQueuedTime` | The amount of time, in milliseconds, a given query spent queued by Workload Management. | | `ResourcePoolQueuedTime` | The amount of time, in milliseconds, a given query spent queued by Resource Governance. | | `MplPath` | The path to the plan files on disk. | | `QueuedTime` | Query queued time. | | `FirstLoadTime` | The timestamp at which a query was first created or loaded from disk. | | `LastExecuted` | The timestamp at which a given query was last executed. | | `AverageMemoryUse` | The average amount of memory used to execute this query. Any temporary memory allocation needed to execute a query including those for hash tables, sorts, result tables, etc. is tracked here. | | `PlanWarnings` | Any warnings associated with the plan. | | `OptimizerNotes` | Information about the query plan used by the optimizer, in JSON format. | | `CpuTime` | The amount of CPU time, in milliseconds, spent executing the query. | | `AverageMaxMemoryUse` | This is the maximum memory use (in bytes) of each run, averaged across all runs of the same query plan. | | `AverageDiskSpillingUse` | The average amount of data, in bytes, spilled to disk during query execution. | | `AverageBlobCacheMissBytes` | The average number (in bytes) that could not be found in the blob cache and had to be downloaded from the unlimited storage | | `BlobCacheWaitTimeMillis` | The time (milliseconds) that were spent on waiting for blob cache data. This is a sum of waiting time from all query execution threads, not to be compared with end-to-end running time. | The `OptimizerNotes` column may contain the following information: * `table_row_counts`: Table row counts and other statistics. * `prospective_histograms`: Queries in the plancache that could have made use of histograms. If there are no queries that could have used histograms, this key will not display. * `is_single_partition`: Value is **true** for a single partition; otherwise, **false**. * `num_materialized_ctes`: Number of materialized CTEs in the plan. * `connections_per_leaf`: Number of connections per leaf. * `thread_per_leaf`: Number of threads per leaf. * `average_leaf_memory`, `average_runtime`: Average memory usage by leaf and average query runtime statistics collected by Workload Management. * `num_broadcasts`, `num_reshuffles`: Number of broadcasts or reshuffles in the plan. > **📝 Note**: SingleStore returns `-1` for `num_reshuffles` and `num_broadcasts` when the [workload manager](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/use-the-workload-manager.md) is not managing (or tracking) the query based on the number of connections (or threads).For example:``` > > --- > "LogBufferTime": 0, > "LogFlushTime": 0, > "OptimizerNotes": "{\"num_broadcasts\": -1, \"num_reshuffles\": -1, \"is_single_partition\": false, \"average_leaf_memory\": 0, \"average_runtime\": 0}", > "PlanId": 57080, > "PlanType": "LLVM", > "PlanWarnings": "", > --- > > ``` ## Example ```sql SHOW PLANCACHE; ``` ```output +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------+--------------------+-----------------+--------------+----------+------------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+------------------------+---------------------------+-------------------------+ | Database | QueryText | PlanId | IsProfile | ProfileCodeGenMode | ReoptimizeState | ReoptimizeID | PlanType | PlanSource | Commits | Rollbacks | RowCount | ExecutionTime | AverageExecTime | LogBufferTime | LogFlushTime | RowLockTime | StreamedRows | LeafNetworkTime | WorkloadManagementQueuedTime | ResourcePoolQueuedTime | MplPath | QueuedTime | FirstLoadTime | LastExecuted | AverageMemoryUse | PlanWarnings | OptimizerNotes | CpuTime | AverageMaxMemoryUse | AverageDiskSpillingUse | AverageBlobCacheMissBytes | BlobCacheWaitTimeMillis | +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------+--------------------+-----------------+--------------+----------+------------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+------------------------+---------------------------+-------------------------+ | information_schema | SELECT @@memsql_id | 0 | 0 | OFF | NONE | 0 | LLVM | LOCAL | 126 | 0 | 126 | 60 | 0 | NULL | NULL | NULL | 0 | 0 | 0 | 0 | /data/master/plancache/9b7/Select_dual_9b762a0c0e1e08e9c0230ff4340794d079208441ed295711cf51a45e5c4714a0_41f48257ff918236 | 0 | 2026-05-14 20:12:25 | 2026-05-14 20:14:27 | 454 | | {"join_memory_costs_estimates":{"rows_in_memory": 0, "bytes_in_memory": 0, "total_tables_saved_in_memory": 0}, "num_thread_per_leaf": 0, "num_connections_per_leaf": 0, "num_broadcasts": 0, "num_reshuffles": 0, "num_materialized_ctes": 0, "is_compiled_run": true, "reoptimize_misestimation_count": -1, "is_single_partition": true, "average_leaf_memory": 0, "average_runtime": 0, "used_pinned_plan": 0, "pinned_plan": 0} | 26 | 1040 | 0 | 0 | 0 | | information_schema | SELECT @@memsql_version | 2 | 0 | OFF | NONE | 0 | LLVM | LOCAL | 20 | 0 | 20 | 2 | 0 | NULL | NULL | NULL | 0 | 0 | 0 | 0 | /data/master/plancache/124/Select_dual_124cebcae224cf49eb164df1625f616108084ac54cfea17dd768a5e510b5652f_40393a6f6276d15b | 0 | 2026-05-14 20:12:25 | 2026-05-14 20:14:26 | 5714 | | {"join_memory_costs_estimates":{"rows_in_memory": 0, "bytes_in_memory": 0, "total_tables_saved_in_memory": 0}, "num_thread_per_leaf": 0, "num_connections_per_leaf": 0, "num_broadcasts": 0, "num_reshuffles": 0, "num_materialized_ctes": 0, "is_compiled_run": true, "reoptimize_misestimation_count": -1, "is_single_partition": true, "average_leaf_memory": 0, "average_runtime": 0, "used_pinned_plan": 0, "pinned_plan": 0} | 1 | 6553 | 0 | 0 | 0 | | | select @@version_comment limit 1 | 9 | 1 | PARTIAL | NONE | 0 | LLVM | LOCAL | 1 | 0 | 1 | 14 | 14 | NULL | NULL | NULL | 0 | 0 | 0 | 0 | /data/master/plancache/6d9/Select_profile_partial_dual_6d91d511c9276f5039cd124f4ba601c3a5d8acbfd9385230c45dd6b20fd3ddce_6205564fa208549f | 0 | 2026-05-14 20:14:16 | 2026-05-14 20:14:16 | 253007 | | {"join_memory_costs_estimates":{"rows_in_memory": 0, "bytes_in_memory": 0, "total_tables_saved_in_memory": 0}, "num_thread_per_leaf": 0, "num_connections_per_leaf": 0, "num_broadcasts": 0, "num_reshuffles": 0, "num_materialized_ctes": 0, "is_compiled_run": false, "reoptimize_misestimation_count": -1, "is_single_partition": true, "average_leaf_memory": 0, "average_runtime": 0, "used_pinned_plan": 0, "pinned_plan": 0} | 11 | 393216 | 0 | 0 | 0 | +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------+--------------------+-----------------+--------------+----------+------------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+------------------------+---------------------------+-------------------------+ ``` *** Modified at: May 15, 2026 Source: [/db/v9.1/reference/sql-reference/show-commands/show-plancache/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-plancache/) (An index of the documentation is available at /llms.txt)