research-article

Plan space analysis: an early warning system to detect plan regressions in cost-based optimizers

Authors:

Florian M. Waas,

Leo Giakoumakis,

Shin ZhangAuthors Info & Claims

DBTest '11: Proceedings of the Fourth International Workshop on Testing Database Systems

Article No.: 2, Pages 1 - 6

https://doi.org/10.1145/1988842.1988844

Published: 13 June 2011 Publication History

Get Access

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.

References

[1]

R. Ahmed. Query Processing in Oracle DBMS. In Proc. Int'l. Workshop on Data Warehousing and OLAP, 2010.

Digital Library

Google Scholar

[2]

M. Elhemali and L. Giakoumakis. Unit Testing Query Transformation Rules. In Proc. Int'l. Workshop on Database Testing (DBTest), 2008.

Digital Library

Google Scholar

[3]

H. G. Elmongui, V. Narasayya, and R. Ramamurthy. A Framework for Testing Query Transformation Rules. In Proc. Int'l. Conf. ACM SIGMOD, 2009.

Digital Library

Google Scholar

[4]

L. Giakoumakis and C. Galindo-Legaria. Testing SQL Servers Query Optimizer: Challenges, Techniques and Experiences. IEEE Data Engineering Bulletin, 31(1), 2008.

Google Scholar

[5]

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

[6]

J. S. Maritz. Distribution-Free Statistical Methods. Chapman & Hall, 1981.

Crossref

Google Scholar

[7]

N. Reddy and J. Haritsa. Analyzing Plan Diagrams of Database Query Optimizers. In Proc. Int'l. Conf. on Very Large Databases, 2005.

Digital Library

Google Scholar

[8]

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.

Digital Library

Google Scholar

Cited By

View all

Lan HZhang YDong YHuang DTang LZhang J(2023)Why Query Plans Are Different: An Automatic Detection and Inference SystemDatabases Theory and Applications10.1007/978-3-031-47843-7_15(213-226)Online publication date: 7-Nov-2023
https://doi.org/10.1007/978-3-031-47843-7_15
Leis VRadke BGubichev AMirchev ABoncz PKemper ANeumann T(2018)Query optimization through the looking glass, and what we found running the Join Order BenchmarkThe VLDB Journal — The International Journal on Very Large Data Bases10.1007/s00778-017-0480-727:5(643-668)Online publication date: 1-Oct-2018
https://dl.acm.org/doi/10.1007/s00778-017-0480-7
Li ZPapaemmanouil OCherniack MMukhopadhyay SZhai CBertino ECrestani FMostafa JTang JSi LZhou XChang YLi YSondhi P(2016)OptMarkProceedings of the 25th ACM International on Conference on Information and Knowledge Management10.1145/2983323.2983658(2155-2160)Online publication date: 24-Oct-2016
https://dl.acm.org/doi/10.1145/2983323.2983658
Show More Cited By

Index Terms

Plan space analysis: an early warning system to detect plan regressions in cost-based optimizers
1. Information systems
  1. Data management systems
    1. Database management system engines
      1. Database query processing
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory
      1. Database query processing and optimization (theory)

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 Intelligence

Fast 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 Scheduling

The 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

Information & Contributors

Information

Published In

DBTest '11: Proceedings of the Fourth International Workshop on Testing Database Systems

June 2011

51 pages

ISBN:9781450306553

DOI:10.1145/1988842

Conference Chairs:
Goetz Graefe,
Kenneth Salem

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 13 June 2011

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Qualifiers

Research-article

Conference

SIGMOD/PODS '11

Sponsor:

SIGMOD

SIGMOD/PODS '11: International Conference on Management of Data

June 13, 2011

Athens, Greece

Acceptance Rates

Overall Acceptance Rate 31 of 56 submissions, 55%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

6
Total Citations
View Citations
221
Total Downloads

Downloads (Last 12 months)5
Downloads (Last 6 weeks)1

Reflects downloads up to 31 Dec 2024

Other Metrics

View Author Metrics

Citations

Cited By

View all

Lan HZhang YDong YHuang DTang LZhang J(2023)Why Query Plans Are Different: An Automatic Detection and Inference SystemDatabases Theory and Applications10.1007/978-3-031-47843-7_15(213-226)Online publication date: 7-Nov-2023
https://doi.org/10.1007/978-3-031-47843-7_15
Leis VRadke BGubichev AMirchev ABoncz PKemper ANeumann T(2018)Query optimization through the looking glass, and what we found running the Join Order BenchmarkThe VLDB Journal — The International Journal on Very Large Data Bases10.1007/s00778-017-0480-727:5(643-668)Online publication date: 1-Oct-2018
https://dl.acm.org/doi/10.1007/s00778-017-0480-7
Li ZPapaemmanouil OCherniack MMukhopadhyay SZhai CBertino ECrestani FMostafa JTang JSi LZhou XChang YLi YSondhi P(2016)OptMarkProceedings of the 25th ACM International on Conference on Information and Knowledge Management10.1145/2983323.2983658(2155-2160)Online publication date: 24-Oct-2016
https://dl.acm.org/doi/10.1145/2983323.2983658
Leis VGubichev AMirchev ABoncz PKemper ANeumann T(2015)How good are query optimizers, really?Proceedings of the VLDB Endowment10.14778/2850583.28505949:3(204-215)Online publication date: 1-Nov-2015
https://dl.acm.org/doi/10.14778/2850583.2850594
Gu ZSoliman MWaas F(2012)Testing the accuracy of query optimizersProceedings of the Fifth International Workshop on Testing Database Systems10.1145/2304510.2304525(1-6)Online publication date: 21-May-2012
https://dl.acm.org/doi/10.1145/2304510.2304525
Trivedi RNilavalagan IHaritsa J(2012)CODDProceedings of the Fifth International Workshop on Testing Database Systems10.1145/2304510.2304516(1-6)Online publication date: 21-May-2012
https://dl.acm.org/doi/10.1145/2304510.2304516

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Cited By

Index Terms

Recommendations

Continuing plan quality optimisation

On Plan Adaption through Planning Graph Analysis

Plan stability: replanning versus plan repair