Apparatus, system, and method for ensuring query execution plan stability in a database management system

• US 7,644,063 B2
• Filed: 08/17/2007
• Issued: 01/05/2010
• Est. Priority Date: 08/17/2007
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Campbell, John J., Bower, Frank D., Purcell, Terrence P., Fuh, You-Chin, Bossman, Patrick D., Pannu, Adarsh R.
• Primary Examiner(s): Cottingham; John R.
• Assistant Examiner(s): Arjomandi; Noosha
• Application Number:
  US11/840,703
• Publication Number:
  US 20090049012A1
• Time in Patent Office: 872 Days
• Field of Search: None
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24549 Run-time optimisation

Abstract

An apparatus, system, and method are disclosed for ensuring query execution plan stability in a database management system. The present invention binds a static database query to a new query execution plan (QEP) that produces the same result set as an original QEP bound to the static database query. Next, the present invention identifies one of the original QEP and the new QEP as a primary QEP and automatically collects execution statistics for the original QEP and the new QEP. Finally, the present invention automatically selects one of the original QEP and the new QEP as the primary QEP in response to completion of the automatic collection of execution statistics, the primary QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the primary QEP selected based on the execution statistics.

Claims

1. A computer program product comprising a computer readable medium having computer usable program code executable to perform operations for ensuring query execution plan stability in a database management system, the operations of the computer program product when performs by a processor and a memory comprises:

binding a static database query to a new query execution plan (QEP) that produces the same result set as an original QEP bound to the static database query;

identifying one of the original QEP and the new QEP as a primary QEP;

automatically collecting execution statistics for the original QEP and the new QEP;

automatically selecting one of the original QEP and the new QEP as the primary QEP in response to completion of the automatic collection of execution statistics, the primary QEP selected such that the automatically selected QEP provides optimal execution performance based on performance criteria in comparison to the automatically unselected QEP, the primary QEP selected based on the execution statistics; and

generating a report for a user, the report comprising one or more or execution statistics for the original QEP, execution statistics for the new QEP, the static database query, an identifier of the static database query, an identifier of the QEP automatically selected as the primary QEP, an identifier of the QEP automatically selected as the secondary QEP, a state indicator describing the state of the status indicator; and

providing the report to the user in response to a user command.

2. The computer program product of claim 1, further comprising,

associating a status indicator with the bound static database query, the original QEP, and the new QEP;

wherein automatically collecting execution statistics further comprises,

executing the secondary QEP for a first request to execute the bound static database query, the secondary QEP selected based on the status indicator;

collecting execution statistics during execution of the executed QEP;

executing the primary QEP in response to a second request to execute the bound static database query the primary QEP selected based on the status indicator; and

collecting execution statistics during execution of the primary QEP for the second request.

3. The computer program product of claim 2, wherein the status indicator assumes one of three states, the states comprising settled, secondary QEP unsettled, and primary QEP unsettled.

4. The computer program product of claim 1, further comprising,

storing the new QEP for the static database query;

identifying the new QEP as the primary QEP;

determining that the static database query includes a stored original QEP;

identifying the stored original QEP as the secondary QEP in response to the static database query including a stored original QEP;

setting a status indicator to a state that triggers comparison of execution statistics for the primary QEP and the secondary QEP, in response to the primary QEP and the secondary QEP being different.

5. The computer program product of claim 4, further comprising,

comparing the primary QEP and the secondary QEP;

determining that the primary QEP and the secondary QEP are the same; and

setting the status indicator to a state that avoids comparison of execution statistics for the primary QEP and the secondary QEP in response to determining that the primary QEP and the secondary QEP are the same.

6. The computer program product of claim 1, further comprising using the primary QEP to satisfy a request to execute the bound static database query in response to a status indicator set to a state that avoids comparison of execution statistics for the primary QEP and one or more other QEPs.

7. The computer program product of claim 1, wherein the execution statistics are selected from the group consisting of whether the query completed execution, the I/O profile for the QEP, the QEP execution time, the number of rows referenced, the number of rows returned, and a timestamp for when the QEP was executed.

8. The computer program product of claim 1, further comprising:

receiving a revert command from a user in response to automatic selection of one of the original QEP and the new QEP as the primary QEP; and

swapping the QEP identified as the primary QEP with the QEP identified as the secondary QEP in response to the revert command.

9. The computer program product of claim 8, further comprising setting a status indicator associated with the bound static database query, the primary QEP, and the secondary QEP to a state that triggers comparison of execution statistics for the primary QEP and the secondary QEP.

10. A computer program product comprising a computer readable medium having computer usable program code executable to perform operations for ensuring query execution plan stability in a database management system, the operations of the computer program product when performs by a processor and a memory comprises:

receiving a new query execution plan (QEP) that produces the same result set as an original QEP bound to a static database query;

binding the static database query to the new QEP and to the original QEP;

identifying the new QEP as a primary QEP;

associating a status indicator with the bound static database query, the primary QEP, and the secondary QEP;

setting the status indicator to indicate the secondary QEP is unsettled;

executing the secondary QEP in response to a first request to execute the bound static database query and the status indicator indicating that the primary QEP and secondary QEP are not settled;

collecting execution statistics during execution of the secondary QEP;

setting the status indicator to indicate the primary QEP is unsettled and send a response to the first request;

executing the primary QEP in response to a second request to execute the bound static database query and the status indicator indicating the primary QEP is unsettled;

collecting execution statistics during execution of the primary QEP;

determining whether the primary QEP or the secondary QEP provides optimal execution performance based on the collected execution statistics and on performance criteria;

automatically swapping a QEP of the primary QEP with a QEP of the secondary QEP in response to the QEP associated with the secondary QEP providing more optimal results than a QEP associated with the primary QEP; and

setting the status indicator to indicate that the primary QEP and secondary QEP are settled and send a response to the second request.

11. The computer program product of claim 10, wherein the status indicator assumes one of three states, the states comprising settled, secondary QEP unsettled, and primary QEP unsettled.

12. The computer program product of claim 10, further comprising:

generating a report for a user, the report comprising one or more of execution statistics for the original QEP, execution statistics for the new QEP, the static database query, a QEP automatically swapped to become the primary QEP, a QEP automatically swapped to become the secondary QEP, a state indicator describing the state of the status indicator; and

providing the report to the user in response to a user command.

13. The computer program product of claim 10, further comprising:

receiving a revert command from a user in response to automatic swapping of one of the original QEP and the new QEP as the primary QEP; and

swapping the QEP identified as the primary QEP with the QEP identified as the secondary QEP in response to the revert command.

14. The computer program product of claim 10, further comprising setting the status indicator to a state that triggers comparison of execution statistics for the primary QEP and the secondary QEP in response to a user command.

15. A database management system configured to ensure query execution plan stability, the database management system comprising:

at least one processor within a computer system in electronic communication with at least one database stored on at least one persistent storage device and at least one client computer system over a communication network;

a memory configured to store operational data and computer instructions executable by the at least one processor, the computer instructions configured to cause the computer system to;

receive a new query execution plan (QEP), in response to a user command provided by a user interface in communication with the computer system, the QEP producing the same result set as an original QEP bound to a static database query;

store the new QEP in the at least one persistent storage device and bind the static database query to the new QEP;

designate the new QEP as a primary QEP;

automatically collect execution statistics for the original QEP and the primary QEP in response to at least two requests to execute the static database query; and

automatically swap the new QEP with the original QEP, such that the original QEP becomes the primary QEP and the new QEP becomes a secondary QEP in response to the execution statistics of the original QEP exceeding a user-configurable threshold, the user-configurable threshold comprising at least one execution statistic for the new QEP.

16. The database management system of claim 15, wherein automatic collection of execution statistics and automatic swapping of the new QEP and the original QEP is controlled based on a status indicator that assumes one of three states, the states comprising settled, secondary QEP unsettled, and primary QEP unsettled, the original QEP designated as a secondary QEP.

17. The database management system of claim 15, further comprising computer instructions configured to cause the computer system to:

receive a revert command from a user in response to automatic swapping of one of the original QEP and the new QEP as the primary QEP; and

swap the QEP identified as the primary QEP with the QEP identified as the secondary QEP in response to the revert command.

18. The database management system of claim 15, further comprising setting the status indicator to a secondary QEP unsettled state in response to a user command, the secondary QEP unsettled state reinitiating comparison of execution statistics for the primary QEP and the secondary QEP in response to a request to execute the static database query.