Query Plan Cache Sizing Based on Database Query Locality
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The management of query plan cache size in database management systems (DBMS) is inefficient, leading to either waste of memory or frequent regeneration of query plans due to insufficient cache size, affecting performance.
Innovation Solution
An intelligent cache management system that dynamically adjusts the query plan cache size based on measured query locality, using a plan cache adjuster to increase or decrease cache size based on query locality metrics, such as accumulated promotion distances, to optimize memory usage and reduce compilation time.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the size of query plan cache is increased, then query execution efficiency is improved by reducing cache misses, but memory usage increases and valuable cache memory is wasted
Solution Approach 1:
The patent implements dynamic cache size management where the query plan cache size is continuously adjusted based on measured query locality. The system monitors query patterns and automatically expands or contracts the cache size to match actual workload requirements, transforming the static cache allocation into a dynamic adaptive system that optimizes both memory utilization and query execution efficiency.
Solution Approach 2:
The system employs feedback mechanisms by measuring query locality metrics (such as query repetition rates and access patterns) and using these measurements to inform cache size adjustments. The performance monitoring component tracks cache hit/miss ratios and query execution statistics, feeding this information back to the cache management system which then adjusts the cache size accordingly, creating a closed-loop control system.
2Quantity of substance
If the size of query plan cache is decreased, then memory usage is optimized and cache memory is available for other purposes, but query execution efficiency deteriorates due to frequent cache misses and query plan regeneration
Solution Approach 1:
Rather than using a fixed small cache size, the system dynamically adjusts the cache size based on real-time query locality measurements. When query patterns show high repetition and locality, the cache expands to accommodate more query plans. When locality decreases, the cache contracts to free memory, thus adapting to varying workload characteristics.
Solution Approach 2:
The system changes the cache size parameter dynamically based on measured query locality metrics. By monitoring parameters such as query repetition frequency and access patterns, the system adjusts the cache size parameter to optimize the balance between memory availability and query execution efficiency under different workload conditions.
3Device complexity
If a fixed cache size is used, then system simplicity is maintained, but adaptability to varying query workloads is reduced
Solution Approach 1:
The cache management system operates autonomously by automatically measuring query locality, determining optimal cache sizes, and adjusting cache allocation without manual intervention. The system serves itself by monitoring its own performance metrics and making self-directed adjustments to cache size, eliminating the need for complex external configuration while maintaining adaptability to varying workloads.
Solution Approach 2:
The system uses feedback from performance monitoring to automatically adjust cache size. By continuously measuring query locality and feeding this information back to the cache management logic, the system adapts to changing workload patterns automatically, achieving high adaptability without requiring complex manual configuration or external control mechanisms.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A method for intelligent query plan cache size management can be implemented. The method can measure query locality during execution of a plurality of incoming queries in a database management system. The database management system includes a query execution plan cache which has a size that can store at least some of query execution plans generated for the plurality of incoming queries. Based on the measured query locality, the method can adjust the size of the query execution plan cache.