Browse Prior Art Database

Method to Control Static SQL Statements for CPU Usage in a Database System Based on the Multiple Factors of Transactions

IP.com Disclosure Number: IPCOM000245378D
Publication Date: 2016-Mar-04
Document File: 5 page(s) / 119K

Publishing Venue

The IP.com Prior Art Database

Abstract

Disclosed is a cost effective search method to find the best match on multiple conditions for static Structure Query Language (SQL) statement governing. The method enables a quick memory access search by using an in-memory hash structure built with bloom filter maps.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 24% of the total text.

Page 01 of 5

Method to Control Static SQL Statements for CPU Usage in a Database System Based on the Multiple Factors of Transactions

For most database applications, the performance of Structured Query Language (SQL) statements is a high priority. Even with optimizing and tuning efforts, however, performance of SQL statements can be still negatively affected by many factors, such as a poorly designed index, an unanticipated access path change, or hardware degradation. A database resource-monitoring tool is often used in an effort to leverage the sharing environment in a high volume of data transactions.

Monitoring and restricting the Central Processing Unit (CPU) usage of SQL statements is one of many popular functions for balancing the CPU resource in a database system; it avoids unexpected high CPU consumption by a long-running SQL statement. Within a database system, the monitoring function should be supported so that the database system can effectively control an SQL statement by stopping its execution when it consumes CPU resources in excess of the CPU limit. This monitoring and controlling function of SQL statements in a database system called the governing function .

Multiple factors of a data transaction need to be considered to determine the CPU limits of SQL statements. SQL statements from OnLine Transaction Processing (OLTP) applications would be set to lower CPU limits, as OLTP workloads are characterized by many small transactions with an expectation of very quick response time from the user. Meanwhile, SQL statements from OnLine Analytical Processing (OLAP) applications or reporting applications would be set to higher CPU limits, as those applications are characterized by a few long-running queries that might consume more CPU resources and cause more contentions. An organization might allow different CPU limits for a

certain type of applications or a certain level of users based on its business priorities. Data transactions from different applications are usually identified by multiple conditions, such as executing user-id, application-package, application-collection-id and application-client-information, of a data transaction.

The use of multiple conditions of data transactions, however, leads to the overlapping set and superset-subset situations, where one or more conditions of a data transaction

can be met by multiple sets. The overlapping set and superset-subset situations require the best match search to find the most optimal set. The best match search is often performed by the exhaustive search. Some optimization techniques, such as sorting and hashing of each condition, can be used to avoid the exhaustive search. Even with sorting and hashing techniques, however, finding the best matching set based on all combinations of each search condition still requires the high cost of operation. This expensive search operation can cause an overhead for a database's governing function and increase the actual CPU usage for SQL stateme...