Runtime Statistics Mapping for Query Plan Cost Estimation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization systems in database management systems (DBMS) face inaccuracies in estimating data statistics for query plans, leading to sub-optimal plan selection due to errors in estimating internal node statistics, which are amplified through cascading effects, resulting in inefficient resource usage.
Innovation Solution
Implementing a system with runtime statistics feedback that collects and maps data statistics from executed query plans to a dictionary, allowing for more accurate estimation of internal node statistics and adaptive query plan optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If cost-based query optimization uses estimated statistics for internal nodes, then query plan selection can be performed, but estimation errors are introduced leading to sub-optimal plans
Solution Approach 1:
The system performs preliminary actions by collecting and storing runtime statistics from executed query plans in a dictionary before they are needed for optimization. This pre-collection of actual statistics eliminates the need for error-prone estimation when optimizing subsequent queries, as the statistics are already available from real execution data.
Solution Approach 2:
The system implements feedback by using runtime statistics collected from previously executed query plans to improve the accuracy of statistics estimation for subsequent query optimizations. The feedback loop continuously refines the statistics dictionary with actual execution data, enabling more accurate cost-based optimization over time.
2Measurement precision
If runtime statistics collection is implemented, then statistics estimation accuracy improves, but system complexity increases
Solution Approach 1:
The runtime statistics collection mechanism serves multiple functions: it collects actual statistics from query execution, stores them in a dictionary for future reference, and provides feedback for continuous refinement. This multi-functional approach consolidates what could be separate complex systems into a unified statistics management infrastructure.
Solution Approach 2:
The system serves itself by automatically collecting its own runtime statistics during normal query execution without requiring external intervention. The statistics are gathered as a byproduct of query plan execution, and the system uses these self-collected statistics to improve its own optimization accuracy.
3Adaptability or versatility
If statistics are collected from executed query plans, then adaptability to dynamic data changes improves, but additional processing overhead is introduced
Solution Approach 1:
The system maintains continuous collection of runtime statistics during normal query execution without interrupting or pausing operations. This continuous feedback mechanism ensures the statistics dictionary is constantly updated with current data characteristics, enabling real-time adaptability to changing data distributions while maintaining uninterrupted query processing.
Data Source
AI summary
A computer implemented method can execute a first query plan for a query, obtain statistics for internal nodes of a first query tree representing the first query plan, receive a second query tree representing a second query plan for the query, search for a matching internal node of the first query tree for a selected internal node of the second query tree, and responsive to finding the matching internal node of the first query tree, apply the statistics for the matching internal node of the first query tree to the selected internal node of the second query tree for estimating cost of the second query plan during query optimization of the query. Related systems and software for implementing the method are also disclosed.