Automatic query performance regression management
A computer implemented method can detect performance regression of executing a query using a current query plan. Responsive to detecting the performance regression, the method can automatically search for one or more candidate solutions for resolving the performance regression, and select, from the one or more candidate solutions, an effective solution that resolves the performance regression. The selecting includes evaluating performance of executing the query using one or more alternative query plans generated by the one or more candidate solutions. The method can store the effective solution for future execution of the query. The effective solution is configured to generate an updated query plan selected from the one or more alternative query plans. The updated query plan has better performance than the current query plan for executing the query. Related systems and software for implementing the method are also disclosed.
1 . A computer-implemented method comprising:
detecting performance regression of executing a query using a current query plan;
responsive to detecting the performance regression, automatically searching for one or more candidate solutions for resolving the performance regression;
selecting, from the one or more candidate solutions identified responsive to detecting the performance regression, an effective solution that resolves the performance regression, wherein the selecting comprises automatically evaluating performance of executing the query using one or more alternative query plans generated by the one or more candidate solutions; and
storing the effective solution for future execution of the query, wherein the effective solution is configured to generate an updated query plan selected from the one or more alternative query plans, wherein the updated query plan has better performance than the current query plan for executing the query,
wherein detecting the performance regression comprises comparing one or more performance metrics of executing the query using the current query plan with corresponding performance metrics stored in an execution history,
wherein the one or more performance metrics comprise query execution time, wherein the performance regression is detected if the query execution time of executing the query using the current query plan is M times greater than an average of N most recent query execution times stored in the execution history, wherein M is predefined and greater than 1, and N is a predefined positive integer.
2 . The method of claim 1 , wherein the execution history stores one or more legacy query plans previously generated for the query, wherein the searching comprises identifying at least some of the legacy query plans stored in the execution history have better performance than the current query plan for executing the query.
3 . The method of claim 2 , wherein the one or more performance metrics comprise query execution time, wherein a candidate solution is identified if the query execution time corresponding to a selected legacy query plan stored in the execution history is smaller than the query execution time of executing the query using the current query plan, wherein the identified candidate solution specifies the selected legacy query plan as an alternative query plan.
4 . The method of claim 3 , wherein the effective solution specifies the updated query plan, wherein the query execution time of executing the query using the updated query plan is less than a predefined fraction of the query execution time of executing the query using the current query plan, wherein query objects included in the current query plan have identical object definitions as corresponding query objects included in the updated query plan.
5 . The method of claim 1 , wherein detecting the performance regression comprises identifying a predefined subtree pattern in a logical tree structure of the current query plan, wherein the logical tree structure defines relationship between a plurality of query operations.
6 . The method of claim 5 , wherein at least one of the candidate solutions specifies compiling the query with a query hint.
7 . The method of claim 5 , further comprising collecting runtime statistics for an operation involved in execution of the query using the current query plan, wherein at least one of the candidate solutions specifies compiling the query using the runtime statistics.
8 . The method of claim 1 , wherein detecting the performance regression comprises identifying the current query plan was generated with an insufficient plan enumeration, wherein at least one of the candidate solutions specifies compiling the query through an exhaustive plan enumeration.
9 . A computing system, comprising:
memory;
one or more hardware processors coupled to the memory; and
one or more computer readable storage media storing instructions that, when loaded into the memory, cause the one or more hardware processors to perform operations comprising:
detecting performance regression of executing a query using a current query plan;
responsive to detecting the performance regression, automatically searching for one or more candidate solutions for resolving the performance regression;
selecting, from the one or more candidate solutions identified responsive to detecting the performance regression, an effective solution that resolves the performance regression, wherein the selecting comprises automatically evaluating performance of executing the query using one or more alternative query plans generated by the one or more candidate solutions; and
storing the effective solution for future execution of the query, wherein the effective solution is configured to generate an updated query plan selected from the one or more alternative query plans, wherein the updated query plan has better performance than the current query plan for executing the query,
wherein detecting the performance regression comprises comparing one or more performance metrics of executing the query using the current query plan with corresponding performance metrics stored in an execution history,
wherein the one or more performance metrics comprise query execution time, wherein the performance regression is detected if the query execution time of executing the query using the current query plan is M times greater than an average of N most recent query execution times stored in the execution history, wherein M is predefined and greater than 1, and N is a predefined positive integer.
10 . The system of claim 9 , wherein the execution history stores one or more legacy query plans previously generated for the query, wherein the searching comprises identifying at least some of the legacy query plans stored in the execution history have better performance than the current query plan for executing the query.
11 . The system of claim 10 , wherein the one or more performance metrics comprise query execution time, wherein a candidate solution is identified if the query execution time corresponding to a selected legacy query plan stored in the execution history is smaller than the query execution time of executing the query using the current query plan, wherein the identified candidate solution specifies the selected legacy query plan as an alternative query plan.
12 . The system of claim 11 , wherein the effective solution specifies the updated query plan, wherein the query execution time of executing the query using the updated query plan is less than a predefined fraction of the query execution time of executing the query using the current query plan, wherein query objects included in the current query plan have identical object definitions as corresponding query objects included in the updated query plan.
13 . The system of claim 9 , wherein detecting the performance regression comprises identifying a predefined subtree pattern in a logical tree structure of the current query plan, wherein the logical tree structure defines relationship between a plurality of query operations.
14 . The system of claim 13 , wherein at least one of the candidate solutions specifies compiling the query with a query hint.
15 . The system of claim 14 , wherein the operations further comprise collecting runtime statistics for an operation involved in execution of the query using the current query plan, wherein at least one of the candidate solutions specifies compiling the query using the runtime statistics.
16 . One or more non-transitory computer-readable media having encoded thereon computer-executable instructions causing one or more processors to perform a method comprising:
detecting performance regression of executing a query using a current query plan;
responsive to detecting the performance regression, automatically searching for one or more candidate solutions for resolving the performance regression;
selecting, from the one or more candidate solutions identified responsive to detecting the performance regression, an effective solution that resolves the performance regression, wherein the selecting comprises automatically evaluating performance of executing the query using one or more alternative query plans generated by the one or more candidate solutions; and
storing the effective solution for future execution of the query, wherein the effective solution is configured to generate an updated query plan selected from the one or more alternative query plans, wherein the updated query plan has better performance than the current query plan for executing the query,
wherein detecting the performance regression comprises comparing one or more performance metrics of executing the query using the current query plan with corresponding performance metrics stored in an execution history,
wherein the one or more performance metrics comprise query execution time, wherein the performance regression is detected if the query execution time of executing the query using the current query plan is M times greater than an average of N most recent query execution times stored in the execution history, wherein M is predefined and greater than 1, and N is a predefined positive integer.