Database Execution Plan Refresh via Cost Validation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face performance regression due to stale execution plans resulting from changes in system characteristics, hardware updates, or optimizer software changes, with no guarantee that recompiled plans will improve performance and potential risk of worsening it.
Innovation Solution
A method and system for refreshing stored execution plans by compiling, executing, and comparing the actual resource cost of database queries, replacing the stored plan only if the new plan's cost is not worse, and discarding it if worse, ensuring consistent performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of energy
If stored execution plans are used to re-use compiled queries, then CPU cost is saved and query performance is consistent, but the plans become sub-optimal over time as system characteristics change
Solution Approach 1:
The system pre-compile the query and store the execution plan in advance. When the stored plan is detected to be sub-optimal (through performance monitoring or change detection), the system automatically recompiles the query to generate a new execution plan, replacing the stale one without requiring manual intervention. This preliminary action ensures the system proactively maintains optimal performance.
Solution Approach 2:
The system implements feedback mechanisms by monitoring actual query performance and comparing it against expected performance or stored plan performance. When degradation is detected, the system triggers automatic re-compilation. This closed-loop feedback ensures the execution plan remains optimal by continuously adapting to changing system characteristics.
2Productivity
If automatic re-compilation is performed to update execution plans, then performance may be improved, but there is risk that the new plan could be worse than the stored plan
Solution Approach 1:
The system prepares a new execution plan in advance through re-compilation, but does not immediately replace the stored plan. Instead, it validates the new plan's performance characteristics first, ensuring it meets acceptable criteria before deployment. This cushioning approach prevents premature adoption of potentially inferior plans.
Solution Approach 2:
The system monitors and compares the performance of the newly compiled execution plan against the stored plan before committing to replacement. Only if the new plan demonstrates equal or better performance does the system proceed with replacement. This feedback-based validation ensures reliability by preventing regression to worse performance.
3Adaptability or versatility
If manual re-compilation with 'with recompile' syntax is used, then the query can be recompiled, but there is no guarantee the new plan will be better and it cannot be reverted
Solution Approach 1:
The system automatically monitors query performance and system changes, triggering re-compilation only when beneficial. It compares the new plan's performance against the stored plan and only replaces the stored plan if the new plan is proven to be equal or better. This feedback mechanism provides both adaptability (automatic updates when needed) and reliability (guaranteed no performance regression).
Solution Approach 2:
The system performs self-service by automatically detecting when re-compilation is needed, executing the re-compilation, validating the new plan, and replacing the stored plan if appropriate. This eliminates the need for manual 'with recompile' syntax while providing stronger performance guarantees through automated validation and comparison.
Data Source
AI summary
An improved system and method for storing, reusing and recompiling execution plans for SQL database queries. The system. The system validates the performance of a newly compiled plan prior to replacing a currently stored execution plan and compares the resource cost of the recompiled plan with the cost of the currently stored plan. In the event that the resource cost of the recompiled plan is not greater than the stored plan resource cost, the system replaces the currently stored execution plan with the recompiled plan.