Database Query Plan Caching with Annotated Repository
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face inefficiencies in query execution due to the need for repeated parsing and optimization of frequently executed queries, which can lead to resource wastage and performance regression caused by changes in data characteristics or optimizer software.
Innovation Solution
A database management system that employs a query repository table to store annotated query plans, using cache keys and configuration-ids to retrieve and update query plans efficiently, and a query optimizer to generate plans based on heuristics, while preventing performance regression by managing status indicators and generating new configuration-ids for software and hardware updates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of energy
If query plans are cached and reused, then resource costs are reduced, but performance regression may occur due to changes in data characteristics or optimizer software
Solution Approach 1:
The system performs preliminary actions by detecting software updates before they take effect, invalidating cached query plans in advance. This prevents the use of outdated query plans that would cause performance regression, while still allowing the system to benefit from caching previously validated plans.
Solution Approach 2:
The system implements feedback mechanisms by monitoring for software updates and using this information to dynamically manage the query plan cache. When updates are detected, the system responds by invalidating relevant cached plans, creating a closed-loop control system that adapts to changing conditions.
2Productivity
If query plans are frequently updated to adapt to changing data characteristics, then query execution efficiency is improved, but the overhead of parsing and optimization increases
Solution Approach 1:
The system performs preliminary validation of query plans against updated software versions before they are executed. This preliminary check prevents the need to re-parse and re-optimize queries that would still be valid, reducing the frequency of full query plan regeneration while maintaining execution efficiency.
Solution Approach 2:
The system changes the state parameter of cached query plans from valid to invalid when software updates are detected, rather than immediately regenerating all query plans. This parameter change approach allows the system to maintain efficiency by reusing valid plans while adapting to software changes through selective invalidation.
Data Source
AI summary
One example discloses a database management system that can comprise a memory for storing computer executable instructions and a processing unit for accessing the memory and executing the computer executable instructions. The computer executable instructions can comprise a query data collector to access a query repository table to determine if a first query plan for a first configuration executed on a relational database has a lowest resource cost of execution between the first query plan and a second query plan for a second configuration. The query repository table can include a plurality of query records with annotated query plans, each annotated query plan comprising text sufficient to generate a query plan of a certain form.