Automatically determining optimization frequencies of queries with parameter markers

Abstract

A method for automatically determining optimization frequencies of queries having one or more parameter markers. Bind value sets and associated measurement sets are obtained. Ouerv execution plans and associated execution costs for optimal query execution with a bind value set are determined. Bind value set pairs for execution plans with maximum distance in selectivity or cardinality are determined. Execution costs for all pairs of plans with maximum selectivity/cardinality distance are determined. An optimization frequency is selected based on differences between the determined execution costs and optimal execution costs. 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.

1 - 15 . (canceled) 
   
   
       16 . A computer-implemented method of determining an optimization frequency of a query having one or more parameter markers, said method comprising:
 obtaining, by a computing system, a plurality of bind value sets, each bind value set including one or more bind values and associated with one or more parameter markers of a query;   obtaining, by said computing system, a plurality of measurement sets associated with said bind value sets in a one-to-one correspondence, each measurement set selected from the group consisting of one or more selectivity measurements and one or more cardinality measurements;   determining, by said computing system, a plurality of query execution plans, each query execution plan capable of optimally executing said query with one or more bind value sets of said plurality of bind value sets;   determining, by said computing system, a first set of execution costs associated with said query execution plans of said plurality of query execution plans in a one-to-one correspondence, each execution cost of said first set being a cost of optimally executing said query with a bind value set of said plurality of bind value sets;   determining, by said computing system, one or more pairs of bind value sets (p 1 , . . . , pn) i , (q 1 , . . . , qn) i  of said plurality of bind value sets, said determining said one or more pairs of bind value sets including determining one or more distances d i  between a first measurement set S 1   i  associated with said bind value set (p 1 , . . . , pn) i  and a second measurement set S 2   i  associated with said (q 1 , . . . , qn) i , said S 1   i  and said S 2   i  included in said plurality of measurement sets, wherein each distance d i  is a maximum distance between any pair of measurement sets associated with query execution plans P i  and Q i  of said plurality of query execution plans, wherein said query execution plan P i  is an optimal query execution plan associated with said bind value set (p 1 , . . . , pn) i  and said query execution plan Q i  is an optimal query execution plan associated with said bind value set (q 1 , . . . , qn) i , and wherein said i≧1;   determining, by said computing system, one or more pairs of execution costs C 1 , C 2   i  of a second set of execution costs, wherein said C 1   i  is a cost of executing said query via said query execution plan P i  with bind value set (q 1 , . . . , qn) i  and said C 2   i  is a cost of executing said query via said query execution plan Q i  with bind value set (p 1 , . . . , pn) i , wherein said determining said one or more pairs of execution costs C 1   i , C 2   i  of said second set of execution costs includes:
 using a first database hint to force said query to use said query execution plan P i  with bind value set (q 1 , . . . , qn) i , and 
 using a second database hint to force said query to use said query execution plan Q i  with bind value set (p 1 , . . . , pn) i ; 
   determining, by said computing system, one or more pairs of differences D 1   i  and D 2   i , wherein said D 1   i  is a difference between said cost C 1   i  and an optimal execution cost OC 1   i  of said first set of execution costs and said D 2   i  is a difference between said cost C 2   i  and an optimal execution cost OC 2   i  of said first set of execution costs, wherein said OC 1   i  is a cost of optimally executing said query via said query execution plan Q i  with bind value set (q 1 , . . . , qn) i , and said OC 2   i  is a cost of optimally executing said query via said query execution plan P i  with bind value set (p 1 , . . . , pn) i ; automatically selecting, by said computing system, an optimization frequency, wherein said optimization frequency is selected from the group consisting of optimizing said query once and reoptimizing said query each time said query is executed; and   storing said optimization frequency in a computer-usable medium,   wherein said optimization frequency is said optimizing said query once as a result of a first determination, via said determining said one or more pairs of differences, that no difference of said one or more pairs of differences exceeds a predefined threshold value, and   wherein said optimization frequency is said reoptimizing said query each time said query is executed as a result of a second determination, via said determining said one or more pairs of differences, that at least one difference of said one or more pairs of differences exceeds said predefined threshold value.   
   
   
       17 - 20 . (canceled)