Parametric Query Optimization via Recost and Plan Caching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in processing parameterized SQL queries, particularly due to high optimizer overheads and unbounded sub-optimality when optimizing each query instance separately, or using a single plan for all instances, which leads to sub-optimal results and excessive resource usage.
Innovation Solution
The proposed solution involves a method that uses selectivity and cost checks to minimize optimizer calls while ensuring tight and bounded sub-optimality, managing a plan cache to reduce redundancy and optimize the number of stored plans, and employing a Recost feature to find suitable plans within an optimality region, thereby reducing the need for optimizer calls and optimizing resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If each query instance is optimized separately (Optimize-Always), then query execution optimality is improved, but optimizer overhead increases significantly
Solution Approach 1:
The system performs preliminary optimization by optimizing a representative query instance in advance and storing the resulting execution plan in a plan cache. When subsequent query instances with similar parameters arrive, the pre-computed plan is reused, avoiding repeated optimization overhead while maintaining acceptable execution quality.
Solution Approach 2:
The system changes parameters by transforming specific query instances into parameterized query templates through generalization. By replacing specific parameter values with parameters and computing selectivity bounds, the system creates a family of query templates that can share execution plans across multiple instances, reducing optimizer invocations while bounding sub-optimality.
2Loss of time
If a single execution plan is reused for all query instances (Optimize-Once), then optimizer overhead is reduced, but query execution quality becomes arbitrarily sub-optimal
Solution Approach 1:
The system applies local quality by computing instance-specific selectivity bounds for different query instances and using these local characteristics to determine plan reuse decisions. Instead of applying a uniform optimization strategy to all instances, the system adapts plan selection based on local selectivity conditions, ensuring each instance receives appropriately optimized plans.
Solution Approach 2:
The system introduces dynamics by making plan selection adaptive rather than static. The query optimizer dynamically decides whether to reuse an existing plan or generate a new one based on real-time selectivity checks and cost comparisons, allowing the system to balance between optimization overhead and execution quality adaptively for each query instance.
3Manufacturing precision
If multiple execution plans are stored for parameterized queries, then query execution quality is improved, but plan cache complexity and memory usage increase
Solution Approach 1:
The system segments the plan cache by organizing execution plans according to query templates and their associated selectivity bounds. Instead of storing plans in a monolithic structure, the system divides the cache into template-specific sections with defined selectivity ranges, making plan retrieval and management more efficient while maintaining execution quality.
Solution Approach 2:
The system uses copying by creating parameterized query templates from specific query instances and reusing these templates across multiple instances. Instead of storing and managing completely separate plans for each instance, the system copies the essential structure of execution plans into template form, reducing redundancy while maintaining the ability to serve multiple instances efficiently.
Data Source
AI summary
In some embodiments, the disclosed subject matter involves a server query optimizer for parametric query optimization (PQO) to address the problem of finding and reusing a relatively small number of query plans that can achieve good plan quality across multiple instances of a parameterized query. An embodiment processes query instances on-line and ensures (a) tight, bounded cost sub-optimality for each instance, (b) low optimization overheads, and (c) only a small number of plans need to be stored. A plan re-costing based approach is disclosed to provide good performance on all three metrics. Other embodiments are described and claimed.