Navin Kabra
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.
- 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). Google Scholar
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. Google ScholarDigital 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. Google ScholarDigital 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). Google ScholarDigital 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). Google ScholarDigital 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. Google ScholarDigital Library
- 7.(}RAEFE, (_~., AND COLE, R. "Optimization of Dynamic Query Evaluation Plans". In Proceedings of the 199~ A CM-SIGMOD Conference (1994). Google ScholarDigital Library
- 8.GRAEFE, G., AND WARD, K. "Dynamic Query Evaluation Plans. In SIGMOD Proceedings (June 1989), ACM, pp. 377-388. Google ScholarDigital 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). Google ScholarDigital Library
- 10.IOANNIDIS, Y., NG, R. T., SHIM, K., AND SELLIS, T. "Parametric Query Optimization". In Proc. of the 18th VLDB Conf. (1992). Google ScholarDigital 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). Google ScholarDigital Library
- 12.KABRA, N. "Query Optimization for Relational and Object-Relational Database Systems". PhD thesis, University of Wisconsin, Madison, 1998. Google ScholarDigital 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). Google ScholarDigital 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). Google ScholarDigital Library
- 15.NAG, B., AND DEWITT, D.J. "Memory Allocation Strategies for Complex Decision Support Queries". Submitted for publication.Google Scholar
- 16.ONO, K., AND LOHMANN, G. "Extensible Enumeration of Feasible Joins for Relational Query Optimization". In Proc. of the 16th VLDB Conf. (Aug. 1990). Google ScholarDigital 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). Google ScholarDigital Library
- 18.POOSALA, V. "Zipf's Law". Tech. rep., University of Wisconsin, Madison, 1995.Google Scholar
- 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.Google Scholar
- 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). Google ScholarDigital Library
- 21.RAAB, F. "TPC Benchmark D - Standard Specification, Revision 1.0". Transaction Processing Performance Council, May 1995.Google Scholar
- 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). Google ScholarDigital Library
- 23.STONEBRAKER, M., ANTON, J., AND HIROHAMA, M. "Extendability in POSTGRES". In Data Engineering Bulletin (1987), vol. 10(2), pp. 16-23.Google Scholar
- 24.VI'r'rER, J. S. "Random Sampling with a Reservoir". In ACM Transactions on Mathematical Software (1985), vol. 11, pp. 37-57. Google ScholarDigital Library
- 25.WONG, E., AND YOUSSEFI, K. "Decomposition- A Strategy for Query Processing". In ACM Transactions on Database Systems (Sept. 1976). Google ScholarDigital 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). Google ScholarDigital Library
- 27.ZIPF, G.K. "Human Behavior and the Principle of Least Resistance". Addison-Wesley, Reading, MA, 1949.Google Scholar
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
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 ApplicationsQuery 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 ...
Comments