Energy monetary cost aware query optimization

• US 8,447,772 B2
• Filed: 06/23/2010
• Issued: 05/21/2013
• Est. Priority Date: 06/23/2010
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Miller, Samuel J., Tenner, Jeffrey W., Williams, Alwood P. III, Carston, Lynnette E., Rohret, Gary E.
• Primary Examiner(s): VO, TRUONG V
• Application Number:
  US12/821,453
• Publication Number:
  US 20110320434A1
• Time in Patent Office: 1,063 Days
• Field of Search: 707/759, 707/713, 707/706, 707/736, 707/809
• US Class Current: 707/759
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Embodiments of the invention provide a method of optimizing a query, including determining an execution plan for use in executing the query, based on a monetary cost of the energy needed to execute the query. A query optimization component may determine a plurality of execution plans for the query. The query optimization component may then select one of the plans to use in executing the query based on the monetary cost calculated for the plan.

Claims

1. A method of optimizing a query, comprising:

determining a plurality of execution plans for executing a query, wherein each of the plurality of execution plans represents an alternative way of executing the query relative to the other execution plans in the plurality of execution plans, and wherein each of the plurality of execution plans includes a plurality of operations to be performed in executing the query and a schedule in which to perform operations in the plurality of operations;

for each of the plurality of execution plans;

determining a power consumption value for the execution plan, wherein the power consumption value approximates an amount of power that would be consumed by executing the query using the respective execution plan; and

determining a monetary cost for executing the query according to the execution plan, based on one or more pricing values and the determined power consumption value for the execution plan;

selecting a first execution plan from the plurality of execution plans to use in executing the query, based on the determined monetary cost for the first execution plan; and

executing the query according to the first execution plan, wherein an entirety of the query is executed once the first execution plan is completed.

2. The method of claim 1, wherein the first execution plan includes a delay time value, and wherein execution of at least a portion of the query is delayed for an amount of time specified in the delay time value.

3. The method of claim 1, wherein the power consumption value determined for the first execution plan is greater than the power consumption value for at least one other execution plan in the plurality of execution plans.

4. The method of claim 1, The method of claim 1, wherein the first execution plan includes using additional processing resources in order to expedite execution of the query.

5. The method of claim 1, wherein the one or more pricing values specify power usage prices at one or more different times.

6. The method of claim 1, wherein the received query includes a completion time value that specifies a time by which execution of the query must be completed, and wherein executing the query according to the first execution plan finishes before the complete time value included in the query.

7. A computer-readable storage medium containing a program which, when executed, performs an operation, comprising:

determining a plurality of execution plans for executing a query, wherein each of the plurality of execution plans represents an alternative way of executing the query relative to the other execution plans in the plurality of execution plans, and wherein each of the plurality of execution plans includes a plurality of operations to be performed in executing the query and a schedule in which to perform operations in the plurality of operations;

for each of the plurality of execution plans;

determining a power consumption value for the execution plan, wherein the power consumption value approximates an amount of power that would be consumed by executing the query using the respective execution plan; and

determining a monetary cost for executing the query according to the execution plan, based on one or more pricing values and the determined power consumption value for the execution plan;

selecting a first execution plan from the plurality of execution plans to use in executing the query, wherein the first execution plan is selected based on the determined monetary cost for the first execution plan; and

executing the query according to the first execution plan, wherein an entirety of the query is executed once the first execution plan is completed.

8. The computer-readable storage medium of claim 7, wherein the first execution plan includes a delay time value, and wherein execution of at least a portion of the query is delayed for an amount of time specified in the delay time value.

9. The computer-readable storage medium of claim 7, wherein the power consumption value determined for the first execution plan is greater than the power consumption value for at least one other execution plan in the plurality of execution plans.

10. The computer-readable storage medium of claim 7, wherein the first execution plan includes using additional processing resources in order to expedite execution of the query.

11. The computer-readable storage medium of claim 7, wherein the one or more pricing values specify power usage prices at one or more different times.

12. The computer-readable storage medium of claim 7, wherein the received query includes a completion time value that specifies a time by which execution of the query must be completed, and wherein executing the query according to the first execution plan finishes before the complete time value included in the query.

13. A system, comprising:

a computer processor; and

a memory containing a program that, when executed on the computer processor, performs an operation for optimizing a query, comprising;

determining a plurality of execution plans for executing a query, wherein each of the plurality of execution plans represents an alternative way of executing the query relative to the other execution plans in the plurality of execution plans, and wherein each of the plurality of execution plans includes a plurality of operations to be performed in executing the query and a schedule in which to perform operations in the plurality of operations;

for each of the plurality of execution plans;

determining a power consumption value for the execution plan, wherein the power consumption value approximates an amount of power that would be consumed by executing the query using the respective execution plan; and

determining a monetary cost for executing the query according to the execution plan, based on one or more pricing values and the determined power consumption value for the execution plan;

selecting a first execution plan from the plurality of execution plans to use in executing the query, wherein the first execution plan is selected based on the determined monetary cost for the first execution plan; and

executing the query according to the first execution plan, wherein an entirety of the query is executed once the first execution plan is completed.

14. The system of claim 13, wherein the first execution plan includes a delay time value, and wherein execution of at least a portion of the query is delayed for an amount of time specified in the delay time value.

15. The system of claim 13, wherein the power consumption value determined for the first execution plan is greater than the power consumption value for at least one other execution plan in the plurality of execution plans.

16. The system of claim 13, wherein the first execution plan includes using additional processing resources in order to expedite execution of the query.

17. The system of claim 13, wherein the one or more pricing values specify power usage prices at one or more different times.

18. The system of claim 13, wherein the received query includes a completion time value that specifies a time by which execution of the query must be completed, and wherein executing the query according to the first execution plan finishes before the complete time value included in the query.