Progressive Parametric Query Optimization

[POPOOLA MIKE]

---

Pedro Bizarro, Nicolas Bruno, and David J. DeWitt

Abstract
Commercial applications usually rely on precompiled parameterized procedures to interact with a database. Unfortunately,
executing a procedure with a set of parameters different from those used at compilation time may be arbitrarily suboptimal. Parametric
query optimization (PQO) attempts to solve this problem by exhaustively determining the optimal plans at each point of the parameter
space at compile time. However, PQO is likely not cost-effective if the query is executed infrequently or if it is executed with values only
within a subset of the parameter space. In this paper, we propose instead to progressively explore the parameter space and build a
parametric plan during several executions of the same query. We introduce algorithms that, as parametric plans are populated, are
able to frequently bypass the optimizer but still execute optimal or near-optimal plans.

INTRODUCTION
IN many applications, the values of runtime parameters of the system, data, or queries themselves are unknown when queries are originally optimized. In these scenarios, there are typically two trivial alternatives to deal with the optimization and execution of such parameterized queries. One approach, termed here as Optimize-Always, is to call the optimizer and generate a new execution plan every time a new instance of the query is invoked. Another trivial approach, termed Optimize-Once, is to optimize the query just once, with some set of parameter values, and reuse the resulting physical plan for any subsequent set of parameters. Both approaches have clear disadvantages. Optimize- Always requires an optimization call for each execution of a query instance. These optimization calls may be a significant part of the total query execution time, especially for simple queries. In addition, Optimize-Always may limit the number of concurrent queries in the system, as the optimization process itself may consume too much memory. On the other hand, Optimize-Once returns a single plan that is used for all points in the parameter space. The chosen plan may be arbitrarily suboptimal for parameter values different from those for which the query was originally optimized.

for more:
http://docs.googleviewer?a=v&q=cache:lrV...dei.uc.pt/ bizarro/papers/tkde2009_ppqo.pdf+Progressive+Parametric+Query+Optimization+pdf&hl=en&gl=in&pid=bl&srcid=ADGEESggZZrP9A22wIy25hsr8rm8ARMIYElZnUEVKJO9pPUx9uWh7N9E6BjiOoLWm_39Ndg1DBbSrJ7i9nw5npw1Yfldtjo1j4-Vjwygcqas4xb6Ipigo991EbuFnnxjSS-EJseISV_A&sig=AHIEtbT_AplOhRavopJkWyXH8h8YvCyLNw

---

[ASWATHI]

Commercial applications usually rely on precompiled parameterized procedures to interact with a database. Unfortunately, executing a procedure with a set of parameters different from those used at compilation time may be arbitrarily suboptimal. Parametric query optimization (PQO) attempts to solve this problem by exhaustively determining the optimal plans at each point of the parameter space at compile time. However, PQO is likely not cost-effective if the query is executed infrequently or if it is executed with values only within a subset of the parameter space. In this paper, we propose instead to progressively explore the parameter space and build a parametric plan during several executions of the same query. We introduce algorithms that, as parametric plans are populated, are able to frequently bypass the optimizer but still execute optimal or near-optimal plans.