SingleStore Managed Service

information_schema.PLANCACHE

This table 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 table, 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.

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.

AVAERAGE_EXEC_TIME

The average execution time 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.

LAST_EXECUTED

The timestamp at which a given query was last executed.

AVERAGE_MEMORY_USE

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.

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 of each run, averaged across all runs of the same query plan.

ACTIVITY_NAME

The ID of the related activity.

PLAN_VARIABLES

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