Database Access Metrics Using Logarithmic Counters for Index Advice
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing automated physical database tuning methods face challenges in collecting precise, compact, and fast workload execution statistics, leading to inaccurate advisor tool recommendations due to offline data collection or low-precision online tracking at page granularity.
Innovation Solution
The proposed solution involves tracking actual output cardinalities of selections at query execution time using logarithmic counters and stream-summary data structures to provide accurate workload execution statistics, enabling advisors to recommend index creation, data compression, buffer pool size adjustments, and table partitioning effectively.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If offline data collection is used to gather workload execution statistics, then memory overhead is reduced, but measurement precision and speed deteriorate due to delayed and low-precision tracking at page granularity
Solution Approach 1:
The patent transforms the counting mechanism from traditional linear counters to logarithmic counters, changing the parameter of how execution statistics are accumulated. This allows the system to track workload execution statistics with higher precision while using less memory, as logarithmic counters require fewer bits to represent the same range of values compared to linear counters
Solution Approach 2:
The patent introduces stream-summary data structures as an intermediary between the database workload and the advisory tools. These data structures aggregate and summarize workload execution statistics in real-time, providing precise measurements without requiring large memory overhead, thus mediating between the need for precision and memory constraints
2Measurement precision
If traditional linear counters are used to track workload execution statistics, then measurement precision is maintained, but device complexity and memory overhead increase
Solution Approach 1:
The patent changes the fundamental parameter of the counting system from linear to logarithmic scale. This transformation reduces the memory requirements and system complexity while preserving measurement precision, as logarithmic counters can represent a wide range of values with fewer bits, thereby simplifying the overall counter tracking system
Solution Approach 2:
The patent segments the workload execution statistics tracking into multiple dimensions using stream-summary data structures, organizing counts by query patterns, execution frequencies, and other categorical attributes. This segmentation allows precise measurement across different workload characteristics while keeping each individual counter compact, reducing overall system complexity
3Measurement precision
If online tracking at row granularity is implemented, then measurement precision improves, but productivity and runtime processing overhead worsen
Solution Approach 1:
The patent merges the workload execution statistics tracking with the normal query execution flow by integrating logarithmic counters and stream-summary data structures into the query processing pipeline. This merging allows precise online tracking without requiring separate processing steps, thus maintaining query execution speed while improving measurement precision
Solution Approach 2:
The patent changes the aggregation parameter from row-level to query-pattern-level using stream-summary data structures. Instead of tracking each individual row access, the system aggregates statistics at the query pattern level, reducing runtime processing overhead while maintaining sufficient precision for advisory tool recommendations
4Measurement precision
If precise workload execution statistics are collected with high memory overhead, then measurement precision improves, but loss of substance increases due to excessive memory consumption
Solution Approach 1:
The patent applies parameter changes by transitioning from linear to logarithmic counting, which fundamentally alters the memory consumption characteristics. Logarithmic counters require O(log n) bits instead of O(n) bits to represent the same counting range, achieving precise workload execution statistics with significantly reduced memory resource consumption
Solution Approach 2:
The patent uses stream-summary data structures that create compact summaries of workload execution patterns rather than storing complete detailed records. This copying approach captures the essential statistical information needed for precision while discarding redundant details, thereby reducing memory overhead without sacrificing 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.