MV_PLANCACHE

MV_PLANCACHE shows plancache information for all nodes in the cluster.

MV_PLANCACHE shows all plans that are currently in SingleStore’s in-memory plancache for all nodes in the cluster; it does not provide a permanent history. When a plan expires, it is removed from this view. Its commit and rollback counts are still included in the node-level counters reported by SHOW STATUS EXTENDED. A plan expires when any of the following conditions are met:

  • The plan remains unused for the duration specified by the plan_expiration_minutes engine variable

  • The node restarts

  • The plan is recompiled or reloaded, for example, after a table is altered

MV_PLANCACHE does not show plans held on disk.

Column Name

Description

NODE_ID

The ID of the node.

DATABASE_NAME

Context database selected with USE <database_name> when query was compiled.

QUERY_TEXT

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.

PLAN_ID

The unique ID of the query plan.

PLAN_TYPE

The type of query plan: interpreted or compiled.

Potential values: MBC, LLVM, COMPILE_LITE, or FAILURE. Refer to Code Generation for more information.

COMMITS

The number of successful executions of a given query.

ROLLBACKS

Number of unsuccessful executions of a given query, including cases where the query was aborted or encountered a runtime error.

Use this information to identify the statements contributing to the failed-query counter on a node, i.e., the Failed_read_queries and Failed_write_queries metrics reported by the SHOW STATUS EXTENDED command.

ROWCOUNT

The 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.

EXECUTION_TIME

The cumulative time, in milliseconds, spent executing a given query.

AVERAGE_EXEC_TIME

The average execution time (in milliseconds) of a given query plan.

LOGBUFFER_TIME

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.

LOGFLUSH_TIME

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.

ROWLOCK_TIME

NULL for SELECT queries, otherwise the cumulative time (in milliseconds) spent waiting to acquire exclusive row locks.

STREAMED_ROWS

Cumulative number of rows at the cluster level streamed from leaves and processed by a given SELECT query.

LEAFNETWORK_TIME

Cumulative time, in milliseconds, at the cluster level spent waiting for results from leaves. This includes the time spent executing queries on the leaves.

WORKLOAD_MANAGEMENT_QUEUED_TIME

The amount of time, in milliseconds, a given query spent queued by Workload Management.

RESOURCE_POOL_QUEUED_TIME

The amount of time, in milliseconds, a given query spent queued by Resource Governance.

MPL_PATH

The path to the plan files on disk.

QUEUED_TIME

The total amount of time, in milliseconds, a given query spends queued.

FIRST_LOAD_TIME

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

LAST_EXECUTED

The timestamp at which a given query was last executed.

AVERAGE_MEMORY_USE

The average amount of memory used (in bytes) to execute this query, over time. Any temporary memory allocation needed to execute a query (including those for hash tables, sorts, result tables, etc.) is tracked here.

PLAN_WARNINGS

Any warnings associated with a given query plan.

PLAN_INFO

Additional information about the query plan, in JSON format.

OPTIMIZER_NOTES

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

This value is only populated on aggregator nodes.

CPU_TIME

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

AVERAGE_MAX_MEMORY_USE

This is the maximum memory use (in bytes) of all of the operators used in each run, averaged across all runs of the same query plan, over time.

AVERAGE_DISK_SPILLING_USE

The average amount of data (in bytes) spilt to disk during query execution.

AVERAGE_BLOB_CACHE_MISS_BYTES

The average number (in bytes) that could not be found in the blob cache and had to be downloaded from the unlimited storage.

BLOB_CACHE_WAIT_TIME_MS

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.

ACTIVITY_NAME

The ID of the related activity.

PLAN_VARIABLES

The values of all engine variables that affect a given query plan.

Last modified:

Was this article helpful?