Autonomous Cache Table Tuning for OLAP Query Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Hierarchical queries in Online Analytical Processing (OLAP) are computationally expensive due to the aggregation of fact data across multiple levels, which varies with each query and is not efficiently addressed by existing caching methods.
Innovation Solution
An autonomous system for creating and tuning auto-cache tables that analyze query logs to identify frequently used dimension level combinations, aggregating resources for the most expensive queries and dynamically managing cache storage to improve performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If caching is implemented for views, then query performance is improved, but the complexity of managing cache storage and tuning is increased
Solution Approach 1:
The system implements autonomous caching where the database engine automatically creates, maintains, and tunes cache tables without requiring manual intervention. The engine monitors query patterns, identifies candidates for caching, creates appropriate cache tables, and manages their lifecycle automatically, allowing the system to serve itself rather than requiring external management overhead
Solution Approach 2:
The system dynamically adjusts caching parameters based on query cost analysis. It evaluates query patterns and automatically modifies cache storage parameters, such as determining which views to cache and how much storage to allocate, by changing these parameters in response to observed query characteristics rather than using fixed manual configurations
2Productivity
If manual cache table creation is performed, then query performance can be improved, but the time and resources required for setup and maintenance are increased
Solution Approach 1:
The system performs preliminary analysis of query patterns to identify which views would benefit from caching before actual queries are executed. By pre-analyzing query costs and patterns, the system prepares cache tables in advance based on predicted query workloads, so that when queries are actually executed, the caching infrastructure is already in place and optimized
Solution Approach 2:
The database engine autonomously monitors its own query workload, identifies candidates for caching, and creates cache tables without external intervention. This self-service approach eliminates the need for manual setup and ongoing maintenance by database administrators, as the system automatically adapts to changing query patterns and manages its own caching infrastructure
Data Source
AI summary
Techniques are provided for autonomous caching of hierarchical data. In one technique, query log data is stored that comprises multiple entries, each entry (a) corresponding to a different instance of a query that was executed against a database and (b) indicating a tuple (or level grouping) comprising a set of hierarchy levels corresponding to a set of dimensions. Multiple tuples indicated in the query log data are identified. For each tuple: (1) a set of entries that indicate the tuple is identified and the set of entries is associated with the tuple; (2) aggregated performance data is generated for the tuple based on performance data associated with each entry in the set of entries that is associated with the tuple; and (3) based on the aggregated performance data, it is determined whether to create a new auto-cache table or to delete an existing auto-cache table associated with the tuple.