Sign inTry Free

SHOW PLANCACHE

On this page

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

Syntax

SHOW PLANCACHE

Remarks

  • SHOW PLANCACHE is a SingleStore Helios extension (it doesn’t exist in MySQL).

  • All counters shown by SHOW PLANCACHE are cleared when SingleStore Helios is restarted.

  • See the Permission Matrix for the required permission.

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. Please note, when executing the same query from different clients, two plans are generated with a different PlanID in the plancache.

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

(Workspace Only) Cumulative number of rows streamed from leaves and processed by the SELECT query.

LeafNetworkTime

(Workspace 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

The amount of CPU time, in milliseconds, spent executing the query.

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.

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.

FirstLoadTime

The timestamp at which a query was first created or loaded from disk.

OptimizerNotes

Information about the query plan used by the optimizer, in JSON format.

AverageDiskSpillingUse

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. 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 Helios 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 |
+--------------------+----------------------------------+--------+----------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------+---------------------+------------------+--------------+-------------------------------------------------------------------------------------------------------------------------+---------+---------------------+

Last modified: February 7, 2024

Was this article helpful?

On this page

Was this article helpful?