Database Access Metrics Using Logarithmic Counters for Index Advisors
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current automated physical database tuning methods face challenges in collecting precise, compact, and fast workload execution statistics, leading to inaccurate advisor tool configurations and inefficient database performance optimization.
Innovation Solution
The proposed solution involves tracking logarithmic counters for index advisors, maintaining sequential and random access counters for data compression advisors, using constant-time page access counter updates for buffer pool size advisors, and combining block access data with stream-summary data for table partitioning advisors to provide accurate and efficient workload execution statistics.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional access counters are used for index advisor, then memory consumption is high, but measurement precision is insufficient
Solution Approach 1:
The patent segments the counter collection into multiple logarithmic counters (e.g., 64 counters) that collectively represent the full range of access frequencies. Instead of using one large counter that consumes excessive memory, the system divides the frequency range into logarithmic intervals and uses multiple smaller counters to represent these intervals, achieving both precision and memory efficiency.
Solution Approach 2:
The patent changes the parameter representation from linear to logarithmic scale. By using logarithmic counters that count in powers of 2 (2^0, 2^1, 2^2, ..., 2^63), the system efficiently represents a wide range of access frequencies with limited memory resources, transforming the parameter measurement approach to optimize the precision-memory tradeoff.
2Reliability
If detailed workload execution statistics are collected, then advisor tool effectiveness is improved, but runtime overhead increases
Solution Approach 1:
The patent implements self-service through automatic counter management where the system automatically creates, updates, and manages logarithmic counters for each attribute without manual intervention. The counter collection mechanism operates autonomously during query execution, updating counters in constant time O(1) without requiring external management or complex processing, thereby reducing runtime overhead while maintaining detailed statistics.
Solution Approach 2:
The patent performs preliminary action by pre-allocating and pre-configuring the logarithmic counter structure before query execution begins. The counter collection framework is established in advance with predefined logarithmic intervals and data structures ready to capture workload statistics, eliminating the need for complex real-time calculations and reducing runtime processing overhead.
3Measurement precision
If comprehensive access patterns are tracked, then database optimization accuracy is improved, but device complexity increases
Solution Approach 1:
The patent simplifies device complexity by changing the parameter tracking approach from monitoring every individual access event to using logarithmic counters that aggregate accesses in power-of-2 intervals. This parameter transformation reduces the complexity of counter management while maintaining comprehensive access pattern tracking accuracy, as the logarithmic structure naturally handles the aggregation and classification of diverse access patterns.
Solution Approach 2:
The patent applies inversion by reversing the traditional approach: instead of creating one counter per possible access frequency value (which would be extremely complex), the system creates a fixed small number of logarithmic counters (e.g., 64) that can represent any access frequency through their logarithmic scaling. This inverted approach dramatically reduces device complexity while maintaining measurement precision.
Data Source
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.