Database Query Resource Management via SQL Condition Policies
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In database environments, managing computing resources effectively is challenging, especially when dealing with a mix of recent and historical data, as existing tools like Oracle's ILM and ASM may not adequately prevent performance reduction from ad-hoc queries accessing old data, and moving data to archival storage is impractical in some setups.
Innovation Solution
A resource allocation system that includes a user interface, a database server, and modules to monitor database queries and automatically adjust computing resources based on query characteristics, such as the SQL WHERE clause, to selectively allocate CPU time and I/O resources, preventing performance degradation by associating specific resource management policies with SQL statements.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If Oracle's ILM and ASM tools are used to organize and separate recent data from older data, then data-storage resources are managed, but ad-hoc queries can still access historical data and reduce overall database performance
Solution Approach 1:
The patent applies local quality by assigning different resource allocation policies to different parts of the database system. Specifically, it monitors SQL SELECT statements and identifies those querying historical data, then applies restrictive resource policies only to those specific queries while leaving other queries unaffected. This selective approach prevents ad-hoc queries from degrading overall performance without impacting legitimate access to historical data.
Solution Approach 2:
The patent introduces an intermediary mechanism between the database server and the physical data storage. This intermediary layer includes functionality to monitor SQL statements, identify queries accessing historical data, and dynamically adjust computing resource allocation based on the query type. This mediator prevents harmful ad-hoc queries from consuming excessive resources while allowing legitimate queries to proceed normally.
2Quantity of substance
If data is moved to archival storage, then storage resources are optimized, but in some setups moving data to archival storage is impractical
Solution Approach 1:
Instead of physically moving data to archival storage, the patent creates a logical copy or representation of the data access patterns. It monitors and analyzes SQL SELECT statements to identify queries accessing historical data, then applies resource management policies based on this analysis. This virtual copying approach achieves resource optimization without the complexity of physical data migration.
Solution Approach 2:
The patent changes the parameter of resource allocation rather than changing the physical location of data. It dynamically adjusts computing resource parameters (CPU time, I/O resources) based on the characteristics of the query and the data being accessed. This allows the system to optimize resource usage for historical data queries without requiring data to be moved to archival storage.
3Productivity
If computing resources are allocated to all database queries equally, then simplicity is maintained, but performance degradation occurs when ad-hoc queries access historical data
Solution Approach 1:
The patent implements a self-service mechanism where the database system automatically monitors its own queries, identifies ad-hoc queries accessing historical data, and adjusts resource allocation without external intervention. The functionality to monitor SQL statements and the resource manager work together autonomously to detect problematic queries and apply appropriate resource policies, reducing the need for manual resource management while improving overall efficiency.
Solution Approach 2:
The patent establishes a feedback loop where the system continuously monitors database queries, analyzes their characteristics, and adjusts resource allocation based on the findings. The resource manager receives feedback from the monitoring functionality about which queries are accessing historical data, and dynamically adjusts computing resource allocation in response. This closed-loop control improves database efficiency while managing complexity through automation.
Data Source
AI summary
A resource allocation system for a database environment. In a specific embodiment, the resource allocation system is adapted to implement a method that includes receiving a first signal from a user input device to identify a SQL SELECT statement to be used in a future database query. The system receives a second signal from the user input device to determine a resource to be used when the SELECT statement is executed. The system then stores, in a storage device, an indication of the resource in association with the SELECT statement so that when the SELECT statement is executed the resource is used.