SHOW PLANCACHE
Shows all query statements that SingleStoreDB has compiled and executed, as well as cumulative query execution statistics associated with each plan.
Syntax
SHOW PLANCACHE
Remarks
SHOW PLANCACHE
is a SingleStoreDB extension (it doesn’t exist in MySQL).All counters shown by
SHOW PLANCACHE
are cleared when SingleStoreDB is restarted.This command can be run on any SingleStoreDB node (see Node Requirements for SingleStoreDB Commands).
See the Permission Matrix for the required permission.
Output
Column | Description |
---|---|
| Context database selected with |
| Query text with all numeric and string parameters replaced by tags (depending on parameter and/or query type). |
| ID of the plan. |
| Plan type, interpreted or compiled. |
| Number of successful executions of the query. |
| Number of unsuccessful executions of the query (i.e. if the query was aborted or it encountered runtime errors). |
| Cumulative number of rows returned by a |
| Cumulative time (in milliseconds) spent executing the query. |
| Average plan execution time. |
| NULL for |
| NULL for |
| NULL for |
| (Cluster Only) Cumulative number of rows streamed from leaves and processed by the |
| (Cluster Only) Cumulative time (in milliseconds) spent waiting for results from leaves. This includes the time spent executing queries on the leaves. |
| Query queued time. |
| Query last execution time. |
| The amount of CPU time, in milliseconds, spent executing the query. |
| 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. |
| Any warnings associated with the plan. |
| This is the maximum memory use of each run, averaged across all runs of the same query plan. |
| The amount of time, in milliseconds, a given query spent queued by Workload Management. |
| The amount of time, in milliseconds, a given query spent queued by Resource Governance. |
| The path to the plan files on disk. |
| The timestamp at which a query was first created or loaded from disk. |
| Information about the query plan used by the optimizer, in JSON format. |
| The average amount of data, in bytes, spilt to disk during query execution. |
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.num_broadcasts
,num_reshuffles
: Number of broadcasts or reshuffles in the plan.is_single_partition
: Value is true for a single partition; otherwise, false.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
SingleStoreDB returns
-1
fornum_reshuffles
andnum_broadcasts
when the workload manager 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
SHOW PLANCACHE; **** +--------------------+----------------------------------+--------+----------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------+---------------------+------------------+--------------+-------------------------------------------------------------------------------------------------------------------------+---------+---------------------+ | Database | QueryText | PlanId | PlanType | Commits | Rollbacks | RowCount | ExecutionTime | AverageExecTime | LogBufferTime | LogFlushTime | RowLockTime | StreamedRows | LeafNetworkTime | QueuedTime | LastExecuted | AverageMemoryUse | PlanWarnings | OptimizerNotes | CpuTime | AverageMaxMemoryUse | +--------------------+----------------------------------+--------+----------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------+---------------------+------------------+--------------+-------------------------------------------------------------------------------------------------------------------------+---------+---------------------+ | information_schema | SELECT @@memsql_id | 0 | LLVM | 4 | 0 | 4 | 347 | 86 | NULL | NULL | NULL | 0 | 0 | 0 | 2019-07-01 13:33:15 | 84237 | | {"num_broadcasts": 0, "num_reshuffles": 0, "is_single_partition": true, "average_leaf_memory": 0, "average_runtime": 0} | 9 | 131072 | | information_schema | SELECT @@memsql_version | 1 | LLVM | 1 | 0 | 1 | 157 | 157 | NULL | NULL | NULL | 0 | 0 | 0 | 2019-07-01 13:33:15 | 130681 | | {"num_broadcasts": 0, "num_reshuffles": 0, "is_single_partition": true, "average_leaf_memory": 0, "average_runtime": 0} | 13 | 131072 | | | select @@version_comment limit @ | 2 | LLVM | 1 | 0 | 1 | 558 | 558 | NULL | NULL | NULL | 0 | 0 | 0 | 2019-07-01 13:33:23 | 130987 | | {"num_broadcasts": 0, "num_reshuffles": 0, "is_single_partition": true, "average_leaf_memory": 0, "average_runtime": 0} | 9 | 131072 | +--------------------+----------------------------------+--------+----------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------+---------------------+------------------+--------------+-------------------------------------------------------------------------------------------------------------------------+---------+---------------------+