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.
`ACTIVITY_NAME`	The `ID` of the related activity.
`PLAN_VARIABLES`	The values of all engine variables that affect a given query plan.

MV_PLANCACHE

Was this article helpful?

Was this article helpful?