PLANCACHE

This view contains information about all query statements that SingleStore has compiled and executed, as well as cumulative query execution statistics associated with each plan.

It shows all plans that are currently in SingleStore’s in-memory plancache - plans are removed from this view, and statistics and counters are reset to zero, when the plan expires (after being unused for the setting of the engine variable plan_expiration_minutes), when the node restarts, or when the plan must be recompiled or reloaded (e.g. when the table is altered).

See also SHOW PLANCACHE, which displays a subset of this information.

Column Name	Description
`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. Please note, when executing the same query from different clients, two plans are generated with a different `PLAN_ID` in the plancache.
`PLAN_TYPE`	The type of query plan: interpreted or compiled.
`COMMITS`	The number of successful executions of a given query.
`ROLLBACKS`	Number of unsuccessful executions of a given query (i.e. cases where query was aborted or encountered runtime errors).
`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 (from the time the query arrived at the server to the time the result was sent to the client). This includes time spent waiting while queued. To find the working time of a query use: `execution_time - queued_time`.
`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. 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.
`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 each run, averaged across all runs of the same query plan.
`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.
`BLOB_CACHE_MISS_B`	A query sometimes cannot find its input data in blob cache, referred to as a cache miss. The blob cache will download the missing data from the unlimited storage. This new field tells us the number of bytes downloaded from the unlimited storage.

PLANCACHE

Was this article helpful?

Was this article helpful?