EXPLOITING EXECUTION FEEDBACK FOR OPTIMIZING CHOICE OF ACCESS METHODS
A proactive monitoring mechanism for correcting the choice of access methods (available query plans) for a given query, based on execution feedback from the same query. The mechanism exploits bypassing predicate short-circuiting inside the database server's predicate evaluation module to obtain expression cardinalities. The mechanism can also modify a plan to obtain expression cardinalities. These techniques are used judiciously by the query optimizer and/or a database administrator (DBA) so that the execution overheads are within acceptable limits.
1 . A computer-implemented system for processing a query, comprising:
an execution engine for executing a query plan selected for processing a query, the engine generating execution information related to the query plan; and
an optimizer component for selecting and processing the query plan based on the execution information.
2 . The system of claim 1 , further comprising a cost component for computing a cost in overhead associated with obtaining execution feedback from the query plan.
3 . The system of claim 1 , wherein the execution information includes expression cardinality information.
4 . The system of claim 1 , wherein the optimizer component automatically selects a new query plan for execution of the query based on the execution information.
5 . The system of claim 1 , wherein the optimizer component automatically modifies the query plan for execution of the query based on the execution information.
6 . The system of claim 1 , further comprising a feedback cache for storing the execution information and statistics for operating in a passive mode or a proactive mode.
7 . The system of claim 1 , wherein the query plan uses one or more of a table scan operator, an index seek operator or an index intersection operator.
8 . The system of claim 1 , wherein the execution information output by the execution engine is obtained by bypassing a predicate short-circuiting optimization to obtain additional expression cardinality information.
9 . The system of claim 1 , wherein a bit vector is maintained for a predicate of the query plan, the bit vector tracks a result of predicate evaluation.
10 . The system of claim 1 , wherein the query accesses multiple tables and the optimizer component employs a measure that weights a query plan expression according to a size of a table.
11 . A computer-implemented method of processing a query, comprising:
selecting a current query plan for execution based on a query;
modifying execution of the current query plan to obtain a modified execution plan;
computing cost associated with processing the modified execution plan; and
automatically processing the query based on the cost.
12 . The method of claim 11 , further comprising avoiding predicate short-circuiting as part of modifying execution the current query plan to obtain an additional set of expression cardinalities.
13 . The method of claim 11 , further comprising employing one or more sampling techniques to reduce overhead associated with predicate short-circuiting and estimation of expression cardinalities.
14 . The method of claim 11 , further comprising maintaining a counter for each predicate of the current query plan to determine if requested expression cardinality information is affected by evaluation of the predicate.
15 . The method of claim 11 , further comprising modifying the current query plan by inserting additional index intersections.
16 . The method of claim 11 , further comprising selecting a new query plan based on the cost information.
17 . The method of claim 11 , further comprising identifying a set of relevant expression cardinalities to monitor for the current query plan.
18 . The method of claim 11 , further comprising maintaining upper and lower bounds for each expression cardinality value of the current query plan that is relevant to access path selection for the query.
19 . The method of claim 11 , further comprising selecting a cardinality value to monitor in the current query plan.
20 . A computer-implemented system, comprising:
computer-implemented means for selecting a current query plan for execution based on a query;
computer-implemented means for modifying execution of the query plan to obtain a modified execution plan;
computer-implemented means for computing cost associated with processing the modified execution plan; and
computer-implemented means for processing the query based on the cost.