Nicolas Bruno
ABSTRACT
Automated physical design tuning for database systems has recently become an active area of research and development. Existing tuning tools explore the space of feasible solutions by repeatedly optimizing queries in the input workload for several candidate configurations. This general approach, while scalable, often results in tuning sessions waiting for results from the query optimizer over 90% of the time. In this paper we introduce a novel approach, called Configuration-Parametric Query Optimization, that drastically improves the performance of current tuning tools. By issuing a single optimization call per query, we are able to generate a compact representation of the optimization space that can then produce very efficiently execution plans for the input query under arbitrary configurations. Our experiments show that our technique speeds-up query optimization by 30x to over 450x with virtually no loss in quality, and effectively eliminates the optimization bottleneck in existing tuning tools. Our techniques open the door for new, more sophisticated optimization strategies by eliminating the main bottleneck of current tuning tools.
- S. Agrawal et al. Database Tuning Advisor for Microsoft SQL Server 2005. In Proceedings of the International Conference on Very Large Databases (VLDB), 2004.Google Scholar
Cross Ref
- K. Billings. A TPC-D Model for Database Query Optimization in Cascades. Ms. thesis, Portland State University, 1996.Google Scholar
- N. Bruno and S. Chaudhuri. Automatic physical database tuning: A relaxation-based approach. In Proceedings of the ACM International Conference on Management of Data (SIGMOD), 2005. Google ScholarDigital Library
- N. Bruno and S. Chaudhuri. To tune or not to tune? A Lightweight Physical Design Alerter. In Proceedings of the International Conference on Very Large Databases (VLDB), 2006. Google ScholarDigital Library
- P. Celis. The Query Optimizer in Tandem's new ServerWare SQL Product. In Proceedings of the International Conference on Very Large Databases (VLDB), 1996. Google ScholarDigital Library
- S. Chaudhuri and V. Narasayya. An efficient cost-driven index selection tool for Microsoft SQL Server. In Proceedings of the International Conference on Very Large Databases (VLDB), 1997. Google ScholarDigital Library
- S. Chaudhuri and V. Narasayya. Autoadmin 'What-if' index analysis utility. In Proceedings of the ACM International Conference on Management of Data (SIGMOD), 1998. Google ScholarDigital Library
- B. Dageville et al. Automatic SQL Tuning in Oracle 10g. In Proceedings of the International Conference on Very Large Databases (VLDB), 2004. Google ScholarDigital Library
- G. Graefe. The Cascades framework for query optimization. Data Engineering Bulletin, 18(3), 1995.Google Scholar
- G. Graefe. The Microsoft Relational Engine. In Proceedings of the International Conference on Data Engineering (ICDE), 1996. Google ScholarDigital Library
- Y. Ioannidis, R. Ng, K. Shim, and T. Sellis. Parametric query optimization. In Proceedings of the International Conference on Very Large Databases (VLDB), 1992. Google ScholarDigital Library
- S. Papadomanolakis, D. Dash, and A. Ailamaki. Efficient use of the query optimizer for automated physical design. In Proceedings of the International Conference on Very Large Databases (VLDB), 2007. Google ScholarDigital Library
- L. Shapiro et al. Exploiting upper and lower bounds in top-down query optimization. In Proceedings of IDEAS, 2001. Google ScholarDigital Library
- G. Valentin, M. Zuliani, D. Zilio, G. Lohman, and A. Skelley. DB2 advisor: An optimizer smart enough to recommend its own indexes. In Proceedings of the International Conference on Data Engineering (ICDE), 2000. Google ScholarDigital Library
- D. Zilio et al. DB2 design advisor: Integrated automatic physical database design. In Proceedings of the International Conference on Very Large Databases (VLDB), 2004. Google ScholarDigital Library
Index Terms
- Configuration-parametric query optimization for physical design tuning
Recommendations
Multi-Objective Parametric Query Optimization
We propose a generalization of the classical database query optimization problem: multi-objective parametric query optimization (MPQ). MPQ compares alternative processing plans according to multiple execution cost metrics. It also models missing pieces ...
Multi-objective parametric query optimization
We propose a generalization of the classical database query optimization problem: multi-objective parametric query (MPQ) optimization. MPQ compares alternative processing plans according to multiple execution cost metrics. It also models missing pieces ...
Multi-objective parametric query optimization
Classical query optimization compares query plans according to one cost metric and associates each plan with a constant cost value. In this paper, we introduce the multi-objective parametric query optimization (MPQO) problem where query plans are ...
Comments