-
Type: Improvement
-
Resolution: Done
-
Priority: Major - P3
-
Affects Version/s: None
-
Component/s: Querying
-
Fully Compatible
This information can be useful to detect when a query plan is oscillating between a good and a bad plan.
Things exit the cache for:
1. safety reasons (index add/drop)
2. no more space in cache
3. performance degrades
4. writes (changes data distribution)
5. explicit commands to mutate cache
Let's log when this happens w/level...1.
The query optimizer currently generates a query plan which is cached and re-used based on the normalized "shape" of the query predicate and sort criteria.
It would be useful to be able to identify plans by id (e.g., hash value) for tracking, hinting and logging.
Tracking
Store the history when the query plan is changed and what it was changed to in the db's system.plans collection.
Diagnostics
Log every time the plan changes for a given query, e.g., <query_id>: changed <old_plan_id> to <new_plan_id>
- is duplicated by
-
SERVER-12358 Add query plan history and logging
- Closed
- is related to
-
SERVER-8871 Support admin hints per query pattern
- Closed
-
SERVER-666 query optimizer introspection
- Closed
- related to
-
SERVER-9152 Ability to get the list of remembered query plans from the server
- Closed