Automatic physical database tuning: a relaxation-based approach

ABSTRACT

In recent years there has been considerable research on automated selection of physical design in database systems. In current solutions, candidate access paths are heuristically chosen based on the structure of each input query, and a subsequent bottom-up search is performed to identify the best overall configuration. To handle large workloads and multiple kinds of physical structures, recent techniques have become increasingly complex: they exhibit many special cases, shortcuts, and heuristics that make it very difficult to analyze and extract properties. In this paper we critically examine the architecture of current solutions. We then design a new framework for the physical design problem that significantly reduces the assumptions and heuristics used in previous approaches. While simplicity and uniformity are important contributions in themselves, we report extensive experimental results showing that our approach could result in comparable (and, in many cases, considerably better) recommendations than state-of-the-art commercial alternatives.

REFERENCES

• 1: S. Agrawal, S. Chaudhuri, L. Kollar, A. Marathe, V. Narasayya, and M. Syamala. Database Tuning Advisor for Microsoft SQL Server 2005. In Proceedings of the 30th International Conference on Very Large Databases, 2004.
• 2: Sanjay Agrawal , Surajit Chaudhuri , Vivek R. Narasayya, Automated Selection of Materialized Views and Indexes in SQL Databases, Proceedings of the 26th International Conference on Very Large Data Bases, p.496-505, September 10-14, 2000
• 3: Surajit Chaudhuri , Rajeev Motwani , Vivek Narasayya, On random sampling over joins, Proceedings of the 1999 ACM SIGMOD international conference on Management of data, p.263-274, May 31-June 03, 1999, Philadelphia, Pennsylvania, United States
• 4: Surajit Chaudhuri , Vivek R. Narasayya, An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server, Proceedings of the 23rd International Conference on Very Large Data Bases, p.146-155, August 25-29, 1997
• 5: Surajit Chaudhuri , Vivek Narasayya, AutoAdmin “what-if” index analysis utility, Proceedings of the 1998 ACM SIGMOD international conference on Management of data, p.367-378, June 01-04, 1998, Seattle, Washington, United States
• 6: Index Merging, Proceedings of the 15th International Conference on Data Engineering, p.296, March 23-26, 1999
• 7: B. Dageville, D. Das, K. Dias, K. Yagoub, M. Zait, and M. Ziauddin. Automatic SQL Tuning in Oracle 10g. In Proceedings of the 30th International Conference on Very Large Databases, 2004.
• 8: G. Graefe. The Cascades framework for query optimization. Data Engineering Bulletin, 18(3), 1995.
• 9: P. Griffiths Selinger , M. M. Astrahan , D. D. Chamberlin , R. A. Lorie , T. G. Price, Access path selection in a relational database management system, Proceedings of the 1979 ACM SIGMOD international conference on Management of data, May 30-June 01, 1979, Boston, Massachusetts [doi>10.1145/582095.582099]
• 10: Alan Skelley, DB2 Advisor: An Optimizer Smart Enough to Recommend its own Indexes, Proceedings of the 16th International Conference on Data Engineering, p.101, February 28-March 03, 2000
• 11: D. Zilio, J. Rao, S. Lightstone, G. Lohman, A. Storm, C. Garcia-Arellano, and S. Fadden. DB2 design advisor: Integrated automatic physical database design. In Proceedings of the 30th International Conference on Very Large Databases, 2004.
• 12: Daniel C. Zilio , Calisto Zuzarte , Guy M. Lohman , Hamid Pirahesh , Jarek Gryz , Eric Alton , Dongming Liang , Gary Valentin, Recommending Materialized Views and Indexes with IBM DB2 Design Advisor, Proceedings of the First International Conference on Autonomic Computing (ICAC'04), p.180-188, May 17-18, 2004