> For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt # $planCacheStats ## Definition Returns [plan cache](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-query-plans-query-optimization) information for a collection. The stage returns a document for each plan cache entry. The `$planCacheStats` stage must be the first stage in the pipeline. The stage has the following syntax: ```javascript { $planCacheStats: { allHosts: } } ``` The `$planCacheStats` aggregation stage has the following options: | Option | Description | | --- | --- | | `allHosts` | Configures how the `$planCacheStats` aggregation stage targets nodes in a sharded cluster. If `true`, [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos.md#std-program-mongos) broadcasts the `$planCacheStats` aggregation stage to all nodes (primary and secondaries) for each affected shard that contains one or more chunks from the target collection.; If `false`, the `$planCacheStats` aggregation stage follows the [Read Preference](https://www.mongodb.com/docs/manual/core/read-preference.md#std-label-read-preference) and only retrieves the plan cache from the targeted replica set primary. Replica sets and standalone servers return an error during pipeline parsing if `allHosts` is set to `true`. The option is only available to sharded clusters. Default: `false` **New in version 7.1** | **See also:** [Query Plans](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-query-plans-query-optimization) ## Considerations ### Pipeline `$planCacheStats` must be the first stage in an aggregation pipeline. ### Restrictions - `$planCacheStats` is not allowed in: - [transactions](https://www.mongodb.com/docs/manual/core/transactions.md#std-label-transactions) - [`$facet`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/facet.md#mongodb-pipeline-pipe.-facet) aggregation stage - `$planCacheStats` requires read concern level [`"local"`.](https://www.mongodb.com/docs/manual/reference/read-concern-local.md#mongodb-readconcern-readconcern.-local-) ### Access Control On systems running with [`authorization`](https://www.mongodb.com/docs/manual/reference/configuration-options.md#mongodb-setting-security.authorization), the user must have the [`planCacheRead`](https://www.mongodb.com/docs/manual/reference/privilege-actions.md#mongodb-authaction-planCacheRead) privilege for the collection. ### Redaction When using [Queryable Encryption](https://www.mongodb.com/docs/manual/core/queryable-encryption.md#std-label-qe-manual-feature-qe), the `$planCacheStats` stage omits operations against encrypted collections, even though the operations are cached as normal. ### Read Preference When the `allHosts` option is set to `false`, [`$planCacheStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/plancachestats.md#mongodb-pipeline-pipe.-planCacheStats) follows the [read preference](https://www.mongodb.com/docs/manual/core/read-preference.md#std-label-read-preference) in selecting the host(s) from which to return the plan cache information. Applications may target different members of a replica set. As such, each replica set member might receive different read commands and have plan cache information that differs from other members. Nevertheless, running `$planCacheStats` on a replica set or a sharded cluster obeys the normal read preference rules. That is, on a replica set, the operation gathers plan cache information from just one member of replica set, and on a sharded cluster, the operation gathers plan cache information from just one member of each shard replica set. ## Output **Changed in version 8.3** The output of `$planCacheStats` depends on the query engine used to complete the query. The value of the `version` field of the `$planCacheStats` indicates which query engine was used: - `1` indicates that the classic engine was used. - `2` indicates that the [slot-based query execution engine](https://www.mongodb.com/docs/manual/reference/sbe.md#std-label-sbe-landing) was used. ### Classic Execution Engine For queries that use the classic execution engine, `$planCacheStats` returns a document similar to the following: ```none { "version" : 1, "createdFromQuery" : , "planCacheShapeHash" : , "planCacheKey" : , "isActive" : , "works" : , "cachedPlan" : { "stage" : , "filter" : , "inputStage" : { "stage" : , ... } }, "timeOfCreation" : , "creationExecStats" : [ // Exec Stats Document for each candidate plan { "nReturned" : , "executionTimeMillisEstimate" : , "peakTrackedMemBytes" : , // Available starting in MongoDB 8.3 "totalKeysExamined" : , "totalDocsExamined" :, "executionStages" : { "stage" : , ... "inputStage" : { "stage" : , ... } } }, ... ], "candidatePlanScores" : [ , ... ], "indexFilterSet" : , "estimatedSizeBytes" : , "querySettings" : , "host" : , "shard" : } ``` Each document includes various query plan and execution stats, including: | Field | Description | | --- | --- | | `version` | A number that indicates the query engine used to complete the query. `1` indicates that the classic engine was used.; `2` indicates that the [slot-based query execution engine](https://www.mongodb.com/docs/manual/reference/sbe.md#std-label-sbe-landing) was used. | | `createdFromQuery` | A document that contains the specific query that resulted in this cache entry. For example: `{ "query" : , "sort" : , "projection" : }` | | `isActive` | A boolean that indicates whether the entry is active or inactive. If active, the query planner is currently using the entry to generate query plans.; If inactive, the query planner is not currently using the entry to generate query plans. See [Plan Cache Entry State.](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-cache-entry-state) | | `planCacheShapeHash` | A hexadecimal string that represents the hash of the [query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-shape). See [`explain.queryPlanner.planCacheShapeHash`.](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.queryPlanner.planCacheShapeHash) Starting in MongoDB 8.0, the existing `queryHash` field is duplicated in a new field named `planCacheShapeHash`. If you're using an earlier MongoDB version, you'll only see the `queryHash` field. Future MongoDB versions will remove the deprecated `queryHash` field, and you'll need to use the `planCacheShapeHash` field instead. | | `planCacheKey` | A hexadecimal string that represents the hash of the key used to find the plan cache entry associated with this query. The plan cache key is a function of both the [plan cache query shape](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) and the currently available indexes for that shape. See [`explain.queryPlanner.planCacheKey`.](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.queryPlanner.planCacheKey) | | `cachedPlan` | The details of the cached plan. The fields included in the `cachedPlan` vary based on whether the query was completed using the classic engine or the slot-based query execution engine. For more information on query plans, see [`explain.queryPlanner`.](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.queryPlanner) | | `works` | The number of "work units" performed by the query execution plan during the trial period when the query planner evaluates candidate plans. For more information, see [`explain.executionStats.executionStages.works`.](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.executionStages.works) | | `timeOfCreation` | Time of creation for the entry. | | `creationExecStats` | An array of execution stats documents. The array contains a document for each candidate plan. For details on the execution stats, see [`explain.executionStats`.](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats) | | `candidatePlanScores` | An array of scores for the candidate plans listed in the `creationExecStats` array. | | `indexFilterSet` | A boolean that indicates whether an [index filter](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-index-filters) exists for the [plan cache query shape.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) | | `estimatedSizeBytes` | The estimated size in bytes of a plan cache entry. | | `querySettings` | **New in version 8.0** A document that contains query settings previously set using [`setQuerySettings`:](https://www.mongodb.com/docs/manual/reference/command/setquerysettings.md#mongodb-dbcommand-dbcmd.setQuerySettings) `querySettings: { indexHints: [ { ns: { db: , coll: }, allowedIndexes: }, ... ], queryFramework: }` `querySettings` fields: `indexHints.ns` (document): Namespace for index hints.`db` (string): Name of the database for index hints.; `coll` (string): Name of the collection for index hints.; `indexHints.allowedIndexes` (array): Array of indexes for index hints. For more details, see [Indexes](https://www.mongodb.com/docs/manual/indexes.md#std-label-indexes) and [`hint()`.](https://www.mongodb.com/docs/manual/reference/method/cursor.hint.md#mongodb-method-cursor.hint); `queryFramework` (string): [Query framework](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-framework) string can be:`classic` for the classic engine.; `sbe` for slot-based query execution engine. For details, see [Slot-Based Query Execution Engine.](https://www.mongodb.com/docs/manual/reference/sbe.md#std-label-sbe-landing) | | `host` | The hostname and port of the [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) instance from which the plan cache information was returned. When run on a sharded cluster, the operation returns plan cache entry information from a single member in each shard replica set. This member is identified with the [shard](https://www.mongodb.com/docs/manual/reference/operator/aggregation/plancachestats.md#std-label-plancachestats-shard) and [host](https://www.mongodb.com/docs/manual/reference/operator/aggregation/plancachestats.md#std-label-plancachestats-host) fields. See also [Redaction.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/plancachestats.md#std-label-plancachestats-read-pref) | | `shard` | The name of the shard from which [`$planCacheStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/plancachestats.md#mongodb-pipeline-pipe.-planCacheStats) retrieved the cache entry. Only available if run on a sharded cluster. | ## Examples ### MongoDB Shell The examples in this section use the following `orders` collection: ```javascript db.orders.insertMany( [ { "_id" : 1, "item" : "abc", "price" : Decimal128("12"), "quantity" : 2, "type": "apparel" }, { "_id" : 2, "item" : "jkl", "price" : Decimal128("20"), "quantity" : 1, "type": "electronics" }, { "_id" : 3, "item" : "abc", "price" : Decimal128("10"), "quantity" : 5, "type": "apparel" }, { "_id" : 4, "item" : "abc", "price" : Decimal128("8"), "quantity" : 10, "type": "apparel" }, { "_id" : 5, "item" : "jkl", "price" : Decimal128("15"), "quantity" : 15, "type": "electronics" } ] ) ``` Create the following indexes on the collection: ```javascript db.orders.createIndex( { item: 1 } ); db.orders.createIndex( { item: 1, quantity: 1 } ); db.orders.createIndex( { quantity: 1 } ); db.orders.createIndex( { quantity: 1, type: 1 } ); db.orders.createIndex( { item: 1, price: 1 }, { partialFilterExpression: { price: { $gte: Decimal128("10")} } } ); ``` **Note:** Index `{ item: 1, price: 1 }` is a [partial index](https://www.mongodb.com/docs/manual/core/index-partial.md#std-label-index-type-partial) and only indexes documents with `price` field greater than or equal to `Decimal128("10")`. Run some queries against the collection: ```javascript db.orders.find( { item: "abc", price: { $gte: Decimal128("10") } } ) db.orders.find( { item: "abc", price: { $gte: Decimal128("5") } } ) db.orders.find( { quantity: { $gte: 20 } } ) db.orders.find( { quantity: { $gte: 5 }, type: "apparel" } ) ``` The preceding queries are completed using the slot-based query execution engine. ### Return Information for All Entries in the Query Cache The following aggregation pipeline uses `$planCacheStats` to return information about the plan cache entries for the collection: ```javascript db.orders.aggregate( [ { $planCacheStats: { } } ] ) ``` Output: ```javascript [ { // Plan Cache Entry 1 version: '2', planCacheShapeHash: '478AD696', planCacheKey: '21AE23AD', isActive: true, works: Long("7"), timeOfCreation: ISODate("2023-05-22T20:33:49.031Z"), cachedPlan: { ... }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("8194"), host: 'mongodb1.example.net:27018' }, { // Plan Cache Entry 2 version: '2', planCacheShapeHash: '3D8AFDC6', planCacheKey: '1C2C4360', isActive: true, works: Long("6"), timeOfCreation: ISODate("2023-05-22T20:33:50.584Z"), cachedPlan: { ... }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("11547"), host: 'mongodb1.example.net:27018' }, { // Plan Cache Entry 3 version: '2', planCacheShapeHash: '27285F9B', planCacheKey: '20BB9404', isActive: true, works: Long("1"), timeOfCreation: ISODate("2023-05-22T20:33:49.051Z"), cachedPlan: { ... }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("7406"), host: 'mongodb1.example.net:27018' }, { // Plan Cache Entry 4 version: '2', planCacheShapeHash: '478AD696', planCacheKey: 'B1435201', isActive: true, works: Long("5"), timeOfCreation: ISODate("2023-05-22T20:33:49.009Z"), cachedPlan: { ... }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("7415"), host: 'mongodb1.example.net:27018' } ], ``` **Warning:** Starting in MongoDB 8.0, the existing `queryHash` field is duplicated in a new field named `planCacheShapeHash`. If you're using an earlier MongoDB version, you'll only see the `queryHash` field. Future MongoDB versions will remove the deprecated `queryHash` field, and you'll need to use the `planCacheShapeHash` field instead. See also [planCacheKey.](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-plan-cache-key) ### Find Cache Entry Details for a Query Hash To return plan cache information for a particular query hash, the [`$planCacheStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/plancachestats.md#mongodb-pipeline-pipe.-planCacheStats) stage can be followed by a [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) on the `planCacheKey` field. The following aggregation pipeline uses [`$planCacheStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/plancachestats.md#mongodb-pipeline-pipe.-planCacheStats) followed by a [`$match`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) stage to return specific information for a particular query hash: ```javascript db.orders.aggregate( [ { $planCacheStats: { } }, { $match: { planCacheKey: "B1435201"} } ] ) ``` Output: ```javascript [ { version: '2', planCacheShapeHash: '478AD696', planCacheKey: 'B1435201', isActive: true, works: Long("5"), timeOfCreation: ISODate("2023-05-22T20:33:49.009Z"), cachedPlan: { slots: '$$RESULT=s11 env: { s3 = 1684787629009 (NOW), s6 = Nothing, s5 = Nothing, s1 = TimeZoneDatabase(Asia/Kuwait...Etc/UCT) (timeZoneDB), s10 = {"item" : 1, "price" : 1}, s2 = Nothing (SEARCH_META) }', stages: '[2] nlj inner [] [s4, s7, s8, s9, s10] \n' + ' left \n' + ' [1] cfilter {(exists(s5) && exists(s6))} \n' + ' [1] ixseek s5 s6 s9 s4 s7 s8 [] @"358822b7-c129-47b7-ad7f-40017a51b03c" @"item_1_price_1" true \n' + ' right \n' + ' [2] limit 1 \n' + ' [2] seek s4 s11 s12 s7 s8 s9 s10 none none [] @"358822b7-c129-47b7-ad7f-40017a51b03c" true false \n' }, indexFilterSet: false, isPinned: false, estimatedSizeBytes: Long("7415"), host: 'mongodb1.example.net:27018' } ] ``` **Warning:** Starting in MongoDB 8.0, the existing `queryHash` field is duplicated in a new field named `planCacheShapeHash`. If you're using an earlier MongoDB version, you'll only see the `queryHash` field. Future MongoDB versions will remove the deprecated `queryHash` field, and you'll need to use the `planCacheShapeHash` field instead. See also [planCacheKey](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-plan-cache-key) and [planCacheShapeHash.](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-query-hash)