QUERY EXECUTION PLAN EFFICIENCY IN A DATABASE MANAGEMENT SYSTEM

• US 20100114868A1
• Filed: 10/21/2008
• Published: 05/06/2010
• Est. Priority Date: 10/21/2008
• Status: Active Grant

• Current Assignee: Alibaba Group Holding Ltd.
• Original Assignee: International Business Machines Corporation
• Inventors: Fuh, You-Chin, Purcell, Terence P., Beavin, Thomas A., Bossman, Patrick D., Pannu, Adarsh R.
• Granted Patent: US 8,060,495 B2
• Time in Patent Office: Days
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

An apparatus, system, and method are disclosed for improving query execution plan efficiency in a database management system. The present invention binds both static database queries and dynamic database queries to new query execution plans (QEPs) that produce the same result set as a former QEP bound to the database queries. Next, the present invention identifies one of the former QEPs and the new QEP as an active QEP and automatically collects execution statistics for one of the former QEPs and the new QEP. Finally, the present invention automatically selects one of the former QEPs and the new QEP as the active QEP in response to completion of the automatic collection of execution statistics, the active QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the active QEP selected based on the execution statistics.

Claims

1. a method for improving query execution plan efficiency in a database:

management system, the method comprising;

receiving a qualified database query, the database query comprising one of a static database query and a dynamic database query;

retrieving a stored query execution plan (QEP) for the database query in response to a QEP repository associating the database query with an active QEP;

generating a new QEP for the database query in response the QEP repository failing to associate the database query with an active QEP, and storing the new QEP in the QEP repository, designating the new QEP as the active QEP, binding the active QEP to the database query; and

satisfying the database query by implementing the active QEP.

2. The method claim 1, wherein the QEP repository is configured to store the active QEP and at least one inactive QEP, the method further comprising rebinding one of the at least one inactive QEPs stored in the QEP repository to the database query, and designating the bound inactive QEP as the active QEP in response to a user input.

3. The method of claim 2, wherein the database query further comprises a hint that guides a QEP generation module in generating the new QEP.

4. The method of claim 2, further comprising:

comparing the new QEP and one of the at least one inactive QEPs to produce comparison results;

presenting the comparison results to a user; and

marking one of the at least one inactive QEPs as the active QEP in response to a user input.

5. The method of claim 2, wherein each QEP in the QEP repository comprises an activation timestamp, the method further comprising rebinding the database query to an inactive QEP in the QEP repository having a different timestamp than the currently active QEP, in response to performance criteria.

6. The method of claim 1, wherein the database query comprises a dynamic database query, the method further comprising

transforming the dynamic database query into a hash key using a hash function;

and

indexing the hash key in a hash table comprising one or more QEPs associated with dynamic database queries.

7. The method of claim 1, wherein the QEP repository comprises one or more of stored inactive QEPs, active QEPs, executable forms, timestamps, query metadata, user supplied hints, and execution statistics.

8. The method of claim 1, wherein the database query comprises at least one parameter in a predicate of the database query, the method further comprising binding a plurality of QEPs to the database query, such that each QEP in the plurality of QEPs is bound to the database query for a predetermined set of values of the at least one parameter in the database query.

9. The method of claim 8, wherein the plurality of QEPs are bound to the database query in response to a user input.

10. The method of claim 1, further comprising:

collecting execution statistics for the active QEP and a user-selected stored inactive QEPs, in response to user-defined criteria;

storing the execution statistics in the QEP repository; and

presenting the collected execution statistics to the user.

11. The method of claim 1, further comprising:

receiving a revert command from a user; and

automatically rebinding the database query such that an inactive QEP last designated as an active QEP becomes the active QEP in response to the receiving the revert command.

12. A system to improve query execution plan efficiency in a database management system, the system comprising:

a processor;

a data storage device for storing data, in communication with the processor;

a memory in communication with the processor, the memory comprising,

a receiver module configured to receive a qualified database query, the database query comprising one of a static database query and a dynamic database query;

a retriever module configured to retrieve a stored query execution plan (QEP) for the database query in response a QEP repository associating the database query with an active QEP;

a generation module configured to generate a new QEP for the database query in response the QEP repository failing to associate the database query with an active QEP, and storing the new QEP in the QEP repository, and designating the new QEP as the active QEP;

an execution module configured to bind the active QEP to the database query; and

a selector module configured to select one of one or more inactive QEPs stored in the QEP repository as the active QEP in response to a user input, the inactive QEPs each formerly bound to the database query.

13. The system of claim 13, further comprising a statistics module configured to:

collect execution statistics for one or more of the active QEPs and one or more stored QEPs;

store the execution statistics in the QEP repository; and

expose the user to the collected execution statistics.

14. The system of claim 13, wherein a plurality of QEPs are bound to the database query in response to a user input.

15. The system of claim 13, further comprising an acceptor module configured to accept a user instruction to bind the database query to a QEP in the QEP repository that was active on a user-specified date.

16. The system of claim 13, further comprising a swap module configured to:

receive a revert command from a user; and

automatically swap the QEP identified as an active QEP with an inactive QEP that was last designated as an active QEP in response to the receiving the revert command.

17. An apparatus for improving query execution plan efficiency in a database management system, the apparatus comprising:

a receiver module configured to receive a qualified database query, the database query comprising one of a static database query and a dynamic database query;

a retriever module configured to retrieve a stored query execution plan (QEP) for the database query in response a QEP repository associating the database query with an active QEP;

a generation module configured to generate a new QEP for the database query in response the QEP repository failing to associate the database query with an active QEP, and storing the new QEP in the QEP repository, and designating the new QEP as the active QEP; and

a binder module configured to bind the database query to the active QEP.

18. A computer program product comprising a computer readable medium having computer usable program code executable to perform operations for improving query execution plan efficiency in a database management system, the operations of the computer program product comprising:

receiving a qualified database query, the database query comprising one of a static database query and a dynamic database query;

retrieving a stored query execution plan (QEP) for the database query in response a QEP repository associating the database query with an active QEP;

generating a new QEP for the database query in response the QEP repository failing to associate the database query with an active QEP, and storing the new QEP in the QEP repository, and designating the new QEP as the active QEP; and

binding the active QEP to the database query.

19. The computer program product of claim 18, further comprising:

retrieving execution statistics for one or more of the active QEP, the new QEP and one or more inactive QEPs; and

comparing the execution statistics.

20. The computer program product of claim 18, further comprising binding one or more of an inactive QEP and a new QEP to the database query in response to a user input.