Sign inTry Free

Important

The SingleStore 9.1 release candidate (RC) gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 9.1.

SHOW PLANCACHE

On this page

Shows all query statements that SingleStore has compiled and executed, as well as cumulative query execution statistics associated with each plan.

Syntax

SHOW PLANCACHE

Remarks

  • SHOW PLANCACHE is a SingleStore extension.

  • All counters shown by SHOW PLANCACHE are cleared when SingleStore is restarted.

  • This command can be run on any SingleStore node (see Cluster Management Commands).

  • See the Permission Matrix for the required permission.

Output

Column

Description

Database

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

QueryText

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.

PlanID

ID of the plan. Please note, when executing the same query from different clients, two plans are generated with a different PlanID in the plancache.

IsProfile

Indicates whether the plan was compiled with profiling enabled.

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

  • 0: Profile statistics are not collected.

ProfileCodeGenMode

The profiling code generation mode for the plan. 

Possible 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).

ReoptimizeState

The current Feedback Reoptimization (FR) state of the plan.

Possible values:

  • 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).

ReoptimizeId

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 ReoptimizeId). This value correlates with FR details in OptimizerNotes for the same PlanId.

PlanType

Plan type, interpreted or compiled.

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

PlanSource

The source of the query plan. For example, LOCAL (local compilation) or DPC (Distributed Plan Cache).

Commits

Number of successful executions of the query.

Rollbacks

Number of unsuccessful executions of the query (i.e. if the query was aborted or it encountered runtime errors).

RowCount

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.

ExecutionTime

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.

AverageExecTime

Average plan execution time in milliseconds.

LogBufferTime

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.

LogFlushTime

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.

RowLockTime

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

StreamedRows

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

LeafNetworkTime

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

WorkloadManagementQueuedTime

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

ResourcePoolQueuedTime

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

MplPath

The path to the plan files on disk.

QueuedTime

Query queued time.

FirstLoadTime

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

LastExecuted

The timestamp at which a given query was last executed.

AverageMemoryUse

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.

PlanWarnings

Any warnings associated with the plan.

OptimizerNotes

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

CpuTime

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

AverageMaxMemoryUse

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

AverageDiskSpillingUse

The average amount of data, in bytes, spilled to disk during query execution.

AverageBlobCacheMissBytes

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

BlobCacheWaitTimeMillis

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.

The OptimizerNotes column may contain the following information:

  • table_row_counts: Table row counts and other statistics.

  • prospective_histograms: Queries in the plancache that could have made use of histograms. If there are no queries that could have used histograms, this key will not display.

  • is_single_partition: Value is true for a single partition; otherwise, false.

  • num_materialized_ctes: Number of materialized CTEs in the plan.

  • connections_per_leaf: Number of connections per leaf.

  • thread_per_leaf: Number of threads per leaf.

  • average_leaf_memory, average_runtime: Average memory usage by leaf and average query runtime statistics collected by Workload Management.

  • num_broadcasts, num_reshuffles: Number of broadcasts or reshuffles in the plan.

    Note

    SingleStore returns -1 for num_reshuffles and num_broadcasts when the workload manager is not managing (or tracking) the query based on the number of connections (or threads).

    For example:

    ---
"LogBufferTime": 0,
"LogFlushTime": 0,
"OptimizerNotes": "{\"num_broadcasts\": -1, \"num_reshuffles\": -1, \"is_single_partition\": false, \"average_leaf_memory\": 0, \"average_runtime\": 0}",
"PlanId": 57080,
"PlanType": "LLVM",
"PlanWarnings": "",
---

Example

SHOW PLANCACHE;
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------+--------------------+-----------------+--------------+----------+------------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+------------------------+---------------------------+-------------------------+
| Database           | QueryText                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | PlanId | IsProfile | ProfileCodeGenMode | ReoptimizeState | ReoptimizeID | PlanType | PlanSource | Commits | Rollbacks | RowCount | ExecutionTime | AverageExecTime | LogBufferTime | LogFlushTime | RowLockTime | StreamedRows | LeafNetworkTime | WorkloadManagementQueuedTime | ResourcePoolQueuedTime | MplPath                                                                                                                                                              | QueuedTime | FirstLoadTime       | LastExecuted        | AverageMemoryUse | PlanWarnings                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | OptimizerNotes                                                                                                                                                                                                                                                                                                                                                                                                                              | CpuTime | AverageMaxMemoryUse | AverageDiskSpillingUse | AverageBlobCacheMissBytes | BlobCacheWaitTimeMillis |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------+--------------------+-----------------+--------------+----------+------------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+------------------------+---------------------------+-------------------------+
| information_schema | SELECT @@memsql_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |      0 |         0 | OFF                | NONE            |            0 | LLVM     | LOCAL      |     126 |         0 |      126 |            60 |               0 |          NULL |         NULL |        NULL |            0 |               0 |                            0 |                      0 | /data/master/plancache/9b7/Select_dual_9b762a0c0e1e08e9c0230ff4340794d079208441ed295711cf51a45e5c4714a0_41f48257ff918236                                             |          0 | 2026-05-14 20:12:25 | 2026-05-14 20:14:27 |              454 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | {"join_memory_costs_estimates":{"rows_in_memory": 0, "bytes_in_memory": 0, "total_tables_saved_in_memory": 0}, "num_thread_per_leaf": 0, "num_connections_per_leaf": 0, "num_broadcasts": 0, "num_reshuffles": 0, "num_materialized_ctes": 0, "is_compiled_run": true, "reoptimize_misestimation_count": -1, "is_single_partition": true, "average_leaf_memory": 0, "average_runtime": 0, "used_pinned_plan": 0, "pinned_plan": 0}          |      26 |                1040 |                      0 |                         0 |                       0 |
| information_schema | SELECT @@memsql_version                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |      2 |         0 | OFF                | NONE            |            0 | LLVM     | LOCAL      |      20 |         0 |       20 |             2 |               0 |          NULL |         NULL |        NULL |            0 |               0 |                            0 |                      0 | /data/master/plancache/124/Select_dual_124cebcae224cf49eb164df1625f616108084ac54cfea17dd768a5e510b5652f_40393a6f6276d15b                                             |          0 | 2026-05-14 20:12:25 | 2026-05-14 20:14:26 |             5714 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | {"join_memory_costs_estimates":{"rows_in_memory": 0, "bytes_in_memory": 0, "total_tables_saved_in_memory": 0}, "num_thread_per_leaf": 0, "num_connections_per_leaf": 0, "num_broadcasts": 0, "num_reshuffles": 0, "num_materialized_ctes": 0, "is_compiled_run": true, "reoptimize_misestimation_count": -1, "is_single_partition": true, "average_leaf_memory": 0, "average_runtime": 0, "used_pinned_plan": 0, "pinned_plan": 0}          |       1 |                6553 |                      0 |                         0 |                       0 |
|                    | select @@version_comment limit 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |      9 |         1 | PARTIAL            | NONE            |            0 | LLVM     | LOCAL      |       1 |         0 |        1 |            14 |              14 |          NULL |         NULL |        NULL |            0 |               0 |                            0 |                      0 | /data/master/plancache/6d9/Select_profile_partial_dual_6d91d511c9276f5039cd124f4ba601c3a5d8acbfd9385230c45dd6b20fd3ddce_6205564fa208549f                             |          0 | 2026-05-14 20:14:16 | 2026-05-14 20:14:16 |           253007 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | {"join_memory_costs_estimates":{"rows_in_memory": 0, "bytes_in_memory": 0, "total_tables_saved_in_memory": 0}, "num_thread_per_leaf": 0, "num_connections_per_leaf": 0, "num_broadcasts": 0, "num_reshuffles": 0, "num_materialized_ctes": 0, "is_compiled_run": false, "reoptimize_misestimation_count": -1, "is_single_partition": true, "average_leaf_memory": 0, "average_runtime": 0, "used_pinned_plan": 0, "pinned_plan": 0}         |      11 |              393216 |                      0 |                         0 |                       0 |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------+--------------------+-----------------+--------------+----------+------------+---------+-----------+----------+---------------+-----------------+---------------+--------------+-------------+--------------+-----------------+------------------------------+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+------------------------+---------------------------+-------------------------+

Last modified:

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In