Florian M. Waas
ABSTRACT
Plan regressions pose a significant problem in commercial database systems: Seemingly innocuous changes to a query optimizer component such as the cost model or the search strategy in order to enhance optimization results may result in unexpected and detrimental changes to previously satisfactory query plans.
Database vendors spend substantial resources on quality assurance to guard against this very issue, yet, testing for plan regressions in optimizers has proven hard and inconclusive. This is due to the nature of the problem: the optimizer chooses a single plan---Best Plan Found (bpf)---from a search space of literally up to hundreds of millions of different plan alternatives. It is standard practice to use a known good bpf and test for changes to this plan, i. e., ensure that no changes have occurred. However, in the vast majority of cases the bpf is not be affected by a code-level change, even though the change is known to affect many plans in the search space.
In this paper, we propose a holistic approach to address this issue. Instead of focusing on test suites consisting of BPFS we take the entire search space into account. We introduce a metric to assess the optimizer's accuracy across the entire search space.
We present preliminary results using a commercial database system, demonstrate the usefulness of our methodology with a standard benchmark, and illustrate how to build such an early warning system.
- R. Ahmed. Query Processing in Oracle DBMS. In Proc. Int'l. Workshop on Data Warehousing and OLAP, 2010. Google Scholar
Digital Library
- M. Elhemali and L. Giakoumakis. Unit Testing Query Transformation Rules. In Proc. Int'l. Workshop on Database Testing (DBTest), 2008. Google ScholarDigital Library
- H. G. Elmongui, V. Narasayya, and R. Ramamurthy. A Framework for Testing Query Transformation Rules. In Proc. Int'l. Conf. ACM SIGMOD, 2009. Google ScholarDigital Library
- L. Giakoumakis and C. Galindo-Legaria. Testing SQL Servers Query Optimizer: Challenges, Techniques and Experiences. IEEE Data Engineering Bulletin, 31(1), 2008.Google Scholar
- G. Graefe, A.-C. König, H. Kuno, V. Markl, and K.-U. Sattler. Robust Query Processing. Technical Report Workshop 10381, Dagstuhl, 2010.Google Scholar
- J. S. Maritz. Distribution-Free Statistical Methods. Chapman & Hall, 1981.Google ScholarCross Ref
- N. Reddy and J. Haritsa. Analyzing Plan Diagrams of Database Query Optimizers. In Proc. Int'l. Conf. on Very Large Databases, 2005. Google ScholarDigital Library
- F. Waas and C. A. Galindo-Legaria. Counting, Enumerating, and Sampling of Execution Plans in a Cost-Based Query Optimizer. In Proc. Int'l. Conf. ACM SIGMOD, 2000. Google ScholarDigital Library
Index Terms
- Plan space analysis: an early warning system to detect plan regressions in cost-based optimizers
Recommendations
Continuing plan quality optimisation
Finding high quality plans for large planning problems is hard. Although some current anytime planners are often able to improve plans quickly, they tend to reach a limit at which the plans produced are still very far from the best possible, but these ...
On Plan Adaption through Planning Graph Analysis
AI*IA '99: Proceedings of the 6th Congress of the Italian Association for Artificial Intelligence on Advances in Artificial IntelligenceFast plan adaptation is important in many AI applications requiring a plan management module. From a theoretical point of view, in the worst case plan adaptation is no more efficient than a complete regeneration of the plan. However, in practice ...
Plan stability: replanning versus plan repair
ICAPS'06: Proceedings of the Sixteenth International Conference on International Conference on Automated Planning and SchedulingThe ultimate objective in planning is to construct plans for execution. However, when a plan is executed in a real environment it can encounter differences between the expected and actual context of execution. These differences can manifest as ...
Comments