# Distributed Plancache > **📝 Note**: This is a Preview feature. The Distributed PlanCache (DPC) is a third layer of the plancache that supplements the [in-memory plancache and the on-disk persistent plancache (PPC)](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation/#UUID-0a745a7a-c630-4066-7532-d3ded280d626.md). The DPC allows cluster nodes to share compiled plans. Nodes can skip query optimization, [code generation](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation.md), and the LLVM compilation process if the plan has been compiled in a different node in the cluster. Thus, the load on the CPU is reduced and the first-time performance of queries that have been compiled on other nodes is improved. The DPC improves performance in the following scenarios: * **Fast scaling**: The DPC triggers plan synchronization during a node's reprovisioning phase. During reprovisioning, recently used query plans are downloaded to nodes’ PPCs. * **Clusters with multiple aggregator nodes**: Aggregators periodically sync the most recently used plans from other aggregators. ## Overview When the DPC is enabled, plans are synchronized automatically between nodes. That is, child aggregators and leaf nodes automatically download plans from the DPC into their local PPC. Once a plan is downloaded, the node can use that plan and avoid query optimization, code generation, and plan compilation in many cases. Similar to how the PPC functions, a plan downloaded to the DPC is usable except when: * A variable affecting the plan has changed in the node. * A table in a query has changed significantly (for example, the number of rows in the table has changed by a factor of two times or more) since the plan was generated. In addition to downloading plans from the DPC, nodes also upload plans to the DPC to make those plans available to other nodes. Similarly, plans can be deleted from the DPC to indicate to other nodes that those plans can be deleted. DPC operations do not interfere with regular cluster operations. While the DPC may consume CPU, memory, and network resources, all operations happen in the background and do not interfere with regular query execution. The DPC operates on a best-effort basis and does not guarantee that plan compilation will not occur on a new node. ## Remarks * DPC requires that the cluster have [Unlimited Storage](https://docs.singlestore.com/db/v9.1/manage-data/combining-unlimited-and-local-storage-databases.md) enabled. DPC utilizes unlimited storage to store plancache files. * DPC only stores plans generated during `MBC`, `LLVM`, and `INTERPRET_FIRST` interpreter modes. * Plan synchronization is not supported on the Master Aggregator. ## Enable and Manage the Distributed Plancache ## Enable the Distributed Plancache The `enable_distributed_plancache` engine variable controls the DPC. Set the `enable_distributed_plancache` global engine variable to `ON` to enable the DPC. ```sql SET GLOBAL enable_distributed_plancache = ON; ``` Use the following command to verify the DPC is enabled. ```sql SELECT @@enable_distributed_plancache; ``` ```output +--------------------------------+ | @@enable_distributed_plancache | +--------------------------------+ | 1 | +--------------------------------+ ``` ## Manage the Distributed Plancache When the DPC is enabled, nodes automatically download, upload, and delete plans from the DPC. Nodes download plans from the DPC to their local PPC when the cluster scales, is rebalanced, or a new node is added to the cluster. Nodes delete plans from the DPC when those plans are explicitly deleted from their local PPC with [DROP … FROM PLANCACHE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/drop-from-plancache.md). The download, upload, delete, and synchronization operations occur in the background and are managed by the DPC task queue. ## Aggregator Synchronization Child aggregators can be configured to automatically synchronize their local PPCs with the DPC. This process is called aggregator synchronization and is useful for clusters with multiple aggregators. To enable automatic aggregator synchronization, set `enable_periodic_distributed_plancache_agg_sync` to `ON`. ```sql SET GLOBAL enable_periodic_distributed_plancache_agg_sync = ON; ``` When `enable_periodic_distributed_plancache_agg_sync` is set to `ON`, at the interval specified by `distributed_plancache_agg_sync_s`, each aggregator performs an aggregator synchronization operation and downloads the most recently used `distributed_plancache_max_download_plans` query plans from the DPC. ## Asynchronous DPC Lookup With asynchronous DPC lookup, query execution looks for plans in the DPC during query optimization and Code Generation. Asynchronous lookup reduces query optimization and compilation time for complex queries and reduces the load on the CPU. This behavior is controlled by the session variable `enable_dpc_lookup`. ```sql SET SESSION enable_dpc_lookup = ON; ``` When there is a PPC miss and the following are true, a thread is launched to lookup and download the plan from the DPC: * `enable_dpc_lookup` is enabled. * `enable_synchronous_dpc_lookup` is not enabled. * The interpreter mode is set to `interpret_first`. Assuming the plan exists in the DPC, there are three different scenarios: **A: Download from the DPC completes before query optimization and code generation complete.** The plan from the DPC is loaded into the node's local PPC. The code generation and query optimization are cancelled, and query execution uses the LLVM plan downloaded from the DPC and loaded to the node’s local PPC. No local asynchronous compilation is launched. **B: Download from the DPC completes after query optimization and code generation complete, but before local asynchronous compilation is launched.** Query execution uses the locally-generated MBC plan for the initial execution. Once the execution is complete, the download thread replaces the PPC files with the plan from the DPC, and drops the original MBC plan from the plancache. Subsequent execution uses the LLVM plan downloaded from the DPC. No local asynchronous compilation is launched. **C: Download from the DPC completes after local asynchronous compilation has launched.** Local asynchronous compilation runs to completion and the node uses the locally compiled plan for future execution. The plan downloaded from the DPC is discarded and the newly compiled plan is asynchronously uploaded to the DPC. Complex queries (for example, large queries with many joins) whose plans are in the DPC often result in Scenario A. When asynchronous compilation is backed up, small to medium queries often result in Scenario B. In Scenarios A and B, local compilation is avoided, reducing the load on the CPU. The engine variable `max_dpc_lookup_threads` controls the maximum concurrent download threads for asynchronous DPC lookups. ## Synchronous DPC Lookup With synchronous DPC lookup, query execution looks to the DPC before starting query optimization, and [Code Generation](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation.md). When a plan exists in the DPC, this synchronous lookup process is typically faster than local compilation, optimization, and code generation for large, complex queries. In contrast to asynchronous lookup, synchronous lookup does not launch a separate thread for the DPC lookup; instead synchronous lookup waits until after the DPC lookup has completed before launching local compilation, optimization, and code generation. SingleStore recommends using asynchronous lookup for most scenarios as synchronous lookup may add latency. If it is known that a query will take a long time for optimization and code generation and it is expected that the query plan will be in the DPC, then synchronous lookup is the better option. Enable synchronous DPC lookup by setting `enable_synchronous_dpc_lookup` to ON on aggregator nodes. ```sql SET SESSION enable_synchronous_dpc_lookup = ON; ``` ## Manual Synchronization Synchronizations of a node's local PPC with the DPC can be manually initiated for a specific database with the [\_SYNC\_PLANS](https://docs.singlestore.com/#section-idm235015734691876.md) command. ## Engine Variables The following engine variables are used to manage the DPC: | Name | Description | | ------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------- | | `distributed_plancache_worker_threads` | Specifies the number of threads used to process tasks in the task queue for the DPC. | | `distributed_plancache_max_download_plans` | Specifies the maximum number of plans downloaded in a synchronization task for the DPC. | | `distributed_plancache_agg_sync_s` | Specifies the interval between aggregators' periodic synchronizations for the DPC. | | `enable_periodic_distributed_plancache_agg_sync` | Specifies if aggregators periodically synchronize their local PPC with the DPC. | | `enable_dpc_lookup` | A session variable that enables asynchronous DPC lookups. | | `enable_synchronous_dpc_lookup` | A session variable that specifies that nodes look for a plan in the DPC when a plan is not found in the node's local PPC. | | `max_dpc_lookup_threads` | Controls the maximum concurrent download threads for asynchronous DPC lookups. | Refer to [List of Engine Variables](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables.md) for default values. ## Clear the DPC Queue ```sql CLEAR DISTRIBUTED_PLANCACHE QUEUE; ``` ## Synchronize the DPC Manually with `_SYNC_PLANS` To synchronize a node's local PPC with the DPC for a specific database, run the following command. ```sql _SYNC_PLANS ; ``` This command will download up to `distributed_plancache_max_download_plans` from the DPC to the node's local PPC for the specified database. The [\_SYNC\_PLANS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/code-generation-functions/sync-plans.md) command first downloads one plan per table in the database. For databases with large numbers of tables, SingleStore recommends increasing the value of `distributed_plancache_max_download_plans` to a value greater than the number of tables in the database before running the command. When this command is run, a synchronization task will be queued regardless of whether `enable_distributed_plancache` is set to `ON` or `OFF`. If the request is enqueued while `enable_distributed_plancache` is set to `OFF`, the task will run when `enable_distributed_plancache` is set to `ON`. ## Observe Distributed Plancache Statistics Connect to a node and use the [SHOW DISTRIBUTED\_PLANCACHE STATUS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-distributed-plancache-status.md) command to observe statistics about the DPC on a specific node, as follows. ```sql SHOW DISTRIBUTED_PLANCACHE STATUS; ``` ```output +-------------------------------------------------------+-------+ | Stat | Value | +-------------------------------------------------------+-------+ | Successful Downloads Since Startup | 128 | | Skipped Downloads Since Startup | 110 | | Failed Downloads Since Startup | 0 | | Plans Uploaded Since Startup | 2 | | Plans Deleted Since Startup | 0 | | DB Synchronization Since Startup | 1 | | Successful Downloads From Periodic Sync Since Startup | 12 | | Skipped Downloads From Periodic Sync Since Startup | 107 | | Failed Downloads From Periodic Sync Since Startup | 0 | | Periodic Query Plan Syncs Since Startup | 1 | | Distributed Plancache Plans Used Since Startup | 90 | | Currently Queued Populate Download Tasks | 0 | | Currently Queued Download Tasks | 0 | | Currently Queued Upload Tasks | 0 | | Currently Queued Delete Tasks | 0 | | Avg Plan Download Latency (ms) | 13 | | Avg Plan Upload Latency (ms) | 38 | | Avg Plan Delete Latency (ms) | 0 | | Avg Duration For DB Plan Synchronization (ms) | 1710 | +-------------------------------------------------------+-------+ ``` **Note**: Failed Downloads indicate that the node tried to download a plan file from the DPC, but failed to do so typically because the plan did not exist in the DPC. Skipped Downloads indicate that the node tried to download the plan from the DPC, but the plan already existed in the local PPC. Use the following commands to view the number of tasks in the DPC task queue. ```sql SHOW STATUS LIKE 'Queued_DPC_Uploads'; SHOW STATUS LIKE 'Queued_DPC_Downloads'; SHOW STATUS LIKE 'Queued_DPC_PopulateDownloads'; SHOW STATUS LIKE 'Queued_DPC_Deletes'; ``` If the values of these metrics increase significantly and the number of compilations rises due to slow plan synchronization, SingleStore recommends considering an increase in the DPC worker thread pool size (`distributed_plancache_worker_threads`). ## Plancache Manager The Plancache Manager (PCM) is an external service that stores metadata about plans stored in a cluster's plancache. During fast scale and suspend and resume operations, the cluster retrieves plan metadata from the PCM. Plans marked as stored in the DPC are retrieved from the DPC. These plans do not need to be re-compiled, allowing the cluster to be automatically warmed up with most recently used ("hot") plans after fast scale and suspend and resume operations. A cluster with DPC enabled automatically uses the PCM. ## Examples ## Example 1: Improve Performance after Scaling and Cold-Starts The DPC can improve performance for scaling and cold starts, which is useful when scaling or adding aggregator nodes occurs regularly as a part of normal operations. Run the following command to enable the DPC. ```sql SET GLOBAL enable_distributed_plancache = ON; ``` With `enable_distributed_plancache` set to `ON`, when a database is scaled or aggregator nodes are added, plans for all databases relevant to the new node(s) are automatically synchronized. This synchronization process downloads `distributed_plancache_max_download_plans`. For databases with a large number of tables, SingleStore recommends setting `distributed_plancache_max_download_plans` to a value greater than the number of tables in the database before synchronizing. Run the following queries to track the status of synchronization. ```sql SHOW DISTRIBUTED_PLANCACHE STATUS; SHOW STATUS LIKE 'Queued_DPC_PopulateDownloads'; SHOW STATUS LIKE 'Queued_DPC_Downloads'; ``` When the values of both `Queued_DPC_PopulateDownloads` and `Queued_DPC_Downloads` are zero, all download tasks from the DPC have finished. ## Example 2: Many Child Aggregators The DPC can improve first-time query performance when there are many child aggregators and the query shapes are heterogeneous. To avoid aggregator nodes repeatedly compiling the same plan, run the following on all aggregator nodes: ```sql SET GLOBAL enable_periodic_distributed_plancache_agg_sync = ON; SET GLOBAL distributed_plancache_agg_sync_s = ; ``` The value \ must be at least 30 seconds. For clusters with many aggregators, SingleStore recommends setting this value between 60 seconds and 10 minutes. These settings ensure that aggregator nodes periodically download plans from the DPC that they do not have in their local PPC. Another option for improving performance in clusters with many child aggregators is to turn on the synchronous DPC lookup using the following command. ```sql SET SESSION enable_synchronous_dpc_lookup = ON; ``` When the `enable_synchronous_dpc_lookup` engine variable is set to `ON` and a node detects that a query plan is not in its local PPC, the node will look for a plan in the DPC before compiling the query plan. Consider a query being run on many aggregators. The first aggregator to receive the query looks (and misses) in its local PPC and the DPC; the node then compiles the plan and uploads it to the DPC. When the query is run on other nodes, those nodes miss in their local PPC, but find the plan in the DPC and use that plan to avoid compilation. The `enable_synchronous_dpc_lookup` engine variable is a [session variable](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables.md) and enables this lookup only for the current session. ## Example 3: Database with a Large Number of Query Plans The DPC can improve performance when a specific database has a disproportionately large number of query plans compared to the other databases. To download plans from the DPC to the PPC for a specific node, run the following commands on that node. ```sql SET GLOBAL distributed_plancache_max_download_plans = ; _SYNC_PLANS ; ``` To download all query plans for the database, set the value `` to the number of tables in the database plus the number of plans in the database. *** Modified at: May 29, 2026 Source: [/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/distributed-plancache/](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/distributed-plancache/) (An index of the documentation is available at /llms.txt)