Database Query Execution Plan Auto-Binding System
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face challenges in ensuring query execution plan stability and efficiency, as existing tools cannot guarantee optimal performance when switching from one query execution plan (QEP) to another, leading to potential regression and increased costs due to performance disruptions and troubleshooting efforts.
Innovation Solution
A system and method that automatically monitors and compares original and new QEPs, designates the most optimal QEP, and binds it to the database query, including features like a QEP repository for storing active and inactive plans, user input for rebinding, and execution statistics for performance analysis.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a new QEP is bound to a database query to improve performance, then query execution efficiency may be improved, but the risk of performance regression increases
Solution Approach 1:
The system performs preliminary actions by collecting execution statistics and generating predicted QEPs before actually binding them to database queries. The bind wait period allows the system to gather performance data on predicted QEPs in a testing environment before committing them to production, thus preventing performance regression while still enabling performance optimization.
Solution Approach 2:
The system implements feedback mechanisms by continuously collecting execution statistics from the database management system and using this data to evaluate QEP performance. The feedback loop compares actual performance against predictions, allowing the system to learn from past performance and improve future QEP selections while maintaining stability through evidence-based decision making.
2Reliability
If a DBA manually reviews and decides on QEP bindings for hundreds or thousands of queries, then control over performance is improved, but the time and complexity of management increases
Solution Approach 1:
The system enables self-service by automatically generating predicted QEPs, collecting execution statistics, evaluating performance, and making binding decisions without requiring DBA intervention for each individual query. The automated system serves itself by using its own collected data to make intelligent decisions, freeing DBAs from manual review while maintaining performance control through automated monitoring and evaluation.
Solution Approach 2:
The system changes parameters by transforming complex QEP evaluation decisions into automated processes based on execution statistics and performance metrics. By changing the decision-making parameters from manual expert judgment to automated statistical analysis, the system reduces time consumption while maintaining or improving reliability through data-driven decisions.
3Speed
If QEP bindings are changed frequently to optimize performance, then query execution speed may improve, but system stability and predictability deteriorate
Solution Approach 1:
The bind wait period serves as a preliminary testing phase where predicted QEPs are evaluated in a controlled manner before full deployment. This preliminary action allows the system to test performance improvements while maintaining stability by limiting the scope of changes during the evaluation period, thus balancing speed improvement with binding stability.
Solution Approach 2:
The system implements dynamic QEP binding where bindings can change based on performance evidence, but with controlled timing through the bind wait period. The system adapts to changing performance conditions by allowing QEP updates when evidence supports improvement, while the timed wait period introduces stability by preventing overly frequent changes. This dynamic approach balances execution speed optimization with binding stability.
Data Source
AI summary
An apparatus, system, and method are disclosed for improving query execution plan efficiency in a database management system. The present invention binds both static database queries and dynamic database queries to new query execution plans (QEPs) that produce the same result set as a former QEP bound to the database queries. Next, the present invention identifies one of the former QEPs and the new QEP as an active QEP and automatically collects execution statistics for one of the former QEPs and the new QEP. Finally, the present invention automatically selects one of the former QEPs and the new QEP as the active QEP in response to completion of the automatic collection of execution statistics, the active QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the active QEP selected based on the execution statistics.