SQL Execution Plan Iteration Using Actual Parameters
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The execution efficiency of SQL statements is hindered by the reliance on estimation parameters based on sampling, which often result in suboptimal execution plans due to errors and hysteresis in statistical information.
Innovation Solution
A method and apparatus that iteratively refine the SQL execution plan by comparing and updating plan trees based on actual execution parameters, establishing a stable plan tree when differences are within a threshold, and recording execution times to select the optimal plan tree for improved efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If statistical information is obtained based on sampling values, then the determination process is simplified and faster, but the accuracy of the SQL execution plan deteriorates due to errors and hysteresis in the statistical information
Solution Approach 1:
The system performs preliminary actions by collecting actual execution parameters during plan execution and storing them for future use. Before determining a new execution plan, the system checks whether sufficient actual execution data is already available, and if so, uses this pre-collected data to improve accuracy without repeating the full data collection process.
Solution Approach 2:
The system implements feedback by using actual execution parameters obtained from previous plan executions to adjust and optimize subsequent plan determination. The actual execution data feeds back into the statistical information database, continuously improving the accuracy of execution plan determination over time while maintaining efficient processing.
2Measurement precision
If iteration is performed multiple times to refine the execution plan, then the accuracy of the SQL execution plan improves, but the complexity of the determination process increases
Solution Approach 1:
The system applies partial action by performing iteration only when necessary - specifically, when actual execution parameters indicate that the current plan is suboptimal or when confidence thresholds are not met. The system does not unnecessarily iterate in all cases, but selectively applies additional iteration steps to achieve sufficient accuracy without excessive complexity.
Solution Approach 2:
The system implements dynamic adjustment of iteration depth based on actual execution feedback. The determination process adapts its complexity level - using simple direct determination when statistical information is sufficient, and switching to multi-stage iteration only when actual execution data suggests improvement is needed, thus optimizing the balance between accuracy and complexity.
Data Source
AI summary
A method and an apparatus for determining a structured query language (SQL) execution plan are provided to optimize determining of the SQL execution plan and improve execution efficiency of the SQL execution plan. The SQL execution plan corresponds to at least one relation table. During an Nth iteration, the method includes obtaining a first iteration parameter generated after a first plan tree is executed on the at least one relation table during an (N−1)th iteration, where N is a natural number greater than 1, establishing a second plan tree according to the first iteration parameter, and determining the first plan tree or the second plan tree as the SQL execution plan when a difference between the second plan tree and the first plan tree is not greater than a first threshold.