Automated Index Creation via Plan Cache Frequency Analysis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems lack an efficient mechanism for optimizing frequently executed queries due to limited costing contexts in commercial optimizers, which fail to account for query frequency over time, leading to suboptimal index creation and resource-intensive index tuning tools that are not active during regular query processing.
Innovation Solution
The system utilizes standard optimizer strategies for dynamic index creation, leveraging query frequency information from the system's request cache to determine beneficial indexes for query performance, and employs an active system management module to automate index creation and management based on historical and anticipated query frequencies.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If an index is created dynamically prior to a retrieve or join operation, then the speed of that specific operation is improved, but the overhead cost of creating the index outweighs the resulting cost improvements
Solution Approach 1:
The system performs preliminary analysis of query frequency from the plan cache before creating indexes. By identifying frequently executed queries in advance and creating indexes proactively based on frequency thresholds, the system ensures that indexes exist before they are needed, eliminating the overhead of dynamic index creation during query execution while maintaining speed improvements.
2Productivity
If index tuning tools are used to examine queries and automatically recommend indexes, then query performance optimization is improved, but the tools are very resource intensive and not active during regular query processing
Solution Approach 1:
The system enables the database optimizer to automatically identify candidate indexes for frequently executed queries using information already available in the plan cache. This self-service approach eliminates the need for separate, resource-intensive index tuning tools by leveraging existing query frequency data and optimizer capabilities to make automated index creation decisions during normal operation.
Solution Approach 2:
The system repurposes the plan cache, which already stores query execution information, to also track query frequency for index creation decisions. By making the plan cache serve dual purposes (performance optimization and frequency tracking), the system avoids the overhead of separate monitoring mechanisms and integrates index optimization into the existing query processing infrastructure.
3Ease of operation
If the costing context of optimizers is limited to a single SQL request, then the optimizer can make quick decisions, but it prevents factoring in a query's actual or expected frequency over a period of time
Solution Approach 1:
The system adds a temporal dimension to the optimizer's costing context by incorporating query frequency information from the plan cache. Instead of evaluating only single-query costs, the system considers cumulative execution frequency as an additional dimension, allowing it to make informed decisions about index creation that account for long-term performance benefits while maintaining quick decision-making capabilities.
Data Source
AI summary
A system, method, and computer-readable medium that facilitate the optimization of frequently executed queries via automated index creation are provided. Standard optimizer strategies for dynamic index creation are utilized with query frequency information taken from a system's request cache. The described mechanisms provide a process of determining what indexes would be beneficial to query performance based on decisions and information from the optimizer that is ultimately responsible for the choice of index usage. Further, the plan cache is utilized in a manner that provides reliable information about the historical and anticipated frequency of specific queries.