Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

SHOW PLANCACHE

Shows all query statements that SingleStore DB has compiled and executed, as well as cumulative query execution statistics associated with each plan.

Syntax

SHOW PLANCACHE

Remarks

  • SHOW PLANCACHE is a SingleStore DB extension (it doesn’t exist in MySQL).
  • All counters shown by SHOW PLANCACHE are cleared when SingleStore DB is restarted.
  • This command can be run on any SingleStore node (see Node Requirements for SingleStore DB Commands).

Output

Column Description
Database Context database selected with USE <db_name> 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.
PlanType Plan type, interpreted or compiled.
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 Cumulative time (in milliseconds) spent executing the query.
AverageExecTime Average plan execution time.
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 (Cluster Only) Cumulative number of rows streamed from leaves and processed by the SELECT query.
LeafNetworkTime (Cluster Only) Cumulative time (in milliseconds) spent waiting for results from leaves. This includes the time spent executing queries on the leaves.
QueuedTime Query queued time.
LastExecuted Query last execution time.
CpuTime Time spent in query compilation.
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.
AverageMaxMemoryUse This is the maximum memory use of each run, averaged across all runs of the same query plan.

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.

    Info

    SingleStore DB returns -1 for num_reshuffles and num_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 |
+--------------------+----------------------------------+--------+----------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------+---------------------+------------------+--------------+-------------------------------------------------------------------------------------------------------------------------+---------+---------------------+