Accurate and timely enforcement of system resource allocation rules

摘要

A computer-implemented method, apparatus and article of manufacture for optimizing a database query. A query execution plan for the database query is generated using estimated cost information; one or more steps of the query execution plan are executed to retrieve data from a database stored on the computer system. Actual cost information is generated for each of the executed steps, and the estimated cost information is re-calculated using the actual cost information. One or more resource allocation rules defined on one or more steps of the query execution plan are executed, based on the estimated cost information, wherein the resource allocation rules include one or more defined actions. The estimated cost information may be re-calculated using the actual cost information when confidence in the estimated cost information is low, but the estimated cost information may not be re-calculated when confidence in the estimated cost information is high. In addition, the estimated cost information may be re-calculated using the actual cost information, only when the step has one or more resource allocation rules defined thereon.

主权项

1. A computer-implemented method for optimizing a database query in a computer system, comprising:
monitoring, in the computer system, resource usage of one or more resources of the computer system, to identify resource bottlenecks and other conditions in the computer system, and to perform one or more resource allocation rules of a rule set to define the way in which the computer system operates, wherein each of the resource allocation rules includes a working value that indicates whether the resource allocation rule is active or not; generating, in the computer system, a query execution plan for the database query using estimated cost information stored in a cost model, wherein the cost model is modified using one or more condition codes representative of the monitored resource usage, to manage the resource bottlenecks and other conditions in the computer system, such that the estimated cost information associated with the resource is weighted in response to the condition code, so that the generated query execution plan uses less of the resource, wherein each of the condition codes is assigned a timestamp to indicate when the condition code expires; executing, in the computer system, one or more steps of the query execution plan to retrieve data from a database stored on the computer system; generating, in the computer system, actual cost information for each of the executed steps; re-calculating, in the computer system, the estimated cost information using the actual cost information based on confidence in the estimated cost information; and executing, in the computer system, one or more of the resource allocation rules based on the re-calculated estimated cost information, wherein the resource allocation rules are defined to control resource allocation for the query execution plan and each of the resource allocation rules defined for a current step of the query execution plan is executed, before a next step of the query execution plan is executed, such that, when the resource allocation rules are violated during the execution of the current step of the query execution plan, the computer system automatically performs a configured corrective action to control resource usage by the query execution plan, and execution of the next step of the query execution plan is delayed until after all of the resource allocation rules defined for the current step of the query execution plan have been executed and their resulting configured corrective actions have been completed, thereby preventing inefficient database queries from consuming excessive resources of the computer system, while managing user expectations and concurrently smoothing peaks and valleys of usage.