In-line Query Capture Database for Dynamic Plan Re-optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Prior art query optimizers fail to dynamically adjust query plans in response to real-time system conditions and operating environment events, leading to inconsistent response times and inability to meet service level goals in complex relational database systems.
Innovation Solution
A computer-implemented method and system that uses an in-line query capture database to monitor and optimize query execution plans, incorporating actual cost information and results to re-optimize plans during execution, and dynamically adjusts system resources and priorities based on system conditions and operating environment events.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If prior art query optimizers use cost models to generate query execution plans, then query plans can be generated based on estimated costs, but the optimizers fail to dynamically adjust plans in response to real-time system conditions leading to inconsistent response times
Solution Approach 1:
The patent implements a feedback mechanism where actual query execution results and system condition data are captured during query execution, stored in a query capture database, and fed back to the query optimizer. This allows the optimizer to dynamically adjust query execution plans based on real-time performance data and actual system conditions, transforming the static cost-based optimization into a dynamic adaptive system that maintains consistent response times.
Solution Approach 2:
The system transitions from static query optimization to dynamic optimization by enabling real-time adjustments to query execution plans. The query optimizer continuously monitors actual execution results and system conditions through the query capture database, and dynamically modifies execution plans during query processing to adapt to changing conditions, ensuring reliable response times.
2Productivity
If query optimizers rely on statistics and modeling assumptions, then cost estimates can be generated, but the estimates may not reflect actual system conditions leading to suboptimal query plans
Solution Approach 1:
The system captures actual query execution results including measured costs and performance metrics in the query capture database, providing feedback to the query optimizer. This feedback loop allows the optimizer to replace or supplement theoretical cost model estimates with actual measured data, significantly improving cost estimation accuracy and enabling more efficient query execution decisions.
Solution Approach 2:
The system implements self-service optimization where the query capture database automatically collects and stores actual execution results during normal query processing. This self-collected data is then used by the query optimizer to improve its cost estimates without requiring external intervention, enabling continuous improvement of query execution efficiency through accumulated operational experience.
3Reliability
If the system monitors and re-optimizes query execution plans in real-time, then response times improve, but system complexity increases due to the query capture database and additional monitoring infrastructure
Solution Approach 1:
The query capture database serves multiple functions: it stores actual query execution results for feedback to the optimizer, maintains system condition data for dynamic plan adjustment, and provides historical performance data for analysis. This multi-functionality reduces the need for separate specialized components, managing system complexity while enabling reliable service level goal achievement through real-time monitoring and re-optimization.
Data Source
AI summary
A computer-implemented method, apparatus and article of manufacture for optimizing a database query. A query execution plan is generated for the database query, which may be stored in an in-line query capture database. Execution of the query execution plan may be monitored in order to generate performance information, which also may be stored in the in-line query capture database. The data stored in in-line query capture database may be used during the initial generation of query execution plans, as well as for the re-optimization of a query execution plan, during the execution of the query execution plan.