Dynamic Query Optimization

• US 20080052720A1
• Filed: 10/31/2007
• Published: 02/28/2008
• Est. Priority Date: 02/26/2004
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Majd, Mahdad, Ruhlow, Randy, Santosuosso, John, Barsness, Eric
• Granted Patent: US 8,122,010 B2
• Time in Patent Office: Days
• US Class Current: 718/104
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 9/50 Allocation of resources, e.g. of the central processing unit [CPU]
• CPC Class Codes: G06F 9/5072 Grid computing
• CPC Class Codes: G06F 9/5077 Logical partitioning of resources; Management or configuration of virtualized resources specific details on emulation or internal functioning of virtual machines G06F9/455

Abstract

Methods, systems, and computer program products for dynamically adjusting computer resources, as appropriate, in response to predictions of query runtimes as well as for rendering costs of the computer resources actually utilized, which costs are consistent with consumer demands.

Claims

1. A system comprising:

one or more processors;

a memory coupled to at least the one processor; and

,

a manager residing in the memory and executable by the at least one processor, wherein said manager;

(a) evaluates a runtime instance of a query during execution of said runtime instance for data relative to performance of said runtime instance with respect to a predefined query time boundary, said predefined query time boundary limiting a length of time in which said runtime instance of said query is allowed to complete runtime execution;

(b) dynamically predicts, using said data relative to performance of said runtime instance, a projected amount of computer resources required to complete execution of said runtime instance of said query within said predefined query time boundary; and

(c) uses the results of said dynamically predicting step to dynamically allocate an amount of computer resources to complete execution of said runtime instance of said query, said amount being an amount required to make the total amount of said computer resources allocated to complete execution of said runtime instance of said query equal to said projected amount of computer resources required to complete execution of said runtime instance of said query within said predefined query time boundary dynamically predicted by said dynamically predicting step.

2. The system recited in claim 1, wherein dynamic predicting includes the manager evaluating the query and the available computer resources to be allocated so that said runtime instance of said query executes within the predefined query time boundaries.

3. The system recited in claim 1, wherein the manager stops running of the query during running if the query runtime exceeds the predefined query time boundary.

4. The system recited in claim 1, wherein the manager renders costs for computer resources actually utilized in having execution of said runtime instance of said query complete within the predefined query time boundary.

5. The system recited in claim 1, wherein the manager further allocates/de-allocates resources that are provided by a networked computing grid and/or one or more processor partitions of the at least one processor

6. The system recited in claim 1, further comprising a user interface that allows a user establishing parameter values for the predicted allocation of the computer resources.

7. The system recited in claim 1, wherein the manager increases the allocated resources by predefined levels of increases in accordance with a user policy

8. A computer-implemented method in a system having at least one processor;

a memory coupled to the at least one processor, and a manager residing in the memory and being executable, the method comprising;

evaluating a runtime instance of a query during execution of said runtime instance for data relative to performance of said runtime instance with respect to a predefined query time boundary, said predefined query time boundary limiting a length of time in which said runtime instance of said query is allowed to complete runtime execution;

dynamically predicting, using said data relative to performance of said runtime instance, a projected amount of computer resources required to complete execution of said runtime instance of said query within said predefined query time boundary; and

responsive to said dynamically predicting step, dynamically allocating an amount of computer resources to complete execution of said runtime instance of said query, said amount being an amount required to make the total amount of said computer resources allocated to complete execution of said runtime instance of said query equal to said projected amount of computer resources required to complete execution of said runtime instance of said query within the predefined query time boundary dynamically predicted by said dynamically predicting step.

9. The method recited in claim 8, wherein dynamic predicting includes evaluating the query and the available computer resources

10. The method recited in claim 8, further comprising rendering costs to a customer for computer resources actually utilized in having execution of said runtime instance of said query complete within the predefined query time boundary

11. The method recited in claim 8, wherein the allocating of resources is provided by a networked computing grid and/or one or more processor partitions of the at least one processor.

12. The method recited in claim 8, further comprising allowing the utilization of a user interface for establishing parameter values for additional computer resources based on computer costs.

13. The method recited in claim 8, wherein the computer resources are allocated are appropriately increased by predefined levels.

14. The method recited in claim 8, wherein the query is prevented from running if the actual query running time exceeds the predefined query time boundary.

15. A computer program product for implementing query optimization in a computer system including a processor, said computer program product including instructions recorded on computer-readable recording media which when executed comprise the steps of:

evaluating a runtime instance of a query during execution of said runtime instance for data relative to performance of said runtime instance with respect to a predefined query time boundary, said predefined query time boundary limiting a length of time in which said runtime instance of said query is allowed to complete runtime execution;

dynamically predicting, using said data relative to performance of said runtime instance, a projected amount of computer resources required to complete execution of said runtime instance of said query within said predefined query time boundary; and

responsive to said dynamically predicting step, dynamically allocating an amount of computer resources to complete execution of said runtime instance of said query, said amount being an amount required to make the total amount of said computer resources allocated to complete execution of said runtime instance of said query equal to said projected amount of computer resources required to complete execution of said runtime instance of said query within the predefined query time boundary dynamically predicted by said dynamically predicting step.

16. The computer program product recited by claim 15, further comprising rendering costs to a customer for computer resources actually utilized in having execution of said runtime instance of said query complete within the predefined query time boundary.

17. The computer program product recited by claim 15, wherein the allocating of computer resources is from a networked computing grid and/or one or more additional processor partitions of the at least one processor, and any combination thereof.

18. The computer program product recited by claim 15, further comprising allowing the utilization of a graphical user interface for allowing a user to establish parameter values related to the costs of utilizing computer resources for having execution of said runtime instance of said query complete within the predefined query time boundary.