DISCOVERY OF POTENTIAL PROBLEMATIC EXECUTION PLANS IN A BIND-SENSITIVE QUERY STATEMENT
Aspects of the present invention provide systems and methods that can help generate potential execution plans for a query statement that have one or more bind variable, whether the one or more bind variables were originally in the query statement or replaced one or more literals in the query. Embodiments of the present invention also include systems and methods for testing performance of one or more of the potential execution plans before they emerge in the production environment.
1 . A computer-implemented method for discovery of one or more execution plans for a bind-sensitive query statement comprising:
for each bind variable from a set of one or more bind variables identified in a bind-sensitive query statement, preparing how values are to be generated for the bind variable;
calculating a quota for evaluating the bind-sensitive query statement; and
performing the following steps while the quota is not exhausted:
generating a new set of bind values for at least one bind variable from the set of one or more bind variables;
evaluating whether a new execution plan is generated by a database system by the new set of bind values; and
responsive to a new execution plan being generated, storing the new execution plan with the set of bind values in a memory.
2 . The computer-implemented method of claim 1 further comprising:
flushing any cached execution plans from a cache memory of the database system in each iteration of the quota evaluation.
3 . The computer-implemented method of claim 1 further comprising:
responsive to the quota being exhausted, benchmarking at least some of the stored new execution plans.
4 . The computer-implemented method of claim 3 further comprising:
responsive to identifying a new execution plan that has an acceptable performance, selecting the new execution plan.
5 . The computer-implemented method of claim 1 wherein the step of preparing how values are to be generated for the bind variable comprises:
gathering information about how values may be generated for a bind variable by evaluating which of a set of methods may be used to obtain values for the bind variable.
6 . The computer-implemented method of claim 5 wherein the set of methods comprises:
using one or more database statistics related to at least some of the data associated with the bind variable;
sampling of data associated with the bind variable; and
using data associated with the bind variable and associated with one or more user-specified input parameters.
7 . The computer-implemented method of claim 6 wherein the step of using one or more database statistics related to at least some of the data associated with the bind variable comprising:
identifying a relevant portion of a database that is associated with a bind variable;
ascertain whether statistics are available for the relevant portion of the database; and
using the statistics to generate a value or values for the bind variable.
8 . The computer-implemented method of claim 1 wherein the step of generating a new set of bind values for at least one bind variable from the set of one or more bind variables comprises:
using, for each bind variable, one or more of methods from a set of methods that have been evaluated as being appropriate for the bind variable.
9 . A system for discovering an execution plan for a bind-sensitive query statement, the system comprising:
one or more processors;
one or more memory components communicatively coupled to the processor;
an interface communicatively coupled to the processor that facilitates accessing one or more databases; and
a bind-sensitive query execution plan discovery subsystem that comprises:
a bind value candidates preparer module that, for each bind variables from a set of one or more bind variables identified in a bind variable query statement, gathers information about how a value or values may be generated for the bind variable by evaluating which of a set of methods may be used to obtain a value or values for the bind variable;
a bind value generation module that:
receives the bind variable query statement;
receives information from the bind value candidates preparer module comprising one or more methods for generating a bind value or values for one or more bind variables in the bind variable query statement; and
generates, for each iteration in a set of iterations, a new set of bind values for at least one bind variable from the set of one or more bind variables for use in generating an execution plan using the new set of bind values:
and
a unique execution plan detection module that, for each iteration from the set of iterations:
receives the generated execution plans;
determines whether the generated execution plan is unique; and
responsive to the generated execution plan being unique, stores in memory the generated execution plan to a unique execution plan datastore along with the new set of bind values associated with the generated execution plan.
10 . The system of claim 9 wherein the bind-sensitive query execution plan discovery subsystem further comprises:
an execution plan benchmark module that benchmarks at least some of the generated execution plans in the unique execution plan datastore.
11 . The system of claim 9 wherein the bind-sensitive query execution plan discovery subsystem is further configured to perform the step of:
flushing any cached execution plans from a cache memory.
12 . The system of claim 9 wherein the bind-sensitive query execution plan discovery subsystem is further configured to perform the step of:
responsive to identifying an execution plan from the generated execution plans that has an acceptable performance, selecting the new execution plan for use with the bind variable query statement.
13 . The system of claim 9 wherein the set of methods comprises:
using one or more database statistics related to at least some of the data associated with the bind variable;
sampling of data associated with the bind variable; and
using data associated with the bind variable and associated with one or more user-specified input parameters.
14 . The system of claim 13 wherein the step of using one or more database statistics related to at least some of the data associated with the bind variable comprising:
identifying a relevant portion of a database that are associated with a bind variable;
ascertain whether statistics are available for the relevant portion of the database; and
using the statistics to generate a value or values for the bind variable.
15 . The system of claim 13 wherein the bind-sensitive query execution plan discovery subsystem further comprises:
a user interface module that receives the one or more user-specified input parameters from a user.
16 . A non-transitory computer readable medium or media comprising one or more sequences of instructions which, when executed by one or more processors, causes steps for discovery of one or more execution plans for a bind-sensitive query statement comprising:
for each bind variable from a set of one or more bind variables identified in a bind-sensitive query statement, preparing how values are to be generated for the bind variable;
calculating a quota for evaluating the bind-sensitive query statement; and
performing the following steps while the quota is not exhausted:
generating a new set of bind values for at least one bind variable from the set of one or more bind variables;
evaluating whether a new execution plan is generated by a database system by the new set of bind values; and
responsive to a new execution plan being generated, storing the new execution plan with the set of bind values in a memory.
17 . The non-transitory computer readable medium or media of claim 16 further comprising:
flushing cached execution plans from a cache memory of the database system in each iteration of the quota evaluation.
18 . The non-transitory computer readable medium or media of claim 16 further comprising:
responsive to the quota being exhausted, benchmarking at least some of the stored new execution plans; and
responsive to identifying a new execution plan that has an acceptable benchmark performance, selecting the new execution plan.
19 . The non-transitory computer readable medium or media of claim 16 wherein the step of preparing how values are to be generated for the bind variable comprises:
gathering information about how values may be generated for a bind variable by evaluating which of a set of methods may be used to obtain values for the bind variable, the set of methods comprising:
using one or more database statistics related to at least some of the data associated with the bind variable;
sampling of data associated with the bind variable; and
using data associated with the bind variable and associated with one or more user-specified input parameters.
20 . The non-transitory computer readable medium or media of claim 19 wherein the step of using one or more database statistics related to at least some of the data associated with the bind variable comprising:
identifying a relevant portion of a database that are associated with a bind variable;
ascertain whether statistics are available for the relevant portion of the database; and
using the statistics to generate a value or values for the bind variable.