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.

Claim:

What is claimed is:

1. A computer-implemented method of automatically determining an optimization frequency of a query having one or more parameter markers, said method comprising: obtaining, bya 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 moreparameter 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 foran 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, eachoptimal 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 queryexecution 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 areincluded 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 saidplurality 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 computingsystem, 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; saidcomputing 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 determiningat 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 frequencyof 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 executingsaid 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 timestampindicating 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 associatedwith 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 bindvalue 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 acomputer 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, eachbind 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 thegroup 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 saidsecond 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 associatedwith 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 valueset of said second plurality of bind value sets; determining, by said computing system, one or more pairs of bind value sets (p1, . . . , pn).sub.i, (q1, . . . , qn).sub.i of said second plurality of bind value sets, said determining said one or morepairs of bind value sets including determining one or more distances d.sub.i between a first measurement set S1.sub.i associated with said bind value set (p1, . . . , pn).sub.i and a second measurement set S2.sub.i associated with said (q1, . . . ,qn).sub.i, said S1.sub.i and said S2.sub.i included in said second plurality of measurement sets, wherein each distance d.sub.i is a maximum distance between any pair of measurement sets associated with query execution plans P.sub.i and Q.sub.i of saidsecond plurality of query execution plans, wherein said query execution plan P.sub.i optimally executes said second query with said bind value set (p1, . . . , pn).sub.i and said query execution plan Q.sub.i optimally executes said second query withsaid bind value set (q1, . . . , qn).sub.i, and wherein said i.gtoreq.1; determining, by said computing system, one or more pairs of execution costs C1.sub.i, C2.sub.i, wherein said C1.sub.i is a cost of executing said second query via said queryexecution plan P.sub.i with bind value set (q1, . . . , qn).sub.i and said C2.sub.i is a cost of executing said second query via said query execution plan Q.sub.i with bind value set (p1, . . . , pn).sub.i, wherein said determining said one or morepairs of execution costs C1.sub.i, C2.sub.i includes: using a database hint to force said second query to use said query execution plan P.sub.i with bind value set (q1, . . . , qn).sub.i, and using said database hint to force said second query to usesaid query execution plan Q.sub.i with bind value set (p1, . . . , pn).sub.i; determining, by said computing system, one or more pairs of differences D1.sub.i and D2.sub.i, wherein said D1.sub.i is a difference between said cost C1.sub.i and an optimalexecution cost OC1.sub.i of said set of optimal execution costs and said D2.sub.i is a difference between said cost C2.sub.i and an optimal execution cost OC2.sub.i of said set of optimal execution costs, wherein said OC1.sub.i is a cost of optimallyexecuting said second query via said query execution plan Q.sub.i with bind value set (q1, . . . , qn).sub.i, and said OC2.sub.i is a cost of optimally executing said second query via said query execution plan P.sub.i with bind value set (p1, . . . ,pn).sub.i; determining, by said computing system, each difference of said one or more pairs of differences D1.sub.i and D2.sub.i does not exceed said predefined threshold value; based on said determining each difference of said one or more pairs ofdifferences 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 timesaid 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 valuesets, 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 anoptimization 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-onecorrespondence, 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 ormore 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 saidplurality 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 saidplurality 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 valuesets; 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 secondmeasurement 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 executioncost 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 queryexecution 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 secondexecution 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 seconddifference 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 differenceexceeds 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 onsaid 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 totalcost 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 parametermarker 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 saidquery, 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 parametermarker 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 databasestatistics 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 databasestatistics.

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 parametermarker 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 selectedfrom 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 pluralityof 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 saidsecond 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 bindvalue sets; determining one or more pairs of bind value sets (p1, . . . , pn).sub.i, (q1, . . . , qn).sub.i of said second plurality of bind value sets, said determining said one or more pairs of bind value sets including determining one or moredistances d.sub.i between a first measurement set S1.sub.i associated with said bind value set (p1, . . . , pn).sub.i and a second measurement set S2.sub.i associated with said (q1, . . . , qn).sub.i, said S1.sub.i and said S2.sub.i included in saidsecond plurality of measurement sets, wherein each distance d.sub.i is a maximum distance between any pair of measurement sets associated with query execution plans P.sub.i and Q.sub.i of said second plurality of query execution plans, wherein said queryexecution plan P.sub.i optimally executes said second query with said bind value set (p1, . . . , pn).sub.i and said query execution plan Q.sub.i optimally executes said second query with said bind value set (q1, . . . , qn).sub.i, and wherein saidi.gtoreq.1; determining one or more pairs of execution costs C1.sub.i, C2.sub.i, wherein said C1.sub.i is a cost of executing said second query via said query execution plan P.sub.i with bind value set (q1, . . . , qn).sub.i and said C2.sub.i is a costof executing said second query via said query execution plan Q.sub.i with bind value set (p1, . . . , pn).sub.i, wherein said determining said one or more pairs of execution costs C1.sub.i, C2.sub.i includes: using a database hint to force said secondquery to use said query execution plan P.sub.i with bind value set (q1, . . . , qn).sub.i, and using said database hint to force said second query to use said query execution plan Q.sub.i with bind value set (p1, . . . , pn).sub.i; determining one ormore pairs of differences D1.sub.i and D2.sub.i, wherein said D1.sub.i is a difference between said cost C1.sub.i and an optimal execution cost OC1.sub.i of said set of optimal execution costs and said D2.sub.i is a difference between said cost C2.sub.iand an optimal execution cost OC2.sub.i of said set of optimal execution costs, wherein said OC1.sub.i is a cost of optimally executing said second query via said query execution plan Q.sub.i with bind value set (q1, . . . , qn).sub.i, and saidOC2.sub.i is a cost of optimally executing said second query via said query execution plan P.sub.i with bind value set (p1, . . . , pn).sub.i; determining each difference of said one or more pairs of differences D1.sub.i and D2.sub.i does not exceedsaid 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 beoptimizing 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 saiddatabase 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 includingone 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 selectedfrom 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 querywith 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-onecorrespondence, 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).sub.i, (q1, . . . , qn).sub.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.sub.i between a first measurement set S1.sub.i associated with said bindvalue set (p1, . . . , pn).sub.i and a second measurement set S2.sub.i associated with said (q1, . . . , qn).sub.i, said S1.sub.i and said S2.sub.i included in said plurality of measurement sets, wherein each distance d.sub.i is a maximum distancebetween any pair of measurement sets associated with query execution plans P.sub.i and Q.sub.i of said plurality of query execution plans, wherein said query execution plan P.sub.i is an optimal query execution plan associated with said bind value set(p1, . . . , pn).sub.i and said query execution plan Q.sub.i is an optimal query execution plan associated with said bind value set (q1, . . . , qn).sub.i, and wherein said i.gtoreq.1; determining, by said computing system, one or more pairs ofexecution costs C1.sub.i, C2.sub.i of a second set of execution costs, wherein said C1.sub.i is a cost of executing said query via said query execution plan P.sub.i with bind value set (q1, . . . , qn).sub.i and said C2.sub.i is a cost of executing saidquery via said query execution plan Q.sub.i with bind value set (p1, . . . , pn).sub.i, wherein said determining said one or more pairs of execution costs C1.sub.i, C2.sub.i of said second set of execution costs includes: using a database hint to forcesaid query to use said query execution plan P.sub.i with bind value set (q1, . . . , qn).sub.i, and using said database hint to force said query to use said query execution plan Q.sub.i with bind value set (p1, . . . , pn).sub.i; determining, by saidcomputing system, one or more pairs of differences D1.sub.i and D2.sub.i, wherein said D1.sub.i is a difference between said cost C1.sub.i and an optimal execution cost OC1.sub.i of said first set of execution costs and said D2.sub.i is a differencebetween said cost C2.sub.i and an optimal execution cost OC2.sub.i of said first set of execution costs, wherein said OC1.sub.i is a cost of optimally executing said query via said query execution plan Q.sub.i with bind value set (q1, . . . , qn).sub.i,and said OC2.sub.i is a cost of optimally executing said query via said query execution plan P.sub.i with bind value set (p1, . . . , pn).sub.i; automatically selecting, by said computing system, an optimization frequency, wherein said optimizationfrequency 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 eachbind 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 firstdetermination, via said determining said one or more pairs of differences, that none of the differences of said one or more pairs of differences D1.sub.i and D2.sub.i exceeds a predefined threshold value, and wherein said optimization frequency is saidreoptimizing 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 predefinedthreshold 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 acomputer system to implement the method of claim 14.

Description:

FIELD OF THE INVENTION

The present invention relates to a method and system for automatically determining optimization frequencies of queries with parameter markers.

BACKGROUND OF THE INVENTION

Conventionally, a programmer or a database administrator (DBA) managing a database system manually selects a reoptimization mode for queries having parameter markers. This selection of an optimal reoptimization mode depends not only on thequery itself, but also on the bind values of the query's parameter marker(s). As these bind values can be unknown or change over time, an attempt to select an optimal reoptimization mode is a non-trivial procedure that may result in selecting asub-optimal reoptimization mode. This burdensome manual process of selecting a reoptimization mode decreases the productivity of the programmer or DBA and increases the total cost of ownership of the database system. Further, a selection of asub-optimal reoptimization mode slows down a program or the entire database system. Thus, there exists a need to overcome at least one of the preceding deficiencies and limitations of the related art.

SUMMARY OF THE INVENTION

In first embodiments, the present invention provides a computer-implemented method of automatically determining an optimization frequency of a query having one or more parameter markers, the 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 the plurality of differences exceeds the predefined threshold value, each pairof execution costs including a first execution cost and a second execution cost, the first execution cost being a cost of executing the query with a bind value set of the plurality of bind value sets via a first query execution plan of the plurality ofquery execution plans and the second execution cost being a cost of optimally executing the query with the bind value set via a second query execution plan of the plurality of query execution plans;

automatically selecting an optimization frequency by the computing system; and

storing the optimization frequency in a computer-usable medium,

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

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

In second embodiments, the present invention provides a computer-implemented method of determining an optimization frequency of a query having one or more parameter markers, the 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 the computing system, a plurality of measurement sets associated with the 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 ormore cardinality measurements;

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

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

determining, by the computing system, one or more pairs of bind value sets (p1, . . . , pn).sub.i, (q1, . . . , qn).sub.i of the plurality of bind value sets, the determining the one or more pairs of bind value sets including determining oneor more distances d.sub.i between a first measurement set S1.sub.i associated with the bind value set (p1, . . . , pn).sub.i and a second measurement set S2.sub.i associated with the (q1, . . . , qn).sub.i, the S1.sub.i and the S2.sub.i included in theplurality of measurement sets, wherein each distance d.sub.i is a maximum distance between any pair of measurement sets associated with query execution plans P.sub.i and Q.sub.i of the plurality of query execution plans, wherein the query execution planP.sub.i is an optimal query execution plan associated with the bind value set (p1, . . . , pn).sub.i and the query execution plan Q.sub.i is an optimal query execution plan associated with the bind value set (p1, . . . , pn).sub.i, and wherein thei.gtoreq.1;

determining, by the computing system, one or more pairs of execution costs C1.sub.i, C2.sub.i of a second set of execution costs, wherein the C1.sub.i is a cost of executing the query via the query execution plan P.sub.i with bind value set (q1,. . . , qn).sub.i and the C2.sub.i is a cost of executing the query via the query execution plan Q.sub.i with bind value set (p1, . . . , pn).sub.i;

determining, by the computing system, one or more pairs of differences D1.sub.i and D2.sub.i, wherein the D1.sub.i is a difference between the cost C1.sub.i and an optimal execution cost OC1.sub.i of the first set of execution costs and theD2.sub.i is a difference between the cost C2.sub.i and an optimal execution cost OC2.sub.i of the first set of execution costs, wherein the OC1.sub.i is a cost of optimally executing the query via the query execution plan Q.sub.i with bind value set (q1,. . . , qn).sub.i, and the OC2.sub.i is a cost of optimally executing the query via the query execution plan P.sub.i with bind value set (p1, . . . , pn).sub.i;

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

storing the optimization frequency in a computer-usable medium,

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

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

Systems and computer program products corresponding to the above-summarized methods are also described herein.

Advantageously, the present invention provides a technique for using selectivity or cardinality information to automatically determine the reoptimization mode of a query having parameter markers, thereby improving the productivity of DBAs andreducing the total cost of ownership of databases.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a block diagram of a system for automatically determining optimization frequencies of queries with parameter markers, in accordance with embodiments of the present invention.

FIG. 2 is a flow diagram of a process of automatically determining optimization frequencies of queries with parameter markers in the system of FIG. 1, in accordance with embodiments of the present invention.

FIG. 3A is a flow diagram of a process of collecting bind values of parameter markers in the process of FIG. 2, in accordance with embodiments of the present invention.

FIG. 3B is an example of a binary dump file converted into a human readable format from which query information and bind values are extracted in the process of FIG. 3A, in accordance with embodiments of the present invention.

FIG. 3C are examples of code for creating database tables that store the query information and bind values in the process of FIG. 3A, in accordance with embodiments of the present invention.

FIG. 3D is an entity-relationship model of the database tables that store the query information and bind values in the process of FIG. 3A, in accordance with embodiments of the present invention.

FIG. 4A is a process of analyzing a query execution plan space within the process of FIG. 2, in accordance with embodiments of the present invention.

FIG. 4B is an example of a table from which query information and bind value sets are requested and obtained in the process of FIG. 4A, in accordance with embodiments of the present invention.

FIG. 4C is an example of a query in which parameter markers are replaced by bind values, where the query's text is obtained from the table in FIG. 4B, in accordance with embodiments of the present invention.

FIG. 4D is an example of an Explain query generated from the query in FIG. 4C, in accordance with embodiments of the present invention.

FIG. 4E is an exemplary plan space diagram illustrating a query execution plan space being analyzed by the process of FIG. 4A, in accordance with embodiments of the present invention.

FIG. 4F is an exemplary plan space diagram illustrating a sorting out of bind value sets for determining a cost difference in the process of FIG. 4A, in accordance with embodiments of the present invention.

FIG. 4G is an exemplary plan space diagram illustrating a determination of pairs of bind value sets having a maximum distance in selectivity for determining a cost difference in the process of FIG. 4A, in accordance with embodiments of thepresent invention.

FIG. 5 is a computing system that includes components of the system of FIG. 1 and implements the processes of FIGS. 2, 3A and 4A, in accordance with embodiments of the present invention.

DETAILED DESCRIPTION OF THE INVENTION

The present invention provides a technique for using selectivity information to automatically determine an optimization frequency (a.k.a. reoptimization mode) of a query having one or more parameter markers. The selectivity information isobtained for multiple instances of parameter marker values either through query feedback, the system catalog, or by drawing random samples. The present invention determines whether a query execution plan is sensitive to the selectivity of the parametermarker values. To support this determination of the query execution plan's sensitivity, the technique disclosed herein creates a graph of the selectivity space and associates each parameter marker bind value set in this space with a query executionplan. By taking the largest distances in the selectivity space, the technique disclosed herein determines whether one query execution plan is sufficient to cover the selectivity space, or whether multiple query execution plans are required. Further,the technique provides a recommendation to either optimize the query once or to reoptimize the query for every set of parameter marker bind values.

Definitions

Parameter marker: a placeholder for a variable within a query. To provide parameter markers with values, variables are bound to the parameter markers. A bind value replaces a parameter marker at query execution time. A bind value of aparameter marker is known only at query execution time.

Plan space diagram: a diagram that shows the location of all different query execution plans for a query having two parameter markers in the space defined by the selectivities of the bind values of the parameter markers.

Frequency diagram: a bar chart showing the frequency distribution of the selectivities of the bind values of a parameter marker (e.g., one axis of the chart plots selectivity and the other axis plots frequency in percent).

Selectivity: A ratio or percentage of data sets that are sorted out by a predicate of a database query. For example, a predicate is a condition in a WHERE clause applied to a database table and a selectivity indicates the percentage of rows ofthe table that satisfy the condition.

Default selectivity: A default selectivity assumes a uniform data distribution in an affected database column and is defined as:

Reoptimization mode: Defines when and how often a database optimizer is called to select a query execution plan for a query with one or more parameter markers. Reoptimization modes are also referred to as optimization frequencies and includeReOpt None, ReOpt Once and ReOpt Always.

ReOpt None: A reoptimization mode in which queries are optimized once before their first execution during a query preparation process. Since parameter marker bind values are unknown at optimization time and known only at query execution time,the optimizer cannot estimate their selectivities using statistical information. Instead, the optimizer determines a default selectivity for each parameter marker predicate. Using the default selectivities, the optimizer selects a query execution planthat is cached and used for all executions of the query. ReOpt None results in an optimal query execution plan only if the data is uniformly distributed in all affected database columns. DB2.RTM. uses ReOpt None as the default reoptimization mode.

ReOpt Once: A reoptimization mode in which a query is optimized only once, before the query's first execution and using the query's first bind value set to estimate the selectivities of the parameter marker predicates. The optimizer chooses aquery execution plan based on execution cost estimations for the estimated selectivities. The resulting query execution plan is cached and used for all executions of the query. ReOpt Once provides a savings in optimization costs, but causes highexecution costs if the cached query execution plan is suboptimal for a set of bind values. ReOpt Once is efficient only if the selectivities of the parameter marker predicates for all subsequent bind value sets do not differ significantly from theaforementioned selectivities determined for the first bind value set.

ReOpt Always: A reoptimization mode in which a query is reoptimized before every execution of the query, each time using a current bind value set (i.e., one or more bind values associated with the current query execution) to estimate currentselectivities of the parameter marker predicates. For each reoptimization, the optimizer selects the query execution plan that is optimal for the current bind value set based on the optimizer's knowledge of the data distribution according to availabledatabase statistics. ReOpt Always is expensive in terms of optimization costs.

In another embodiment, the query text, parameter marker values and query execution time information are collected from a source other than binary dump file 104 and are stored in a structure (e.g., plain files) other than PM tables 108.

FIG. 2 is a flow diagram of a process of automatically determining optimization frequencies of queries with parameter markers in the system of FIG. 1, in accordance with embodiments of the present invention. The optimization frequencydetermination process of FIG. 2 begins at step 200. In step 202, a software tool (e.g., extract & transform tool 106 in FIG. 1) collects bind values of each parameter marker of a set of one or more parameter markers included in a query. In a firstembodiment, the bind values are written into binary dump file 104 (see FIG. 1) (e.g., a RDSMon dump file provided by a DB2.RTM. feedback warehouse architecture) and the bind values are subsequently extracted from the binary dump file in step 202 byextract & transform tool 106 (see FIG. 1).

In a second embodiment, a software tool (not shown) that replaces extract & transform tool 106 in FIG. 1 uses single column statistics (e.g., information about frequent values and quantiles of a table's column) from system catalog tables tocollect the bind values in step 202. Using single column database statistics in step 202 advantageously allows bind values to be collected according to their selectivities, thereby permitting the bind values to be used to analyze the whole queryexecution plan space, as described below relative to FIG. 4A. Hereinafter, a query execution plan space is also referred to simply as a plan space. Further, using database statistics in step 202 makes it possible to collect bind values for queries thatnever executed. On the other hand, using single column statistics in step 202 requires up-to-date statistics and refreshing such statistics is expensive. Further, collecting bind values from single column statistics assumes independence of the columnsto which the parameter markers refer. To request values from single column statistics, it is necessary to identify the tables and columns to which the parameter marker predicates of a query refer. Therefore, a complex task of parsing of the query mustbe performed.

In a third embodiment, a software tool (not shown) that replaces extract & transform tool 106 in FIG. 1 randomly selects bind value sets in step 202. The bind value sets are selected from the tables and columns to which the parameter markerpredicates of a query refer. Random selection of bind value sets in step 202 advantageously allows bind values for any query to be selected irrespective of whether the query ever executed. The number of collected bind values may be assigned as thevalues are randomly selected from the whole range of data. Further, using random sampling to collect bind values in step 202 allows whole bind value sets to be collected and not single bind values. Therefore, independence of the columns to which theparameter markers refer does not have to be assumed. However, it is very expensive to collect bind values by random sampling if the query joins large tables because the whole data range has to be scanned to ensure a satisfactory distribution of bindvalues. Furthermore, the query must be parsed if sampling is used in step 202.

In one embodiment, extract & transform tool 106 (see FIG. 1) transforms the data extracted in step 202 into strings to facilitate handling and storage. In one embodiment, after the extraction in step 202, a validity check is performed on theextracted data. This validity check includes a check that the query includes one or more parameter markers. If the validity check determines that a query includes at least one parameter marker, then the process of FIG. 2 continues with step 204. Ifthe validity check determines that a query does not include any parameter markers, then the remaining steps of the process of FIG. 2 are skipped for that query.

In step 204, extract & transform tool 106 (see FIG. 1) loads (i.e., stores) the bind values collected in step 202 and related query execution information into PM tables 108 (see FIG. 1). In another embodiment, the PM tables are not part ofsystem 100 (see FIG. 1) and step 204 stores the query execution information and the collected bind values in another data structure (e.g., plain files). The related query execution information loaded in step 204 includes the text of the query, the queryexecution timestamp, and the duration of the query execution. The collection and storage of bind values and related query execution information in steps 202 and 204 are discussed in more detail below relative to FIG. 3A.

FIG. 3A is a flow diagram of a process of collecting and storing bind values of parameter markers in the process of FIG. 2, in accordance with embodiments of the present invention. The collection and storage process of FIG. 2 incorporates queryfeedback information and starts at step 300. In step 302, each time a query is executed, a new record associated with the query execution is appended to binary dump file 104 (see FIG. 1). As one example, step 302 appends the new record to a RDSMonbinary dump file that provides a DB2.RTM. feedback warehouse with information about the execution of queries. The feedback warehouse is filled with information about query executions that had a significant discrepancy between the optimizer's estimatedcosts and the actual query execution costs.

The contents of the record appended in step 302 include, for example, (1) a unique ID for the record, (2) query execution timestamp, (3) the amount of time taken to execute the query (a.k.a. query execution time), (4) the number of parametermarkers included in the query, (5) the value type, value length and value of each parameter marker included in the query, (6) the text of the query statement, (7) the length of the query text, (8) the optimizer's estimated information and informationlength, and (9) the runtime counter's information and information length.

In step 304, extract & transform tool 106 (see FIG. 1) extracts information about the executions of queries having one or more parameter markers and the bind values associated with those queries. Step 304 extracts the query executioninformation and bind values from the records in binary dump file 104 (see FIG. 1). As one example, the step 304 data is extracted from the aforementioned RDSMon binary dump file. The data extracted in step 304 includes the text of the query statement,the query execution timestamp, the query execution time and all parameter marker bind values. A validity check (not shown) includes a check to determine whether the query includes at least one parameter marker and may include one or more other checks ofthe validity of the query execution information and bind values being extracted based on predefined criteria (e.g., check that the query text does not exceed a predetermined limit on the number of characters). If the validity check determines that thequery does not include at least one parameter marker, then the remaining steps of FIG. 3A are not performed.

As all parameter marker bind values are saved in their original data type in the RDSMon file, extract & transform tool 106 (see FIG. 1) transforms the extracted parameter marker bind values into strings in step 306. The transformation in step306 simplifies the handling and storage of the bind values.

In step 308, extract & transform tool 106 (see FIG. 1) stores the following items in a table of PM tables 108 (see FIG. 1): the query text, the database on which the query was executed, and a count of how many times the query was executed.

In step 310, extract & transform tool 106 (see FIG. 1) stores the following items in a table of PM tables 108 (see FIG. 1): an identifier of each parameter marker within the query, every actual bind value used for each parameter marker withinthe query, and a count of how many times a bind value was used for a parameter marker.

In step 312, extract & transform tool 106 (see FIG. 1) stores the following items in a table of PM tables 108 (see FIG. 1): each combination of bind values (i.e., bind value set) a query has been executed with and a count of how many times eachcombination occurred.

In step 314, extract & transform tool 106 (see FIG. 1) stores the following items in a table of PM tables 108 (see FIG. 1): a query execution timestamp for each query execution and an amount of time taken to execute the query for each queryexecution.

The tables referenced by steps 308, 310, 312 and 314 are described below relative to FIGS. 3C and 3D. The process of FIG. 3A ends at step 316.

FIG. 3B is an example of a binary dump file converted into a human readable format from which query information and bind values are extracted in the process of FIG. 3A, in accordance with embodiments of the present invention. A RDSMon binarydump file 320 includes examples of the query execution information, bind value information, optimizer estimations and runtime counter information described above relative to step 302 of FIG. 3A.

Storing Query Execution Information

FIG. 3C are examples of code for creating database tables that store the query information and bind values in the process of FIG. 3A, in accordance with embodiments of the present invention. Code 340 is an example of Data Definition Language(DDL) statements that generate four relational database tables PMQUERIES, PMVALUES, PMCOMBINATIONS AND PMEXECUTIONS. These relational database tables are examples of PM tables 108 (see FIG. 1). The PMQUERIES table includes columns for storing the dataitems described above relative to step 308 (see FIG. 3A). The PMVALUES table includes columns for storing the data items described above relative to step 310 (see FIG. 3A). The PMCOMBINATIONS table includes columns for storing the data items describedabove relative to step 312 (see FIG. 3A). The PMEXECUTIONS table includes columns for storing the data items described above relative to step 314 (see FIG. 3A). Complete descriptions of the four PM tables generated by DDL statements 340 are presentedbelow relative to FIG. 3D.

FIG. 3D is an entity-relationship (ER) model of the relational database tables that store the query information and bind values in the process of FIG. 3A, in accordance with embodiments of the present invention. ER model 360 illustrates thefour PM tables created by the statements of FIG. 3C, their relationships and their respective columns. Each of the four PM tables in ER model 360 is described below.

PMQUERIES: Each row contains information about one query. The columns of PMQUERIES are described in Table 1.

TABLE-US-00001 TABLE 1 PMQUERIES Column Description QID Primary key column QTEXT Contains the query text having no more than a predefined maximum number of characters DBNAME Name of the database on which the query ran CCOUNT Counts how manytimes information about a certain query is added to the PM Tables. The counter is initialized with one when a new query is inserted.

PMVALUES: Each row contains the bind value of one parameter marker. A row is deleted from PMVALUES if the query the row refers to is deleted from the PMQUERIES table. The columns of PMVALUES are described in Table 2.

TABLE-US-00002 TABLE 2 PMVALUES Column Description QID References a row in the PMQUERIES table and therefore references a specific query PNO Identifies the parameter marker within the query. For the first parameter marker, PNO has the value ofone, for the second parameter marker, PNO has the value of two, etc. PVID Primary key column PVAL Stores the bind value of the parameter marker as a VARCHAR having no more than a predefined maximum number of characters PVCOUNT Counts how many times thevalue for a parameter marker has occurred, where the parameter marker is in the query specified by QID and on the parameter marker position specified by PNO

PMCOMBINATIONS: This table combines one or more bind values to a bind value set. For each bind value set, one or more rows are inserted. A row is deleted from PMCOMBINATIONS if the query to which the row refers is deleted from the PMQUERIEStable. The columns of PMCOMBINATIONS are described in Table 3.

TABLE-US-00003 TABLE 3 PMCOMBINATIONS Column Description QID References a row in the PMQUERIES table and therefore references a specific query. CID Primary key column PVID References a bind value in the PMVALUES table CCOUNT Counts how manytimes the bind value set of this row has occurred.

PMEXECUTIONS: Each row stores information about one execution of a query. For every execution, a new row is inserted. The primary key is the combination of CID and EXECUTED. A row is deleted from PMEXECUTIONS if the query to which the rowrefers is deleted from the PMQUERIES table. The columns of PMEXECUTIONS are described in Table 4.

TABLE-US-00004 TABLE 4 PMEXECUTIONS Column Description QID References a row in the PMQUERIES table and therefore references a specific query EXECUTED Stores the time when the query was executed CID Stores the bind value set with which the querywas executed EXEC_TIME Stores the time taken to execute the query

In one embodiment, step 204 (see FIG. 2) includes the following storage process performed by extract & transform tool 106 (see FIG. 1) for each set of query execution data collected in step 202 (see FIG. 2). First, the query text and thedatabase name (e.g., provided as a command line parameter) are inserted into the PMQUERIES table. If the combination of both values already exists, the associated counter QCOUNT is incremented by one. Otherwise, a new query identifier QID is receivedand the data is written to the PMQUERIES table. Second, all parameter marker bind values are inserted into the PMVALUES table according to the insertion of the query text. Third, the information about the combination of the bind values (i.e., the bindvalue set) is written to the PMCOMBINATIONS table. If the bind value set is already included in the PMCOMBINATIONS table, then the counter CCOUNT is incremented. Otherwise, a new bind value set identifier CID is generated and a row is inserted into thePMCOMBINATIONS table for each bind value of the bind value set. Finally, the query identifier, the bind value set identifier, the timestamp of the query's execution and the amount of time taken to execute the query are inserted into the PMEXECUTIONStable.

Plan Space Analysis

FIG. 4A is a process of analyzing a query execution plan space within the process of FIG. 2, in accordance with embodiments of the present invention. The query execution plan space analysis process begins at step 400. In step 402, plan spaceanalyzer 110 (see FIG. 1) requests and obtains the query text, the name of the database on which the query executed, and parameter marker bind value sets collected in step 202 (see FIG. 2). In one example, step 402 requests and obtains the query andbind value set information from PM tables 108 (see FIG. 1).

Prior to step 404, plan space analyzer 110 (see FIG. 1) employs a database facility to generate Explain information for each query whose query text is obtained in step 402 (hereinafter referred to as the "retrieved queries"). Generating Explaininformation for a query is also referred to herein as explaining the query (e.g., by composing and executing an EXPLAIN query). Each of the retrieved queries is explained with all of the retrieved query's bind value sets. In order to make each of theretrieved queries explainable, the retrieved query's one or more parameter markers are replaced by the bind values of one of the retrieved query's bind value sets. Via the Explain facility being used, the plan space analyzer assigns a query identifier(e.g., QUERYNO) to each bind value set and generates a query tag (e.g., QUERYTAG) for each of the retrieved queries. The generated query tag includes the current timestamp and a random number. As one example, each retrieved query is explained with itsQUERYTAG and its bind value set's QUERYNO. Thus, the query identifier and query tag uniquely identifies the Explain information of every retrieved query for each of the query's bind value sets.

As one example, the plan space analyzer uses a DB2.RTM. facility to store Explain information in database tables called Explain tables. The Explain information stored in the Explain tables is generated while optimizing a query. If the Explainfacility of DB2.RTM. is activated with the command SET CURRENT EXPLAIN MODE YES, the execution plan of a query is stored in the Explain tables when the query is executed.

As another example, Explain information is generated by composing and executing a Structured Query Language (SQL) statement EXPLAIN, which captures Explain information about the query execution plan for a supplied explainable statement andplaces the Explain information into Explain tables. An explainable statement is one of the following SQL statements: DELETE, INSERT, SELECT, SELECT INTO, UPDATE, VALUES, or VALUES INTO. The present invention employs the SQL EXPLAIN statement's SETQUERYNO and SET QUERYTAG options to set the aforementioned query identifiers and query tags, respectively, to uniquely identify the Explain information. A sample SQL EXPLAIN statement that explains a query prior to step 404 is shown in FIG. 4D.

In steps 404 and 406, plan space analyzer 110 (see FIG. 1) uses the query identifier and query tag to obtain Explain information from the aforementioned Explain tables for each bind value set of a retrieved query. In step 404, the Explaininformation obtained includes selectivities for all parameter marker predicates for each bind value set of each retrieved query. In another embodiment, cardinality measurements (a.k.a. absolute measurements) for each bind value set are obtained in step404 instead of selectivities. In step 406, the Explain information obtained includes query execution plan information and estimated execution costs for each bind value set of each retrieved query. Each obtained estimated execution cost is an estimateof the cost of using an obtained query execution plan to optimally execute the retrieved query with a particular bind value set.

In one embodiment, the plan space analyzer assigns unique execution plan IDs to the query execution plans, so that all identical query execution plans have the same execution plan ID. In this case, step 406 obtains the execution plan ID insteadof all of the query execution plan information.

In step 410, plan space analyzer 110 (see FIG. 1) determines one or more pairs of parameter marker bind value sets (p1, . . . , pn).sub.i, (q1, . . . , qn).sub.i where P.sub.i is an optimal query execution plan associated with (p1, . . . ,pn).sub.i, Q.sub.i is an optimal query execution plan associated with (q1, . . . , qn).sub.i and i.gtoreq.1. The plan space analyzer determines a distance d.sub.i between a first selectivity measurement set (i.e., a set of one or more selectivitymeasurements) associated with (p1, . . . , pn).sub.i and a second selectivity measurement set associated with (q1, . . . , qn).sub.i. Each distance d.sub.i is a maximum distance between any pair of selectivity measurement sets, where the firstselectivity measurement set of the pair is associated with query execution plan P.sub.i and the second selectivity measurement set of the pair is associated with Q.sub.i. In another embodiment, step 410 determines the bind value set pairs that areassociated with maximum distances between cardinality measurements.

In step 412, for the one or more bind value set pairs determined in step 410, plan space analyzer 110 (see FIG. 1) determines one or more pairs of execution costs C1.sub.i and C2.sub.i where C1.sub.i is an estimated cost of executing the queryvia forcing the use of bind value set (q1, . . . , qn).sub.i with query execution plan P.sub.i and where C2.sub.i is an estimated cost of executing the query via forcing the use of bind value set (p1, . . . , pn).sub.i with query execution planQ.sub.i. This forcing of bind value sets with sub-optimal query execution plans is referred to herein as cross-forced plans.

In step 414, plan space analyzer 110 (see FIG. 1) compares each of the costs determined in step 412 with the cost of executing the query via a corresponding optimal query execution plan. The plan space analyzer determines one or more pairs ofdifferences D1.sub.i, D2.sub.i, where D1.sub.i is a difference between execution cost C1.sub.i and an optimal execution cost OC1.sub.i and D2.sub.i is a difference between execution cost C2.sub.i and an optimal execution cost OC2.sub.i. Optimalexecution costs OC1.sub.i and OC2.sub.i are included in the execution costs determined in step 406. OC1.sub.i is a cost of optimally executing the query via query execution plan Q.sub.i with bind value set (q1, . . . , qn).sub.i and OC2.sub.i is a costof optimally executing the query via query execution plan P.sub.i with bind value set (p1, . . . , pn).sub.i.

If none of the differences in the one or more pairs of differences D1.sub.i, D2.sub.i exceed a predefined threshold value (i.e., the costs in each pair of costs compared in step 414 are substantially similar), then plan space analyzer 110 (seeFIG. 1) recommends ReOpt Once as the optimization frequency for the query being analyzed. On the other hand, if at least one difference of the one or more pairs of differences D1.sub.i, D2.sub.i exceeds the predefined threshold value (i.e., costs in atleast one of the pairs of costs compared in step 414 differ substantially), then plan space analyzer 110 (see FIG. 1) recommends ReOpt Always as the optimization frequency for the query being analyzed.

FIG. 4B is an example of a table from which query information and bind value sets are requested and obtained in the process of FIG. 4A, in accordance with embodiments of the present invention. Table 420 includes examples of a query text, adatabase name and bind value sets requested and obtained by plan space analyzer 110 (see FIG. 1) in step 402 of FIG. 4A. Each parameter marker in the query text in table 420 is indicated by a question mark. In one embodiment, the query text, databasename and bind value sets are obtained from PM tables 108 (see FIG. 1).

FIG. 4C is an example of a query in which parameter markers are replaced by bind values, where the query's text is obtained from the table in FIG. 4B, in accordance with embodiments of the present invention. After step 402 (see FIG. 4A), theone or more parameter markers of the query being analyzed by the process of FIG. 4A are replaced by the one or more bind values in the bind value set obtained in step 402 (see FIG. 4A) and the resulting query is explained. Query 430 is an example of aquery in which the two bind values of the first listed bind value set in table 420 (see FIG. 4B) replace their corresponding parameter markers in the query text of table 420 (see FIG. 4B). That is, the first listed bind value set in FIG. 4B is (1998,63817) and therefore in query 430, 1998 and 63817 replace the first question mark and second question mark, respectively, in the query text of FIG. 4B. Explaining query 430 is illustrated in FIG. 4D.

FIG. 4D is an example of an Explain query generated from the query in FIG. 4C, in accordance with embodiments of the present invention. Explain query 440 is an example of a query used to explain query 430 (see FIG. 4C) subsequent to replacing aquery's parameter markers with the bind values of a bind value set obtained in step 402 (see FIG. 4A). For each query being considered by the analysis process of FIG. 4A, an Explain query is composed and executed for each bind value set. The executionof each Explain query results in a query execution plan. The combination of QUERYTAG and QUERYID in Explain query 440 is unique and is used to identify the explain information later in the process of FIG. 4A.

Comparing Execution Costs

Although not shown in FIG. 4A, an initial criterion in the decision to use ReOpt Once or ReOpt Always is the number of different query execution plans associated with the query whose plan space is being analyzed. If only one query executionplan is determined in step 406 (see FIG. 4A) for all bind value sets obtained in step 402 (see FIG. 4A), then ReOpt Once is selected as the recommended optimal optimization frequency and the subsequent analysis steps of FIG. 4A are not required. In thiscase, ReOpt Always has the same execution cost as using ReOpt Once because ReOpt Always uses the same query execution plan after each reoptimization, but the optimization cost of ReOpt Always is significantly higher than using ReOpt Once.

If more than one query execution plan is determined for a query in step 406 (see FIG. 4A), then plan space analyzer 110 (see FIG. 1) makes an optimization frequency recommendation based on comparisons of estimated execution costs.

If a query is explained in step 406 (see FIG. 4A) with more than one query execution plan, then plan space analyzer 110 (see FIG. 1) recommends ReOpt Once in step 414 (see FIG. 4A) if using any of the query execution plans for all bind valuesets does not result in execution costs that differ significantly from the execution costs associated with using the optimal query execution plan in each case. Otherwise, the plan space analyzer recommends ReOpt Always as the optimization frequency. That is, ReOpt Always is recommended if using any of the query execution plans for all bind value sets results in execution costs that differ significantly from the execution costs associated with using the optimal query execution plan in each case.

Assuming the worst case, the maximum difference in execution costs for a bind value set has to be determined. However, comparing the execution costs of all bind value sets of a query for all query execution plans is very expensive and timeconsuming. To reduce the number of cost comparisons, plan space analyzer 110 (see FIG. 1) selects suitable bind value sets to be compared. An example of selecting suitable bind value sets and making the cost comparisons is illustrated in FIGS. 4E-4G.

The selection of bind value sets for the execution cost comparison is based on the distance of their bind values in selectivity. The optimizer's choice of the optimal execution plan is heavily dependent on the selectivity of local predicates. In another embodiment, the selection of the bind value sets for the execution cost comparison is based on distances between cardinality measurements.

In the description of selecting bind value sets that follows, A is a set of the bind value sets A.sub.1 to A.sub.m and B is a set of bind value sets B.sub.1 to B.sub.n, as shown in (1) and (2) presented below. All bind value sets of A and B arebind value sets of the query Q. A={A.sub.1,A.sub.2, . . . ,A.sub.m} (1) B={B.sub.1,B.sub.2, . . . ,B.sub.n} (2)

All bind value sets of A use P.sub.A as the optimal execution plan and all bind value sets of B use P.sub.B as the optimal execution plan, as shown in (3) and (4) presented below: A.fwdarw.P.sub.A (3) B.fwdarw.P.sub.B (4)

C(V,P) is the estimated execution cost of the query Q with the bind value set V and the execution plan P.

D.sub.A and D.sub.B are the differences in execution costs between using P.sub.A and P.sub.B, as shown in (5) and (6) presented below: D.sub.A=C(A.sub.i,P.sub.B)-C(A.sub.i,P.sub.A) (5) D.sub.B=C(B.sub.j,P.sub.A)-C(B.sub.j,P.sub.B) (6)

D.sub.A and D.sub.B are supposed to be maximal if the value sets A.sub.i and B.sub.j have a maximum distance in selectivity to each other because of the execution plan's dependency on selectivity.

Therefore, the criterion for selecting bind value sets is the distance between bind value sets in selectivity. For each combination of two query execution plans P.sub.A and P.sub.B, one pair of bind value sets A.sub.i and B.sub.j is selected. A.sub.i and B.sub.j are the bind value sets with the maximum distance in selectivity.

The process of selecting bind value sets is illustrated by plan space diagrams in FIGS. 4E-4G. A plan space diagram shows the distribution of execution plans in the selectivity space of a query. The selectivity measurements of the parametermarker predicates are plotted on the axes of the coordinate system of a plan space diagram. In another embodiment, cardinality measurements of the parameter marker predicates are plotted on the axes. Each point represents one bind value set. In FIGS.4E-4G, the shape and fill color of a circular or rectangular point on a plan space diagram indicates the query execution plan associated with the bind value set represented by that point. The number of parameter markers in a query whose plan space isbeing analyzed is equivalent to the number of dimensions in the plan space diagram.

FIG. 4E is an exemplary plan space diagram illustrating a query execution plan space being analyzed by the process of FIG. 4A, in accordance with embodiments of the present invention. Plan space diagram 450 illustrates the plan space of a querythat has two parameter markers. The bind value sets indicated by the points in plan space diagram 450 are grouped by execution plan ID in step 408 of FIG. 4A. In this case, each bind value set is associated with one of three query execution plans(i.e., Plan 1, Plan 2 and Plan 3), which are indicated in diagram 450 by the white-filled circles (i.e., Plan 1), black-filled circles (i.e., Plan 2) and the black-filled rectangles (i.e., Plan 3).

The goal of the selection of the bind value sets is it to determine pairs of points on a plan space diagram where the bind value sets represented by each pair of points are associated with different query execution plans Plan I and Plan II andwhere the points are at a maximum distance from each other, as compared to other pairs of points also associated with Plan I and Plan II. In one embodiment, distances between all points on a plan space diagram are calculated to determine theaforementioned pairs of points.

In equation (7a) presented below, D.sub.E is the Euclidean distance between two points P and Q in an n-dimensional space.

.times..times. ##EQU00002##

For comparison purposes, a distance measure D is sufficient, as shown below in expression (7b). The cost of the computation of the distance D is denoted by C(D).

.times..times. ##EQU00003##

The overall cost to select desired points out of m points in an n-dimensional space is shown below in expression (8):

.function. ##EQU00004##

As the overall cost represented by expression (8) rises quadratically for an increasing number of points and linearly for an increasing number of dimensions, the method of selecting bind value sets by calculating the distances between all pointsin a plan space diagram is expensive.

In a second embodiment, in order to reduce the number of distance calculations and male the process less expensive, each point in a plan space diagram that is totally surrounded by points associated with the same query execution plan as thepoint being considered is ignored (a.k.a. sorted out) in the determination of the maximum distances. Points in plan space diagram 450 that are sorted out are indicated by triangular points in a plan space diagram 460 of FIG. 4F. In other words, pointsthat indicate bind value sets with minimum or maximum selectivities remain as circular or rectangular points in FIG. 4F, but points representing midrange bind value sets are ignored to simplify the maximum distances determination in step 410 of FIG. 4A.

A point P is totally surrounded if in every orthant of an n-dimensional Cartesian coordinate system with its origin in the point P, a point with the same shape and fill color as P exists. After a point is classified as surrounded, the pointcannot be used to surround other points. Therefore, in an n-dimensional space, at least 2.sup.n points are not surrounded by other points with the same shape and fill color.

The costs for this algorithm for m points and n dimensions are shown below in expressions (9) and (10), where C(P) denotes the cost to compare the position of two points. In the worst case (i.e., expression (9)), no bind value set is sortedout. In the best case (i.e., expression (10)), all bind value sets are sorted out except for the aforementioned 2.sup.n points.

.times..times..times..times..times..function..times..times..times..times.- .times..function. ##EQU00005## Using the sort out algorithm to reduce the number of points to be considered for the distance calculation, the final costs for m pointsand n dimensions are presented below in expressions (11) and (12), in which C(D) is the cost of the distance calculation and C(P) is the cost to compare the position of two points.

In the worst case (i.e., expression (11)), no point is sorted out and every point is combined two times with every other point. Comparing the worst case (i.e., expression (11)) to the original costs in expression (8) presented above, the worstcase is three times as expensive as the original cost of determining the distances between all points.

Comparing the best case (i.e., expression (12)) with the original costs in expression (8), the number of distance computations is reduced to 2.sup.n.

After using the sort out algorithm to generate plan space diagram 460 in FIG. 4F, maximum distances between selectivity measurements in the plan space diagram are determined in step 410 (see FIG. 4A). The maximum selectivity distances arebetween bind value sets associated with different query execution plans. The line segments drawn between the larger circular or rectangular points in plan space diagram 470 in FIG. 4G indicate the maximum selectivity distances. The larger circular orrectangular points at the endpoints of a line segment in FIG. 4G represent a pair of bind value sets used in a determination of estimated execution costs in step 412 (see FIG. 4A). Hereinafter, each pair of bind value sets identified in step 410 (seeFIG. 4A) is also referred to as an identified pair. Thus, the three line segments joining points in FIG. 4G identify three pairs of bind value sets. That is, a first line segment joins a first pair of bind value sets associated with Plan 1 and Plan 2,a second line segment joins a second pair of bind value sets associated with Plan 1 and Plan 3, and a third line segment joins a third pair of bind value sets associated with Plan 2 and Plan 3.

After identifying the pairs of bind value sets that have a maximum distance in selectivity, the differences in estimated execution costs are determined in step 414 (see FIG. 4A). Each difference determined in step 414 (see FIG. 4A) is between afirst execution cost of sub-optimally executing a query with a first query execution plan that is forced to use a bind value set of an identified pair and a second execution cost of optimally executing the query with a second query execution plan usingthe same bind value set. The first execution plan is the plan for optimally executing the query with the other bind value set in the identified pair.

As the optimal execution costs for each bind value set in the identified pairs are already known from the explain information generated in step 206 (see FIG. 2), step 412 (see FIG. 4A) does not need to determine those optimal execution costs. In step 412, for each query execution plan associated with a bind value set in an identified pair, plan space analyzer 110 (see FIG. 1) determines the suboptimal execution cost of forcing the query execution plan to use the other bind value set in theidentified pair. This determination of the suboptimal execution costs in step 412 is performed via explaining the query with cross forced plans. Thus, for each identified pair of bind value sets A and B, which are associated with optimal queryexecution plans P and Q, respectively, the query is explained in step 412 to determine a first suboptimal execution cost of using query execution plan P with bind value set B and a second suboptimal execution cost of using query execution plan Q withbind value set A.

In one embodiment, a database hint feature is employed in step 412 (see FIG. 4A) to force the query to use suboptimal query execution plans and subsequently determine the aforementioned suboptimal execution costs. As one example, a databasehint is a syntax addition to a SQL query (e.g., embedded within a comment of the SQL query) that overrides the database optimizer and controls the execution plan of the SQL query.

After explaining the query with all of the identified pairs of bind value sets using cross forced plans, the differences determined between the suboptimal execution costs and the optimal costs are analyzed in step 414 (see FIG. 4A). If all ofthe differences do not exceed a predefined threshold value, then plan space analyzer 110 (see FIG. 1) recommends ReOpt Once as the optimization frequency for the query. That is, ReOpt Once is recommended if each suboptimal execution cost and itsassociated optimal execution cost determined in step 412 (see FIG. 4A) are substantially similar. The predefined threshold value takes into account the optimization costs associated with ReOpt Always.

In the case of the three identified pairs of bind value sets in plan space diagram 470, six suboptimal execution costs are determined in step 412 (see FIG. 4A) and six comparisons are made in step 414 to determine six differences between thesuboptimal execution costs and their associated optimal execution costs.

Computing System

FIG. 5 is a computing system that includes components of the system of FIG. 1 and implements the processes of FIGS. 2, 3A and 4A, in accordance with embodiments of the present invention. Computing unit 500 is suitable for storing and/orexecuting program code of a system for automatically determining optimization frequencies of queries with parameter markers 514, and generally comprises a central processing unit (CPU) 502, a memory 504, an input/output (I/O) interface 506, a bus 508,I/O devices 510 and a storage unit 512. Optimization frequency determination system 514 includes, for example, extract & transform tool 106 (see FIG. 1) and plan space analyzer 110 (see FIG. 1). CPU 502 performs computation and control functions ofcomputing unit 500. CPU 502 may comprise a single processing unit, or be distributed across one or more processing units in one or more locations (e.g., on a client and server).

Local memory elements of memory 504 are employed during actual execution of the program code of optimization frequency determination system 514. Cache memory elements of memory 504 provide temporary storage of at least some program code inorder to reduce the number of times code must be retrieved from bulk storage during execution. Further, memory 504 may include other systems not shown in FIG. 5, such as an operating system (e.g., Linux) that runs on CPU 502 and provides control ofvarious components within and/or connected to computing unit 500.

Memory 504 may comprise any known type of data storage and/or transmission media, including bulk storage, magnetic media, optical media, random access memory (RAM), read-only memory (ROM), a data cache, a data object, etc. Storage unit 512 is,for example, a magnetic disk drive or an optical disk drive that stores data. Moreover, similar to CPU 502, memory 504 may reside at a single physical location, comprising one or more types of data storage, or be distributed across a plurality ofphysical systems in various forms. Further, memory 504 can include data distributed across, for example, a LAN, WAN or storage area network (SAN) (not shown).

I/O interface 506 comprises any system for exchanging information to or from an external source. I/O devices 510 comprise any known type of external device, including a display monitor, keyboard, mouse, printer, speakers, handheld device,printer, facsimile, etc. Bus 508 provides a communication link between each of the components in computing unit 500, and may comprise any type of transmission link, including electrical, optical, wireless, etc.

I/O interface 506 also allows computing unit 500 to store and retrieve information (e.g., program instructions or data) from an auxiliary storage device (e.g., storage unit 512). The auxiliary storage device may be a non-volatile storage device(e.g., a CD-ROM drive which receives a CD-ROM disk). Computing unit 500 can store and retrieve information from other auxiliary storage devices (not shown), which can include a direct access storage device (DASD) (e.g., hard disk or floppy diskette), amagneto-optical disk drive, a tape drive, or a wireless communication device.

The invention can take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment containing both hardware and software elements. In a preferred embodiment, the invention is implemented in software, whichincludes but is not limited to firmware, resident software, microcode, etc.

Furthermore, the invention can take the form of a computer program product accessible from a computer-usable or computer-readable medium providing program code of optimization frequency determination system 514 for use by or in connection with acomputing unit 500 or any instruction execution system to provide and facilitate the capabilities of the present invention. For the purposes of this description, a computer-usable or computer-readable medium can be any apparatus that can contain, store,communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device.

The medium can be an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system (or apparatus or device) or a propagation medium. Examples of a computer-readable medium include a semiconductor or solid state memory,magnetic tape, a removable computer diskette, RAM 504, ROM, a rigid magnetic disk and an optical disk. Current examples of optical disks include compact disk-read-only memory (CD-ROM), compact disk-read/write (CD-R/W) and DVD.

The flow diagrams depicted herein are provided by way of example. There may be variations to these diagrams or the steps (or operations) described herein without departing from the spirit of the invention. For instance, in certain cases, thesteps may be performed in differing order, or steps may be added, deleted or modified. All of these variations are considered a part of the present invention as recited in the appended claims.

While embodiments of the present invention have been described herein for purposes of illustration, many modifications and changes will become apparent to those skilled in the art. Accordingly, the appended claims are intended to encompass allsuch modifications and changes as fall within the true spirit and scope of this invention.