$planCacheStats
On this page
Definition
$planCacheStats
Returns plan cache 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 takes an empty document as a parameter and has the following syntax:{ $planCacheStats: { } }
Considerations
Pipeline
$planCacheStats
must be the first stage in an aggregation
pipeline.
Restrictions
Access Control
On systems running with authorization
, the user
must have the planCacheRead
privilege for the collection.
Redaction
When using Queryable Encryption, the
$planCacheStats
stage omits operations against encrypted collections, even though the operations are cached as normal.
Read Preference
$planCacheStats
observes the 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 7.0.
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 was used.
For queries that use the classic execution engine,
$planCacheStats
returns a document similar to the following:
{ "version" : 1, "createdFromQuery" : <document>, "queryHash" : <hexadecimal string>, "planCacheKey" : <hexadecimal string>, "isActive" : <boolean>, "works" : <NumberLong>, "cachedPlan" : { "stage" : <STAGE1>, "filter" : <document>, "inputStage" : { "stage" : <STAGE2>, ... } }, "timeOfCreation" : <date>, "creationExecStats" : [ // Exec Stats Document for each candidate plan { "nReturned" : <num>, "executionTimeMillisEstimate" : <num>, "totalKeysExamined" : <num>, "totalDocsExamined" :<num>, "executionStages" : { "stage" : <STAGE A>, ... "inputStage" : { "stage" : <STAGE B>, ... } } }, ... ], "candidatePlanScores" : [ <number>, ... ], "indexFilterSet" : <boolean>, "estimatedSizeBytes" : <num>, "host" : <string>, "shard" : <string> }
Each document includes various query plan and execution stats, including:
Field | Description | |||||
---|---|---|---|---|---|---|
| A number that indicates the query engine used to complete the query.
| |||||
| A document that contains the specific query that resulted in this cache entry. For example:
| |||||
| A boolean that indicates whether the entry is active or inactive.
| |||||
| A hexadecimal string that represents the hash of the query shape. See | |||||
| 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 query shape and the currently available indexes for
that shape. See | |||||
| The details of the cached plan. The fields included in the
| |||||
| 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
| |||||
| Time of creation for the entry. | |||||
| An array of execution stats documents. The array contains a document for each candidate plan. For details on the execution stats, see
| |||||
| An array of scores for the candidate plans listed in the
| |||||
| A boolean that indicates whether an index filter exists for the query shape. | |||||
| The estimated size in bytes of a plan cache entry. | |||||
| The hostname and port of the 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 and host fields. See also Redaction. | |||||
| The name of the shard from which Only available if run on a sharded cluster. |
For queries that use the slot-based query execution engine, $planCacheStats
returns a
document similar to the following:
{ "version" : 2, "queryHash" : <hexadecimal string>, "planCacheKey" : <hexadecimal string>, "isActive" : <boolean>, "works" : <NumberLong>, "cachedPlan" : { "slots" : <string>, "stages": <string> }, "indexFilterSet" : <boolean>, "estimatedSizeBytes" : <num>, "host" : <string> }
Each document includes various query plan and execution stats, including:
Field | Description |
---|---|
| A number that indicates the query engine used to complete the query.
|
| A hexadecimal string that represents the hash of the query shape. See |
| 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 query shape and the currently available indexes for
that shape. See |
| A boolean that indicates whether the entry is active or inactive.
|
| 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
|
| The details of the cached plan. The fields included in the
|
| A boolean that indicates whether an index filter exists for the query shape. |
| The estimated size in bytes of a plan cache entry. |
| The hostname and port of the 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 and host fields. See also Redaction. |
Examples
The examples in this section use the following orders
collection:
db.orders.insertMany( [ { "_id" : 1, "item" : "abc", "price" : NumberDecimal("12"), "quantity" : 2, "type": "apparel" }, { "_id" : 2, "item" : "jkl", "price" : NumberDecimal("20"), "quantity" : 1, "type": "electronics" }, { "_id" : 3, "item" : "abc", "price" : NumberDecimal("10"), "quantity" : 5, "type": "apparel" }, { "_id" : 4, "item" : "abc", "price" : NumberDecimal("8"), "quantity" : 10, "type": "apparel" }, { "_id" : 5, "item" : "jkl", "price" : NumberDecimal("15"), "quantity" : 15, "type": "electronics" } ] )
Create the following indexes on the collection:
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: NumberDecimal("10")} } } );
Note
Index { item: 1, price: 1 }
is a partial index and only indexes documents with price
field greater than or equal to NumberDecimal("10")
.
Run some queries against the collection:
db.orders.find( { item: "abc", price: { $gte: NumberDecimal("10") } } ) db.orders.find( { item: "abc", price: { $gte: NumberDecimal("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 on the plan cache entries for the collection:
db.orders.aggregate( [ { $planCacheStats: { } } ] )
Output:
[ { // Plan Cache Entry 1 version: '2', queryHash: '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', queryHash: '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', queryHash: '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', queryHash: '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' } ],
See also planCacheKey
.
Find Cache Entry Details for a Query Hash
To return plan cache information for a particular query hash, the
$planCacheStats
stage can be followed by a
$match
on the planCacheKey
field.
The following aggregation pipeline uses $planCacheStats
followed by a $match
stage to return specific information
for a particular query hash:
db.orders.aggregate( [ { $planCacheStats: { } }, { $match: { planCacheKey: "B1435201"} } ] )
Output:
[ { version: '2', queryHash: '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' } ]
See also planCacheKey
and queryHash
.