AUTOMATICALLY DETERMINING OPTIMIZATION FREQUENCIES OF QUERIES WITH PARAMETER MARKERS

• US 20080222092A1
• Filed: 05/22/2008
• Published: 09/11/2008
• Est. Priority Date: 02/09/2007
• Status: Active Grant

• Current Assignee: Fabian Hueske, Volker Gerhard Markl
• Original Assignee: Fabian Hueske, Volker Gerhard Markl
• Inventors: Markl, Volker Gerhard, Hueske, Fabian
• Granted Patent: US 7,987,178 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

A method and system for automatically determining optimization frequencies of queries having one or more parameter markers. Execution plans for a query are generated and each plan is associated with one or more bind value sets. An optimization frequency is selected based on differences between pairs of execution costs where one execution cost of a pair is a cost of executing the query with a bind value set via a first execution plan and the other execution cost of the pair is a cost of optimally executing the query with the bind value set via a second execution plan. The differences are based on maximum selectivity or cardinality distances associated with the bind value sets. If none of the differences exceeds a predefined value, the query is optimized once. If at least one of the differences exceeds the predefined value, the query is reoptimized each time the query is executed.

Claims

1. A computer-implemented method of automatically determining an optimization frequency of a query having one or more parameter markers, said method comprising:

generating, by a computing system, a plurality of query execution plans for an execution of a query having one or more parameter markers, each query execution plan associated with one or more bind value sets of a plurality of bind values sets;

determining that no difference of a plurality of differences between pairs of execution costs exceeds a predefined threshold value or that at least one difference of said plurality of differences exceeds said predefined threshold value, each pair of execution costs including a first execution cost and a second execution cost, said first execution cost being a cost of executing said query with a bind value set of said plurality of bind value sets via a first query execution plan of said plurality of query execution plans and said second execution cost being a cost of optimally executing said query with said bind value set via a second query execution plan of said plurality of query execution plans;

automatically selecting an optimization frequency by said computing system; and

storing said optimization frequency in a computer-usable medium,

wherein said optimization frequency is optimizing said query once as a result of a first determination by said determining that no difference of said plurality of differences exceeds said predefined threshold value, and

wherein said optimization frequency is reoptimizing said query each time said query is executed as a result of a second determination by said determining that at least one difference of said plurality of differences exceeds said predefined threshold value.

2. The method of claim 1, wherein said determining includes determining that no difference of said plurality of differences exceeds said predefined threshold value, and wherein said optimization frequency is said optimizing said query once.

3. The method of claim 1, wherein said determining includes determining that at least one difference of said plurality of differences exceeds said predefined threshold value, and wherein said optimizing frequency is said reoptimizing said query each time said query is executed.

4. The method of claim 1, further comprising collecting parameter marker data by said computing system, said parameter marker data including said plurality of bind value sets, wherein said collecting said parameter marker data comprises:

extracting, from a record of a binary dump file provided by a database feedback warehouse system, query information and a first bind value set of said plurality of bind value sets, said query information including a text of said query, a timestamp indicating an execution of said query, and an amount of time taken by said execution of said query; and

storing said query information and said first bind value set in a plurality of relational database tables.

5. The method of claim 4, wherein said first bind value set includes one or more actual values of said one or more parameters or one or more selectivities of one or more bind values of said first bind value set.

6. The method of claim 1, further comprising collecting parameter marker data by said computing system, said parameter marker data including said plurality of bind value sets, wherein said collecting said parameter marker data comprises:

parsing said query with a Structured Query Language (SQL) parser;

identifying one or more database columns referred to by one or more predicates that include said one or more parameter markers;

obtaining single-column database statistics associated with said one or more database columns;

collecting said plurality of bind value sets from said single-column database statistics; and

determining selectivities of said plurality of bind value sets from said single-column database statistics.

7. The method of claim 1, further comprising collecting parameter marker data by said computing system, said parameter marker data including said plurality of bind value sets, wherein said collecting said parameter marker data comprises:

parsing said query with a Structured Query Language (SQL) parser;

identifying one or more database columns referred to by one or more predicates that include said one or more parameter markers; and

selecting, via random sampling, said plurality of bind value sets from said one or more database columns.

8. A computer program product, comprising a computer-usable medium having a computer-readable program code embodied therein, said computer-readable program code comprising an algorithm adapted to implement the method of claim 1.

9. A computing system comprising a processor coupled to a computer-readable memory unit, said memory unit comprising a software application and instructions that when executed by said processor implement a method of automatically determining an optimization frequency of a query having one or more parameter markers, said method comprising:

generating, by a computing system, a plurality of query execution plans for an execution of a query having one or more parameter markers, each query execution plan associated with one or more bind value sets of a plurality of bind values sets;

determining that no difference of a plurality of differences between pairs of execution costs exceeds a predefined threshold value or that at least one difference of said plurality of differences exceeds said predefined threshold value, each pair of execution costs including a first execution cost and a second execution cost, said first execution cost being a cost of executing said query with a bind value set of said plurality of bind value sets via a first query execution plan of said plurality of query execution plans and said second execution cost being a cost of optimally executing said query with said bind value set via a second query execution plan of said plurality of query execution plans;

automatically selecting an optimization frequency by said computing system; and

storing said optimization frequency in a computer-usable medium,

wherein said optimization frequency is optimizing said query once as a result of a first determination by said determining that no difference of said plurality of differences exceeds said predefined threshold value, and

wherein said optimization frequency is reoptimizing said query each time said query is executed as a result of a second determination by said determining that at least one difference of said plurality of differences exceeds said predefined threshold value.

10. The computing system of claim 9, wherein said determining includes determining that no difference of said plurality of differences exceeds said predefined threshold value, and wherein said optimization frequency is said optimizing said query once.

11. The computing system of claim 9, wherein said determining includes determining that at least one difference of said plurality of differences exceeds said predefined threshold value, and wherein said optimizing frequency is said reoptimizing said query each time said query is executed.

12. The computing system of claim 9, wherein said method further comprises collecting parameter marker data by said computing system, said parameter marker data including said plurality of bind value sets, wherein said collecting said parameter marker data comprises:

extracting, from a record of a binary dump file provided by a database feedback warehouse system, query information and a first bind value set of said plurality of bind value sets, said query information including a text of said query, a timestamp indicating an execution of said query, and an amount of time taken by said execution of said query; and

storing said query information and said first bind value set in a plurality of relational database tables.

13. The computing system of claim 12, wherein said first bind value set includes one or more actual values of said one or more parameters or one or more selectivities of one or more bind values of said first bind value set.

14. The computing system of claim 9, wherein said method further comprises collecting parameter marker data by said computing system, said parameter marker data including said plurality of bind value sets, wherein said collecting said parameter marker data comprises:

parsing said query with a Structured Query Language (SQL) parser;

identifying one or more database columns referred to by one or more predicates that include said one or more parameter markers;

obtaining single-column database statistics associated with said one or more database columns;

collecting said plurality of bind value sets from said single-column database statistics; and

determining selectivities of said plurality of bind value sets from said single-column database statistics.

15. The computing system of claim 9, wherein said method further comprises collecting parameter marker data by said computing system, said parameter marker data including said plurality of bind value sets, wherein said collecting said parameter marker data comprises:

parsing said query with a Structured Query Language (SQL) parser;

identifying one or more database columns referred to by one or more predicates that include said one or more parameter markers; and

selecting, via random sampling, said plurality of bind value sets from said one or more database columns.

16. A computer-implemented method of determining an optimization frequency of a query having one or more parameter markers, said method comprising:

obtaining, by a computing system, a plurality of bind value sets, each bind value set including one or more bind values and associated with one or more parameter markers of a query;

obtaining, by said computing system, a plurality of measurement sets associated with said bind value sets in a one-to-one correspondence, each measurement set selected from the group consisting of one or more selectivity measurements and one or more cardinality measurements;

determining, by said computing system, a plurality of query execution plans, each query execution plan capable of optimally executing said query with one or more bind value sets of said plurality of bind value sets;

determining, by said computing system, a first set of execution costs associated with said query execution plans of said plurality of query execution plans in a one-to-one correspondence, each execution cost of said first set being a cost of optimally executing said query with a bind value set of said plurality of bind value sets;

determining, by said computing system, one or more pairs of bind value sets (p1, . . . , pn)_i, (q1, . . . , qn)_i of said plurality of bind value sets, said determining said one or more pairs of bind value sets including determining one or more distances d_i between a first measurement set S1_i associated with said bind value set (p1, . . . , pn)_i and a second measurement set S2_i associated with said (q1, . . . , qn)_i, said S1_i and said S2_i included in said plurality of measurement sets, wherein each distance d_i is a maximum distance between any pair of measurement sets associated with query execution plans P_i and Q_i of said plurality of query execution plans, wherein said query execution plan P_i is an optimal query execution plan associated with said bind value set (p1, . . . , pn)_i and said query execution plan Q_i is an optimal query execution plan associated with said bind value set (q1, . . . , qn)_i, and wherein said i≧

1;

determining, by said computing system, one or more pairs of execution costs C1_i, C2_i of a second set of execution costs, wherein said C1_i is a cost of executing said query via said query execution plan P_i with bind value set (q1, . . . , qn)_i and said C2_i is a cost of executing said query via said query execution plan Q_i with bind value set (p1, . . . , pn)_i;

determining, by said computing system, one or more pairs of differences D1_i and D2_i, wherein said D1_i is a difference between said cost C1_i and an optimal execution cost OC1_i of said first set of execution costs and said D2_i is a difference between said cost C2_i and an optimal execution cost OC2_i of said first set of execution costs, wherein said OC1_i is a cost of optimally executing said query via said query execution plan Q_i with bind value set (q1, . . . , qn)_i, and said OC2_i is a cost of optimally executing said query via said query execution plan P_i with bind value set (p1, . . . , pn)_i;

automatically selecting, by said computing system, an optimization frequency, wherein said optimization frequency is selected from the group consisting of optimizing said query once and reoptimizing said query each time said query is executed; and

storing said optimization frequency in a computer-usable medium,

wherein said optimization frequency is said optimizing said query once as a result of a first determination, via said determining said one or more pairs of differences, that no difference of said one or more pairs of differences exceeds a predefined threshold value, and

wherein said optimization frequency is said reoptimizing said query each time said query is executed as a result of a second determination, via said determining said one or more pairs of differences, that at least one difference of said one or more pairs of differences exceeds said predefined threshold value.

17. The method of claim 16, wherein each measurement set of said plurality of measurement sets is one or more selectivity measurements.

18. The method of claim 16, wherein each measurement set of said plurality of measurement sets is one or more cardinality measurements.

19. The method of claim 16, wherein said determining said one or more pairs of execution costs C1_i, C2_i of said second set of execution costs includes:

using a first database hint to force said query to use said query execution plan P_i with bind value set (q1, . . . , qn)_i; and

using a second database hint to force said query to use said query execution plan Q_i with bind value set (p1, . . . , pn)_i.

20. A computer program product, comprising a computer-usable medium having a computer-readable program code embodied therein, said computer-readable program code comprising an algorithm adapted to implement the method of claim 16.