Automatically determining optimization frequencies of queries with parameter markers
First Claim
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.
0 Assignments
0 Petitions
Accused Products
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.
-
Citations
17 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. - View Dependent Claims (2, 3, 4, 5, 6, 7)
-
-
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. - View Dependent Claims (9, 10, 11, 12, 13)
-
-
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 di between a first measurement set S1i associated with said bind value set (p1, . . . , pn)i and a second measurement set S2i associated with said (q1, . . . , qn)i, said S1i and said S2i included in said plurality of measurement sets, wherein each distance di is a maximum distance between any pair of measurement sets associated with query execution plans Pi and Qi of said plurality of query execution plans, wherein said query execution plan Pi is an optimal query execution plan associated with said bind value set (p1, . . . , pn)i and said query execution plan Qi 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 C1i, C2i of a second set of execution costs, wherein said C1i is a cost of executing said query via said query execution plan Pi with bind value set (q1, . . . , qn)i and said C2i is a cost of executing said query via said query execution plan Qi with bind value set (p1, . . . , pn)i, wherein said determining said one or more pairs of execution costs C1i, C2i of said second set of execution costs includes; using a database hint to force said query to use said query execution plan Pi with bind value set (q1, . . . , qn)i, and using said database hint to force said query to use said query execution plan Qi with bind value set (p1, . . . , pn)i; determining, by said computing system, one or more pairs of differences D1i and D2i, wherein said D1i is a difference between said cost C1i and an optimal execution cost OC1i of said first set of execution costs and said D2i is a difference between said cost C2i and an optimal execution cost OC2i of said first set of execution costs, wherein said OC1i is a cost of optimally executing said query via said query execution plan Qi with bind value set (q1, . . . , qn)i, and said OC2i is a cost of optimally executing said query via said query execution plan Pi 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 D1i and D2i 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. - View Dependent Claims (15, 16, 17)
-
Specification