MV_PLANCACHE

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.

IS_PROFILE

Indicates whether the plan was compiled with profiling enabled.

• 1: Plan was compiled with a profiling codegen mode.

• 0: Profile statistics are not collected.

PROFILE_CODEGEN_MODE

Profile codegen mode used when compiling the plan. It can have the following values:

• OFF: No profiling.

• FULL: Full profiling for PROFILE queries and auto-profile LITE/FULL mode.

• PARTIAL: Lightweight profiling for SMART auto-profile mode (primarily for row-count statistics).

REOPTIMIZE_STATE

Specifies Feedback Reoptimization (FR) state for this plan. Typical values include:

• NONE: No FR activity. This is the initial state of the plan.

• MANUAL MARK TO BE REOPTIMIZED: Plan has been manually marked for FR (via REOPTIMIZE MARK) .

• AUTO MARK TO BE REOPTIMIZED: Plan has been automatically marked (by SingleStore) for FR.

• BEING REOPTIMIZED: An FR run is currently in progress for this plan.

• REOPTIMIZE FINISHED: FR completed and committed an optimized plan. This plan can be marked and FR'd again at a higher level.

• REOPTIMIZE CONVERGED: FR did not find a better plan; further FR attempts are suppressed until conditions change.

• COMPILED: An FR plan has been generated and is pending evaluation. The plan has not yet been executed.

• REOPTIMIZE BEFORE COMMIT: This plan is an FR-derived plan that has been reoptimized but the result has not yet been committed.

• OUT OF DATE REOPTIMIZED: This is an FR-derived plan from an earlier FR iteration; a newer FR iteration is already committed, making this plan stale.

• UNKNOWN: This is an FR-derived plan whose original plan can no longer be found in the plancache (likely already evicted).

REOPTIMIZE_ID

Specifies the Feedback Reoptimization (FR) iteration at which this plan was compiled. It can have the following values:

• 0: This plan was not generated by FR.

• > 0: If the value is greater than 0, this plan was generated by FR at this iteration (value of REOPTIMIZE_ID). This value correlates with FR details in OPTIMIZER_NOTES for the same PLAN_ID.

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.