Database Access Metrics for Low-Overhead Index Advisor Tuning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing automated physical database tuning methods face challenges in accurately and efficiently collecting workload execution statistics with low runtime and memory overhead, leading to imprecise and slow advisor tool performance.
Innovation Solution
Implementing precise, compact, and fast workload execution statistics collection methods, including lazy counters for index advisors, dual counters for data compression advisors, efficient page access tracking for buffer pool size advisors, and combined block and stream-summary data for table partitioning advisors, to enhance advisor tool effectiveness.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional workload execution statistics collection methods are used, then advisor tools can provide database tuning recommendations, but the memory overhead and processing time become excessive
Solution Approach 1:
The patent segments the statistics collection by creating separate counter types (lazy counters, dual counters, page access counters) for different advisor needs, allowing selective tracking that reduces overall memory overhead while maintaining necessary precision for each advisor type
Solution Approach 2:
The patent implements partial tracking by using lazy counters that only collect statistics when actually needed by the index advisor, avoiding unnecessary memory allocation and processing for statistics that would not be used
2Measurement precision
If comprehensive workload statistics are collected for all database operations, then advisor accuracy improves, but processing overhead increases
Solution Approach 1:
The patent divides statistics collection into specialized counter mechanisms (lazy counters for index advisors, dual counters for compression advisors, page access counters for buffer pool advisors) that only process relevant operations, reducing overall processing overhead while maintaining accuracy for each specific advisor type
Solution Approach 2:
The lazy counter mechanism allows the system to self-regulate by only collecting statistics when actually needed by the advisor, avoiding unnecessary processing overhead for operations that do not require detailed tracking
3Measurement precision
If detailed access patterns are tracked for every database attribute, then table partitioning recommendations improve, but the complexity of the tracking system increases
Solution Approach 1:
The patent merges block counters and stream-summary data structures to efficiently track access patterns, combining the benefits of precise counting with compact representation to reduce system complexity while maintaining detection accuracy
Data Source
Figure 1
Figure 2A
Figure 2B~2C
AI summary
The present disclosure involves systems, software, and computer implemented methods for improved design and implementation of data access metrics for automated physical database design. An example method includes identifying a database workload for which index advisor access counters are to be tracked. Each SQL statement in the database workload is executed. For each SQL statement, attribute sets are determined for which a selection predicate filters a result for an SQL statement. An output cardinality of each selection predicate is determined. A logarithmic counter for an attribute set corresponding to the selection predicate is determined based on the output cardinality of the selection predicate. The determined logarithmic counter is incremented. Respective values for logarithmic counters of the determined attributes are provided to an index advisor. The index advisor determines attribute sets for which to propose an index based on the logarithmic counters of the respective attribute sets.