Incremental Query Plan Repair for Database Recompilation Overhead
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Complex queries in database systems often require frequent recompilation due to changes in data statistics or database design, leading to significant overhead in time and cost, despite the use of plan caches to mitigate recompilation overhead.
Innovation Solution
The implementation of incremental repair techniques, including local repair, global repair, and local recompilation, which utilize existing query plans to produce new, optimized plans with minimal changes, and a threshold-based policy to determine when full recompilation is necessary.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If full recompilation is performed to ensure optimal query performance, then query execution efficiency is improved, but time and computational overhead increase significantly
Solution Approach 1:
The patent segments the query plan recompilation process into two distinct approaches: full recompilation and incremental repair. Full recompilation is performed only when necessary (when plans are invalidated), while incremental repair handles minor modifications by adjusting only the affected portions of the plan. This segmentation reduces the frequency and overhead of complete recompilations while maintaining query execution efficiency.
Solution Approach 2:
The patent applies partial action by implementing incremental repair techniques that modify only the necessary portions of a query plan rather than performing complete recompilation. When statistics or database structures change, the system performs partial updates to the execution plan, adjusting only the affected operators or subplans, thereby reducing the time and computational resources required compared to full recompilation.
2Loss of time
If query plans are cached to reduce recompilation overhead, then recompilation frequency is reduced, but plan validity becomes an issue when base data or database structure changes
Solution Approach 1:
The patent implements a feedback mechanism through plan invalidation tracking. The system monitors changes in base data statistics and database structure, and when such changes occur, it invalidates the affected cached query plans. This feedback loop ensures that cached plans remain valid by detecting when they become obsolete due to underlying data or structure changes, triggering appropriate recompilation or repair actions.
Solution Approach 2:
The patent introduces dynamic adaptability to the query plan caching system by implementing incremental repair capabilities. When cached plans become partially invalid due to database changes, the system dynamically adjusts the plans through incremental repair rather than complete recompilation. This dynamic approach allows the system to maintain cached plans when possible while adapting them to current database states, balancing caching benefits with plan validity.
3Loss of time
If incremental repair techniques are used to reduce recompilation overhead, then time and cost are reduced, but the ability to handle significant database changes is limited
Solution Approach 1:
The patent segments the handling of database changes into two pathways: incremental repair for minor changes and full recompilation for significant changes. The system evaluates the nature and extent of database changes, and when changes exceed the threshold that incremental repair can handle, it automatically transitions to full recompilation. This segmentation allows the system to maximize the benefits of incremental repair for routine changes while maintaining the ability to handle significant database restructuring through complete recompilation.
Data Source
AI summary
Database systems use a plan cache to avoid the overheads (e.g., time, money) of query recompilation. Query plans can become invalidated by updates to the statistics on data or changes to the physical database design. Once a plan is invalidated, it can be repaired utilizing one or more of the disclosed embodiments. Incremental repair of query plans includes reusing parts of the current plan rather than discarding the plan entirely when it is invalidated. Repair to an existing query plan is attempted before resorting to full recompilation.