Dynamic Query Plan Fragmentation for Database Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Complex queries in large commercial database systems often result in sub-optimal execution plans due to errors in cardinality and cost estimations, especially with increased joins and skewed data, leading to inefficient resource usage and processing issues.
Innovation Solution
A method is introduced to select between static and dynamic query plan generation, where requests are fragmented into manageable parts, allowing incremental planning and execution, with feedback applied between fragments to adjust and optimize the plan dynamically.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If static plan generation is used, then query execution simplicity is maintained, but query performance deteriorates due to exponential error growth in cardinality and cost estimations for complex queries with multiple joins
Solution Approach 1:
The patent divides a complex query into multiple query fragments, each representing a subset of the original query. This segmentation allows the system to generate and execute plans for smaller, more manageable units rather than attempting to optimize the entire complex query at once, thereby reducing error accumulation in cardinality and cost estimations.
Solution Approach 2:
The patent introduces dynamic plan generation that adapts during query execution. Instead of relying solely on static compile-time statistics, the system dynamically adjusts query plans based on actual runtime observations and feedback from intermediate results, allowing it to correct estimation errors as they occur during execution.
2Adaptability or versatility
If dynamic plan generation with fragmentation is implemented, then query performance and adaptability improve, but system complexity increases due to incremental planning and execution overhead
Solution Approach 1:
The query optimizer is segmented into multiple components that handle different aspects of query processing: fragment identification, individual fragment planning, execution monitoring, and dynamic plan adjustment. This modular architecture manages complexity by distributing optimization tasks across specialized subsystems rather than requiring a single complex optimization engine.
Solution Approach 2:
The system performs preliminary actions by pre-identifying query fragments and preparing initial plans for each fragment before full execution. This preliminary structuring of the query into manageable pieces with pre-computed plans reduces the complexity of real-time decision-making during query execution.
3Productivity
If static plans are executed blindly without feedback, then execution simplicity is maintained, but resource usage efficiency deteriorates when actual table row counts, CPU usage, and I/O counts differ significantly from optimizer estimates
Solution Approach 1:
The patent implements feedback mechanisms that monitor actual query execution metrics including table row counts, CPU usage, and I/O operations. This feedback is used to detect deviations from estimated costs and cardinalities, triggering dynamic plan adjustments to correct sub-optimal execution paths and improve resource efficiency.
Solution Approach 2:
The query execution system transitions from static to dynamic operation by continuously adapting plans based on runtime feedback. When actual resource consumption patterns diverge from optimizer predictions, the system dynamically reoptimizes query plans to align with observed conditions, preventing waste of time and resources on increasingly inefficient execution paths.
Data Source
AI summary
Techniques for improving complex database queries are provided. A determination is made whether to adopt a static or dynamic query execution plan based on metrics. When the dynamic query execution plan is used, a request fragment of the request is planned and the corresponding plan fragment is executed. The processed fragment provides feedback related to its processing to the remaining request and the process is repeated on the remaining request until the request is completed.