Database Query Plan Cache Eviction Using Utility Scores
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems (DBMS) query plan cache management is inefficient as it only frees resources when the cache reaches its storage limit, failing to address resource usage when the cache is below the limit, and lacks an accurate method to determine which plans to evict.
Innovation Solution
A DBMS query plan cache management platform calculates a utility score for each query plan based on execution count, compilation time, and last usage time, allowing for evictions based on these scores rather than cache size, enabling efficient and accurate management of query plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If LRU eviction scheme is used when cache reaches storage limit, then memory space is freed, but resource management is inefficient as it does not address resources when cache is below limit and evicts based on time rather than utility
Solution Approach 1:
The patent changes the eviction parameter from cache size (physical limit) to utility score (performance metric). The system calculates utility scores based on execution count, compilation time, and last usage time, then evicts plans with lowest utility scores regardless of cache size, transforming the eviction decision from a space-based to a performance-based parameter
Solution Approach 2:
The system implements feedback by continuously monitoring query plan performance metrics (execution count, compilation time, last usage time) and using this information to calculate utility scores. This feedback loop enables dynamic eviction decisions that adapt to actual plan utility rather than static cache size thresholds
2Productivity
If plan cache stores all compiled plans, then query execution can be optimized by reusing plans, but memory consumption increases and resources are wasted on plans that will never be used again
Solution Approach 1:
The patent transforms the cache management approach by changing from size-based retention to utility-based retention. Plans are retained based on their calculated utility scores rather than simply filling available memory space, ensuring that memory is allocated to plans that provide actual performance value
Solution Approach 2:
The system implements selective discarding of query plans based on utility score evaluation. Plans with low utility scores are evicted from the cache to free memory, while high utility plans are retained, enabling continuous optimization of memory allocation based on actual plan performance
3Ease of manufacture
If cache eviction is based on Least-Recently-Used scheme, then implementation is simple, but accuracy in determining which plans to evict is poor as it does not consider plan utility
Solution Approach 1:
The patent changes the eviction criterion from temporal (last usage time) to performance-based (utility score combining execution count, compilation time, and last usage time). This parameter transformation improves eviction accuracy by considering actual plan usefulness rather than just recency of access
Solution Approach 2:
The utility score calculation acts as an intermediary that synthesizes multiple performance metrics into a single evictable metric. This intermediary layer translates complex performance data into a straightforward decision criterion, maintaining implementation simplicity while improving accuracy
Data Source
AI summary
According to some embodiments, a system to manage a query plan cache for a Database Management System (“DBMS”) includes a DBMS query plan cache data store. The DBMS query plan cache data store may contain, for example, electronic records representing a plurality of query plans each associated with a set of instructions created in response to a query previously submitted by a user. A DBMS query plan cache management platform may then calculate a utility score for each query plan in the DBMS query plan cache data store. At least one query plan may be evicted from the DBMS query plan cache data store based on the calculated utility score, wherein the evicting is not based on a size of the DBMS query plan cache.