Database Query Optimizer Automatic Tuning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems often experience performance regressions due to inefficient query execution plans during upgrades, leading to dissatisfaction among customers and potential avoidance of upgrades, as the new optimizer may select suboptimal plans compared to the previous version.
Innovation Solution
Implementing an automatic tuning mechanism that uses the best query plan generated by the prior version as a baseline, learns to identify and avoid inefficient plans, and dynamically adjusts execution plans based on performance metrics to ensure performance consistency across upgrades.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If the database system upgrades to a new version with a new optimizer, then new features and improvements are obtained, but query performance may regress due to inefficient query execution plans
Solution Approach 1:
The system performs preliminary action by using the prior version's optimizer to generate a baseline query execution plan before the new optimizer processes the query. This baseline plan serves as a reference point, allowing the system to compare new plans against a known good performance standard and prevent performance regressions during upgrades.
Solution Approach 2:
The system implements feedback by comparing the execution metrics of new query plans against the baseline plan and using this comparison to determine whether to accept or reject the new plan. The feedback loop ensures that only plans performing at least as well as the baseline are adopted, preventing performance degradation while allowing improvements.
2Productivity
If the optimizer selects new query execution plans in the new version, then optimization improvements are achieved, but runaway queries occur with inefficient plans
Solution Approach 1:
The system uses feedback by monitoring query execution metrics and comparing them against the baseline plan's performance. This feedback mechanism allows the optimizer to learn from actual execution results and adjust future plan selections, preventing runaway queries while maintaining optimization improvements.
Solution Approach 2:
The system applies dynamics by making the query plan selection process adaptive and learnable. The optimizer dynamically adjusts its behavior based on observed execution metrics, transitioning from static plan generation to a dynamic, data-driven approach that prevents inefficient plans from being selected in the future.
3Reliability
If manual tuning of query plans is performed, then performance optimization is achieved, but DBA intervention is required increasing operational complexity
Solution Approach 1:
The system implements self-service by automatically generating baseline plans, evaluating new plans against the baseline, and making decisions about plan acceptance without requiring DBA intervention. The system performs self-tuning of query execution plans, eliminating manual optimization tasks while maintaining performance reliability.
Data Source
AI summary
System and methodology for automatic tuning of database query optimizer is described. In one embodiment, in a database system having an optimizer for selecting a query plan for executing a database query, a method of the present invention is described for automatically tuning query performance to prevent query performance regression that may occur during upgrade of the database system from a prior version to a new version, the method comprises steps of: in response to receiving a given database query for execution, specifying a query plan generated by the prior version's optimizer as a baseline best plan for executing the given database query; generating at least one new query plan using the new version's optimizer; learning performance for each new query plan generated by recording corresponding query execution metrics; if a given new query plan is observed to have better performance than the best plan previously specified, specifying that given new query plan to be the best plan for executing the given database query; if a given new query plan is observed to have worse performance than the best plan previously specified, specifying that given new query plan to be a bad plan to be avoided in the future; and automatically tuning future execution of the given database query by using the query plan that the system learned was the best plan.