Distinct Query Plan Capture for Stable SQL Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face challenges in managing query plan stability due to performance regressions caused by system upgrades, data skew, inaccurate size estimation, and parameter variability, leading to inefficient storage of query plans and resource utilization.
Innovation Solution
Implementing an intelligent Abstract SQL Plan (ASP) retention strategy that captures and stores only distinct query plans, using a QPS manager to serialize query plans into ASPs, and employing tree-shape strings and hash values for efficient comparison to avoid duplicates, thereby optimizing storage and maintaining system performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If query plans are captured and stored to maintain stability, then performance regression is mitigated, but storage space is consumed
Solution Approach 1:
The patent creates a simplified representation (copy) of the query plan using tree-shape strings instead of storing the complete query plan objects. This copy contains only the essential structural information needed for comparison and identification, significantly reducing storage requirements while maintaining the ability to detect duplicate plans and ensure stability.
Solution Approach 2:
The patent extracts only the critical structural elements of query plans (operator types, child operator relationships) into tree-shape strings, discarding redundant detailed information. This extraction allows the system to store minimal representations that are sufficient for detecting plan changes and maintaining stability without consuming excessive storage space.
2Reliability
If all query plans are stored to ensure stability, then performance regression is prevented, but storage capacity is exceeded
Solution Approach 1:
Instead of storing complete query plan objects, the system creates compact string representations (copies) that capture only the essential tree structure. This copying approach reduces the volume of stored data dramatically while preserving the ability to identify duplicate plans and maintain stability through comparison of these simplified representations.
Solution Approach 2:
The patent merges multiple query plan objects into a single unified storage structure using tree-shape strings. By combining the structural information from multiple plans into a standardized string format, the system can store and compare plans more efficiently, reducing overall storage capacity requirements while maintaining stability.
3Quantity of substance
If query plans are compared to avoid duplicates, then storage efficiency is improved, but processing time is consumed
Solution Approach 1:
The patent creates simplified string copies of query plans that can be quickly compared using standard string comparison operations. These copies are much smaller and faster to process than complete query plan objects, allowing efficient duplicate detection while minimizing the processing time required for comparison operations.
Solution Approach 2:
The patent replaces complex object-based comparison mechanisms with simpler string-based comparison. By substituting the mechanical process of comparing full query plan objects with the more efficient process of comparing compact tree-shape strings, the system achieves faster processing times while maintaining storage efficiency.
Data Source
AI summary
A computer implemented method can generate a current query execution plan for a query and serialize the current query execution plan into a current query plan object. The current query plan object specifies a query tree which defines a plurality of query operators of the current query execution plan. The method can compare the current query plan object with one or more stored query plan objects contained in a plan repository. The one or more stored query plan objects were serialized from previous query execution plans generated for the query. Responsive to finding that no stored query plan object matches the current query plan object, the method can store the current query plan object in the plan repository. Related systems and software for implementing the method are also disclosed.