Generating database query plans

• US 8,312,007 B2
• Filed: 05/08/2008
• Issued: 11/13/2012
• Est. Priority Date: 05/08/2008
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bestgen, Robert J., Hu, Wei, Kethireddy, Shantan, Passe, Andrew P., Thiemann, Ulrich
• Primary Examiner(s): SOMERS, MARC S
• Application Number:
  US12/117,070
• Publication Number:
  US 20090281986A1
• Time in Patent Office: 1,650 Days
• Field of Search: 707/705, 707/713, 707/718, 707/719, 707/720, 707/721, 707/999.002
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y02D 10/00 Energy efficient computing, e.g. low power processors, power management or thermal management

Abstract

Embodiments of the invention provide techniques for optimizing database queries for energy efficiency. In general, a query optimizer is configured to compare energy requirements of query plans, and to select a query plan requiring minimal energy to execute. In one embodiment, the query optimizer may also compare time performance of the query plans, and may select a query plan by matching to a user preference for a relative priority between energy requirements and time performance.

Claims

1. A computer-implemented method, comprising:

receiving a database query;

estimating an energy consumption requirement for generating a new query plan adapted for executing the database query;

estimating an energy consumption saving resulting from executing the new query plan against a database instead of executing an existing query plan against the database, wherein the energy consumption saving comprises the difference between an amount of electrical energy required to execute the existing query plan and an amount of electrical energy required to execute the new query plan; and

upon determining that the energy consumption saving exceeds the energy consumption requirement, generating the new query plan.

2. The computer-implemented method of claim 1, wherein upon determining that the energy consumption savings exceeds the energy consumption requirement further comprises:

executing the new query plan; and

returning a set of query results.

3. The computer-implemented method of claim 1, further comprising:

otherwise, upon determining that the energy consumption saving does not exceed the energy consumption requirement;

executing the existing query plan; and

returning a set of query results.

4. The computer-implemented method of claim 1, wherein estimating the energy consumption requirement is based on at least one of:

(i) one or more predefined standards of energy consumption for generating various types of query plans, and (ii) historical data describing energy consumption during prior instances of generating new query plans.

5. The computer-implemented method of claim 1, wherein estimating the energy consumption saving is based on at least one of:

(i) one or more predefined standards of energy consumption for executing various query plans, (ii) historical data describing energy consumption during prior instances of query plan execution, and (iii) a predicted number of future instances of query plan execution.

6. The computer-implemented method of claim 1, wherein the energy consumption requirement comprises an amount of electrical energy required to spin one or more hard-disk drives required to generate the new query plan.

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

receiving a database query;

estimating an energy consumption requirement for generating a new query plan adapted for executing the database query;

estimating an energy consumption saving resulting from executing the new query plan against a database instead of executing an existing query plan against the database, wherein the energy consumption saving comprises the difference between an amount of electrical energy required to execute the existing query plan and an amount of electrical energy required to execute the new query plan; and

upon determining that the energy consumption saving exceeds the energy consumption requirement, generating the new query plan.

8. The computer readable storage medium of claim 7, wherein upon determining that the energy consumption savings exceeds the energy consumption requirement further comprises:

executing the new query plan; and

returning a set of query results.

9. The computer readable storage medium of claim 7, the operation further comprising:

otherwise, upon determining that the energy consumption saving does not exceed the energy consumption requirement;

executing the existing query plan; and

returning a set of query results.

10. The computer readable storage medium of claim 7, wherein estimating the energy consumption requirement is based on at least one of:

(i) one or more predefined standards of energy consumption for generating various types of query plans, and (ii) historical data describing energy consumption during prior instances of generating new query plans.

11. The computer readable storage medium of claim 7, wherein estimating the energy consumption saving is based on at least one of:

(i) one or more predefined standards of energy consumption for executing various query plans, (ii) historical data describing energy consumption during prior instances of query plan execution, and (iii) a predicted number of future instances of query plan execution.

12. The computer readable storage medium of claim 7, wherein the energy consumption requirement comprises an amount of electrical energy required to spin one or more hard-disk drives required to generate the new query plan.

13. A system, comprising:

a database;

a processor; and

a memory containing a program, which when executed by the processor is configured to perform an operation, comprising;

receiving a database query;

estimating an energy consumption requirement for generating a new query plan adapted for executing the database query;

estimating an energy consumption saving resulting from executing the new query plan against the database instead of executing an existing query plan against the database, wherein the energy consumption saving comprises the difference between an amount of electrical energy required to execute the existing query plan and an amount of electrical energy required to execute the new query plan; and

upon determining that the energy consumption saving exceeds the energy consumption requirement, generating the new query plan.

14. The system of claim 13, wherein upon determining that the energy consumption savings exceeds the energy consumption requirement further comprises:

executing the new query plan; and

returning a set of query results.

15. The system of claim 13, the operation further comprising:

otherwise, upon determining that the energy consumption saving does not exceed the energy consumption requirement;

executing the existing query plan; and

returning a set of query results.

16. The system of claim 13, wherein estimating the energy consumption requirement is based on at least one of:

(i) one or more predefined standards of energy consumption for generating various types of query plans, and (ii) historical data describing energy consumption during prior instances of generating new query plans.

17. The system of claim 13, wherein estimating the energy consumption saving is based on at least one of:

(i) one or more predefined standards of energy consumption for executing various query plans, (ii) historical data describing energy consumption during prior instances of query plan execution, and (iii) a predicted number of future instances of query plan execution.

18. The system of claim 13, wherein the energy consumption requirement comprises an amount of electrical energy required to spin one or more hard-disk drives required to generate the new query plan.