Patent ID: 7987178
Filing Date: 2011-07-26
Classification: G06F

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