Automatically determining optimization frequencies of queries with parameter markers

• US 7,987,178 B2
• Filed: 05/22/2008
• Issued: 07/26/2011
• Est. Priority Date: 02/09/2007
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Hueske, Fabian, Markl, Volker Gerhard
• Primary Examiner(s): Channavajjala; Srirama
• Application Number:
  US12/125,146
• Publication Number:
  US 20080222092A1
• Time in Patent Office: 1,160 Days
• Field of Search: 707/609, 707687-689, 707/705, 707713-721, 717/101, 717/127
• US Class Current: 707/713
• 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:

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

generating, by said computing system, a plurality of query execution plans for an execution of said query, 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 plurality of optimal execution costs, each optimal execution cost being a cost of optimally executing said query with a corresponding bind value set of said plurality of bind value sets;

determining, by said computing system, first and second query execution plans of said plurality of query execution plans that have a maximum distance between a first measurement set associated with said first query execution plan and a second measurement set associated with said second query execution plan, wherein said first and second measurement sets are included in said plurality of measurement sets, and wherein said plurality of optimal execution costs includes a first optimal execution cost of optimally executing said query via said first query execution plan with a first bind value set of said plurality of bind value sets, and further includes a second optimal execution cost of optimally executing said query via said second query execution plan with a second bind value set of said plurality of bind value sets;

determining, by said computing system, a first execution cost of executing said query via said first query execution plan with said second bind value set and a second execution cost of executing said query via said second query execution plan with said first bind value set;

said computing system determining a first difference between said first execution cost and said first optimal execution cost, and a second difference between said second execution cost and said second optimal execution cost;

said computing system determining at least one difference of said first and second differences exceeds a predefined threshold value;

based on said determining said at least one difference exceeds said predefined threshold value, said computing system automatically selecting a frequency of optimizing said query to be reoptimizing said query each time said query is executed instead of selecting optimizing said query only once; and

based on said automatically selected frequency of optimizing said query, said computing system executing said query on a database for each bind value set of said plurality of bind value sets, wherein a result of said executing said query is a reduction of a total cost of ownership of said database.

2. 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 said 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.

3. The method of claim 2, 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.

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:

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.

5. 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.

6. A computer program product, comprising a computer-readable, tangible storage device having a computer-readable program code stored therein, said computer-readable program code containing instructions that are carried out by a processor of a computer system to implement the method of claim 1.

7. The method of claim 1, further comprising:

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

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

determining, by said computing system, a set of optimal execution costs associated with said query execution plans of said second plurality of query execution plans in a one-to-one correspondence, each optimal execution cost of said set of optimal execution costs being a cost of optimally executing said second query with a bind value set of said second 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 second 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 second 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 second plurality of query execution plans, wherein said query execution plan P_i optimally executes said second query with said bind value set (p1, . . . , pn)_i and said query execution plan Q_i optimally executes said second query 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, wherein said C1_i is a cost of executing said second 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 second query via said query execution plan Q_i with bind value set (p1, . . . , pn)_i, wherein said determining said one or more pairs of execution costs C1_i, C2_i includes;

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

using said database hint to force said second query to use 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 set of optimal execution costs and said D2_i is a difference between said cost C2_i and an optimal execution cost OC2_i of said set of optimal execution costs, wherein said OC1_i is a cost of optimally executing said second 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 second query via said query execution plan P_i with bind value set (p1, . . . , pn)_i;

determining, by said computing system, each difference of said one or more pairs of differences D1_i and D2_i does not exceed said predefined threshold value;

based on said determining each difference of said one or more pairs of differences does not exceed said predefined threshold value, said computing system automatically selecting a second frequency of optimizing said second query to be optimizing said second query only once instead of reoptimizing said second query each time said second query is executed; and

based on said automatically selected second frequency of optimizing said second query, said computing system executing said second query on said database for each bind value set of said second plurality of bind value sets, wherein a result of executing said second query is said reduction of said total cost of said database.

8. 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:

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

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

generating, by said computing system, a plurality of query execution plans for an execution of said query, 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 plurality of optimal execution costs, each optimal execution cost being a cost of optimally executing said query with a corresponding bind value set of said plurality of bind value sets;

determining, by said computing system, first and second query execution plans of said plurality of query execution plans that have a maximum distance between a first measurement set associated with said first query execution plan and a second measurement set associated with said second query execution plan, wherein said first and second measurement sets are included in said plurality of measurement sets, and wherein said plurality of optimal execution costs includes a first optimal execution cost of optimally executing said query via said first query execution plan with a first bind value set of said plurality of bind value sets, and further includes a second optimal execution cost of optimally executing said query via said second query execution plan with a second bind value set of said plurality of bind value sets;

determining, by said computing system, a first execution cost of executing said query via said first query execution plan with said second bind value set and a second execution cost of executing said query via said second query execution plan with said first bind value set;

said computing system determining a first difference between said first execution cost and said first optimal execution cost, and a second difference between said second execution cost and said second optimal execution cost;

determining at least one difference of said first and second differences exceeds a predefined threshold value;

based on said determining said at least one difference exceeds said predefined threshold value, said computing system automatically selecting a frequency of optimizing said query to be reoptimizing said query each time said query is executed instead of selecting optimizing said query only once;

based on said automatically selected frequency of optimizing said query, said computing system executing said query on a database for each bind value set of said plurality of bind value sets, wherein a result of said executing said query is a reduction of a total cost of ownership of said database.

9. The computing system of claim 8, 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 said 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.

10. The computing system of claim 9, 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.

11. The computing system of claim 8, 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.

12. The computing system of claim 8, 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.

13. The computing system of claim 8, wherein said method further comprises:

obtaining a second plurality of bind value sets and a second plurality of measurement sets associated with said second bind value sets in a one-to-one correspondence, each bind value set of said second plurality of bind value sets including at least one bind value and associated with said one or more parameter markers of a second query, and each measurement set of said second plurality of measurement sets selected from the group consisting of one or more selectivity measurements and one or more cardinality measurements;

generating a second plurality of query execution plans for an execution of said second query, each query execution plan of said second plurality of query execution plans capable of optimally executing said second query with one or more bind value sets of said second plurality of bind value sets;

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

determining one or more pairs of bind value sets (p1, . . . , pn)_i, (q1, . . . , qn)_i of said second 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 second 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 second plurality of query execution plans, wherein said query execution plan P_i optimally executes said second query with said bind value set (p1, . . . , pn)_i and said query execution plan Q_i optimally executes said second query with said bind value set (q1, . . . , qn)_i, and wherein said i≧

1;

determining one or more pairs of execution costs C1_i, C2_i, wherein said C1_i is a cost of executing said second 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 second query via said query execution plan Q_i with bind value set (p1, . . . , pn)_i, wherein said determining said one or more pairs of execution costs C1_i, C2_i includes;

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

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

determining 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 set of optimal execution costs and said D2_i is a difference between said cost C2_i and an optimal execution cost OC2_i of said set of optimal execution costs, wherein said OC1_i is a cost of optimally executing said second 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 second query via said query execution plan P_i with bind value set (p1, . . . , pn)_i;

determining each difference of said one or more pairs of differences D1_i and D2_i does not exceed said predefined threshold value;

based on said determining each difference of said one or more pairs of differences does not exceed said predefined threshold value, automatically selecting a second frequency of optimizing said second query to be optimizing said second query only once instead of reoptimizing said second query each time said second query is executed; and

based on said automatically selected second frequency of optimizing said second query, executing said second query on said database for each bind value set of said second plurality of bind value sets, wherein a result of executing said second query is said reduction of said total cost of said database.

14. 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, 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 database hint to force said query to use said query execution plan P_i with bind value set (q1, . . . , qn)_i, and

using said database hint to force said query to use 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

executing, according to said automatically selected optimization frequency, said query on a database for each bind value set of said plurality of bind value sets, wherein a result of said executing said query is a reduction of a total cost of ownership of said database,

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 none of the differences of said one or more pairs of differences D1_i and D2_i 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.

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

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

17. A computer program product, comprising a computer-readable, tangible storage device having a computer-readable program code stored therein, said computer-readable program code containing instructions that are carried out by a processor of a computer system to implement the method of claim 14.