article

Free access

Efficient mid-query re-optimization of sub-optimal query execution plans

Authors:

David J. DeWittAuthors Info & Claims

ACM SIGMOD Record, Volume 27, Issue 2

Pages 106 - 117

https://doi.org/10.1145/276305.276315

Published: 01 June 1998 Publication History

Abstract

For a number of reasons, even the best query optimizers can very often produce sub-optimal query execution plans, leading to a significant degradation of performance. This is especially true in databases used for complex decision support queries and/or object-relational databases. In this paper, we describe an algorithm that detects sub-optimality of a query execution plan during query execution and attempts to correct the problem. The basic idea is to collect statistics at key points during the execution of a complex query. These statistics are then used to optimize the execution of the query, either by improving the resource allocation for that query, or by changing the execution plan for the remainder of the query. To ensure that this does not significantly slow down the normal execution of a query, the Query Optimizer carefully chooses what statistics to collect, when to collect them, and the circumstances under which to re-optimize the query. We describe an implementation of this algorithm in the Paradise Database System, and we report on performance studies, which indicate that this can result in significant improvements in the performance of complex queries.

References

[1]

AMSALEG, L., FRANKLIN, M. J., TOMASIC, A., AND URHAN, T. "Scrambling Query Plans to Cope with Unexpected Delays". In The 4th International Conference on Parallel and Distributed Information Systems (PDIS) (Miami Beach, Florida, Dec. 1996).

Digital Library

[2]

ANTOSHENKOV, G. "Dynamic Query Optimization in Rdb/VMS". In in Proceedings of the IEEE Conference on Data Engineering (1993), pp. 538-547.

Digital Library

[3]

ANTOSHENKOV, G. "Dynamic Optimization of Index Scan Restricted by Booleans". In In Proceedings of the IEEE Conference on Data Engineering (1996), pp. 430- 440.

Digital Library

[4]

(',HEN, M. S., ET AL. "Using Segmented Right-Deep 2~ees for Execution of Pipelined Hash Joins". In Proc. of the 18th VLDB Conf. (1992).

Digital Library

[5]

{)ERR, M. A., MORISHITA, S., AND PHIPPS, G. "Adaptive Query Optimization in a Deductive Database System". In In Proceedings of the Proceedings of the Second International Conference on Information and Knowledge Management (Washington D. C., USA, 1993).

Digital Library

[6]

FLAJOLET, P., AND MARTIN, G. N. "Probabilistic Counting Algorithms for Database Applications". In Journal of Computer and System Sciences (1985), vol. 31 (2), pp. 182-209.

Digital Library

[7]

(}RAEFE, (_~., AND COLE, R. "Optimization of Dynamic Query Evaluation Plans". In Proceedings of the 199~ A CM-SIGMOD Conference (1994).

Digital Library

[8]

GRAEFE, G., AND WARD, K. "Dynamic Query Evaluation Plans. In SIGMOD Proceedings (June 1989), ACM, pp. 377-388.

Digital Library

[9]

IOANNIDIS, Y., AND CHRISTODOULAKIS, S. "On the Propogation of Errors in the Size of Join Results". In Proceedings of the 1991 A CM-SIGMOD Conference (Denver, Colorado, May 1991).

Digital Library

[10]

IOANNIDIS, Y., NG, R. T., SHIM, K., AND SELLIS, T. "Parametric Query Optimization". In Proc. of the 18th VLDB Conf. (1992).

Digital Library

[11]

IOANNIDIS, Y., AND POOSALA, V. "Balancing Histogram Optimality and Practicality for Query Result Size Estimation". In P~vceedings of the 1995 ACM- SIGMOD Conference (San Jose, California, May 1995).

Digital Library

[12]

KABRA, N. "Query Optimization for Relational and Object-Relational Database Systems". PhD thesis, University of Wisconsin, Madison, 1998.

Digital Library

[13]

KABRA, N., AND DEWITT, D. J. "Opt++: An Object Oriented Implementation for Extensible Database Query Optimization". In to appear in The VLDB Journal (1998).

Digital Library

[14]

MEH'rA, M., AND DEWITT, D. 3. "Dynamic Memory Allocation for Multiple Query Workloads". In Proc. of the 19th VLDB Conf. (Dublin, Ireland, 1993).

Digital Library

[15]

NAG, B., AND DEWITT, D.J. "Memory Allocation Strategies for Complex Decision Support Queries". Submitted for publication.

[16]

ONO, K., AND LOHMANN, G. "Extensible Enumeration of Feasible Joins for Relational Query Optimization". In Proc. of the 16th VLDB Conf. (Aug. 1990).

Digital Library

[17]

PATEL, 3. M., ET Aa. "Building a Scalable Geo-Spatial DMBS: Technology, Implementation, and Evaluation". In Proceedings of the 1997 A CM-SIGMOD Conference (Tuscon, Arizona, May 1997).

Digital Library

[18]

POOSALA, V. "Zipf's Law". Tech. rep., University of Wisconsin, Madison, 1995.

[19]

POOSALA, V., AND IOANNIDIS, Y. "Histogram-Based Solutions to Diverse Database Estimation Problems". In Data Engineering Bulletin (1995), vol. 18(3), pp. 10- 18.

[20]

POOSALA, V., IOANNIDIS, Y., HAAS, P. J., AND SHEKITA, E. "improved Histograms for Selectivity Estimation of Range Predicates". In Proceedings of the 1996 A CM-SIGMOD Conference (Montreal, Canada, June 1996).

Digital Library

[21]

RAAB, F. "TPC Benchmark D - Standard Specification, Revision 1.0". Transaction Processing Performance Council, May 1995.

[22]

SELINGER, P., ASTRAHAN, M., CHAMBERLIN, D., LO- RIE, R., AND PPdCE, T. "Access Path Selection in a Relational Database Management System". In Proceedings of the A CM SIGMOD Conference on Management of Data (May 1979).

Digital Library

[23]

STONEBRAKER, M., ANTON, J., AND HIROHAMA, M. "Extendability in POSTGRES". In Data Engineering Bulletin (1987), vol. 10(2), pp. 16-23.

[24]

VI'r'rER, J. S. "Random Sampling with a Reservoir". In ACM Transactions on Mathematical Software (1985), vol. 11, pp. 37-57.

Digital Library

[25]

WONG, E., AND YOUSSEFI, K. "Decomposition- A Strategy for Query Processing". In ACM Transactions on Database Systems (Sept. 1976).

Digital Library

[26]

Yu, P. S., AND CORNELL, D. W. "Buffer Management Based on Return on Consumption in a Multi-Query Environment". In VLDB Journal (Jan. 1993), vol. 2(1).

Digital Library

[27]

ZIPF, G.K. "Human Behavior and the Principle of Least Resistance". Addison-Wesley, Reading, MA, 1949.

Cited By

Helmer SWood PStuefer M(2024)Optimism and Pessimism in Database Query Optimisation2024 IEEE 18th International Conference on Semantic Computing (ICSC)10.1109/ICSC59802.2024.00049(269-276)Online publication date: 5-Feb-2024
https://doi.org/10.1109/ICSC59802.2024.00049
Wang FYan XYiu MLI SMao ZTang B(2023)Speeding Up End-to-end Query Execution via Learning-based Progressive Cardinality EstimationProceedings of the ACM on Management of Data10.1145/35887081:1(1-25)Online publication date: 30-May-2023
https://doi.org/10.1145/3588708
Zhang WKim JRoss KSedlar EStadler L(2021)Adaptive code generation for data-intensive analyticsProceedings of the VLDB Endowment10.14778/3447689.344769714:6(929-942)Online publication date: 12-Apr-2021
https://dl.acm.org/doi/10.14778/3447689.3447697
Show More Cited By

Index Terms

Efficient mid-query re-optimization of sub-optimal query execution plans

Recommendations

Efficient mid-query re-optimization of sub-optimal query execution plans
SIGMOD '98: Proceedings of the 1998 ACM SIGMOD international conference on Management of data

For a number of reasons, even the best query optimizers can very often produce sub-optimal query execution plans, leading to a significant degradation of performance. This is especially true in databases used for complex decision support queries and/or ...
Generating query plans for distributed query processing using genetic algorithm
ICICA'11: Proceedings of the Second international conference on Information Computing and Applications

Query Processing is a key determinant in the overall performance of distributed databases. It requires processing of data at their respective sites and transmission of the same between them. These together constitute a distributed query processing ...
Dynamic optimization of query execution plans

Comments

Information & Contributors

Information

Published In

cover image ACM SIGMOD Record

ACM SIGMOD Record Volume 27, Issue 2

June 1998

595 pages

ISSN:0163-5808

DOI:10.1145/276305

Chairmen:
Laura Haas
IBM Almaden Research Center, San Jose, CA
,
Pamela Drew
Boeing Co.
,
Editor:
Ashutosh Tiwary
Boeing Co.; and Univ. of Washington, Seattle

Issue’s Table of Contents

SIGMOD '98: Proceedings of the 1998 ACM SIGMOD international conference on Management of data
June 1998
599 pages
ISBN:0897919955
DOI:10.1145/276304
Chairmen:
Laura Haas
IBM AlmadenResearch Center, San Jose, CA
,
Pamela Drew
Boeing Co.
,
Editors:
Ashutosh Tiwary
Boeing Co.; and Univ. of Washington, Seattle
,
Michael Franklin
Univ. of Maryland, College Park

Copyright © 1998 ACM.

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: 01 June 1998

Published in SIGMOD Volume 27, Issue 2

Check for updates

Qualifiers

Article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

245
Total Citations
View Citations
1,726
Total Downloads

Downloads (Last 12 months)226
Downloads (Last 6 weeks)33

Reflects downloads up to 31 Dec 2024

Other Metrics

View Author Metrics

Citations

Cited By

Helmer SWood PStuefer M(2024)Optimism and Pessimism in Database Query Optimisation2024 IEEE 18th International Conference on Semantic Computing (ICSC)10.1109/ICSC59802.2024.00049(269-276)Online publication date: 5-Feb-2024
https://doi.org/10.1109/ICSC59802.2024.00049
Wang FYan XYiu MLI SMao ZTang B(2023)Speeding Up End-to-end Query Execution via Learning-based Progressive Cardinality EstimationProceedings of the ACM on Management of Data10.1145/35887081:1(1-25)Online publication date: 30-May-2023
https://doi.org/10.1145/3588708
Zhang WKim JRoss KSedlar EStadler L(2021)Adaptive code generation for data-intensive analyticsProceedings of the VLDB Endowment10.14778/3447689.344769714:6(929-942)Online publication date: 12-Apr-2021
https://dl.acm.org/doi/10.14778/3447689.3447697
Luo SJankov DYuan BJermaine CLi GLi ZIdreos SSrivastava D(2021)Automatic Optimization of Matrix Implementations for Distributed Machine Learning and Linear AlgebraProceedings of the 2021 International Conference on Management of Data10.1145/3448016.3457317(1222-1234)Online publication date: 9-Jun-2021
https://dl.acm.org/doi/10.1145/3448016.3457317
Snodgrass RCurrim SSuh Y(2021)Have query optimizers hit the wall?The VLDB Journal10.1007/s00778-021-00689-yOnline publication date: 20-Sep-2021
https://doi.org/10.1007/s00778-021-00689-y
Wang CGruenwald Ld’Orazio LLeal E(2021)Cloud Query Processing with Reinforcement Learning-Based Multi-objective Re-optimizationModel and Data Engineering10.1007/978-3-030-78428-7_12(141-155)Online publication date: 14-Jun-2021
https://doi.org/10.1007/978-3-030-78428-7_12
Goda KHayamizu YYamada HKitsuregawa M(2020)Out-of-order execution of database queriesProceedings of the VLDB Endowment10.14778/3415478.341557113:12(3489-3501)Online publication date: 14-Sep-2020
https://dl.acm.org/doi/10.14778/3415478.3415571
Haritsa J(2020)Robust query processingProceedings of the VLDB Endowment10.14778/3415478.341556113:12(3425-3428)Online publication date: 14-Sep-2020
https://dl.acm.org/doi/10.14778/3415478.3415561
Jankov DLuo SYuan BCai ZZou JJermaine CGao Z(2020)Declarative Recursive Computation on an RDBMSACM SIGMOD Record10.1145/3422648.342265949:1(43-50)Online publication date: 4-Sep-2020
https://dl.acm.org/doi/10.1145/3422648.3422659
Sikdar SJermaine CMaier DPottinger RDoan ATan WAlawini ANgo H(2020)MONSOON: Multi-Step Optimization and Execution of Queries with Partially Obscured PredicatesProceedings of the 2020 ACM SIGMOD International Conference on Management of Data10.1145/3318464.3389728(225-240)Online publication date: 11-Jun-2020
https://dl.acm.org/doi/10.1145/3318464.3389728
Show More Cited By

View Options

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Login options

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

Full Access

Get this Publication

Media

Figures

Other

Tables

View Issue’s Table of Contents