Seminar by Prof. Jayant Haritsa
Reducing Query Optimization Overheads Through Plan Recycling
Prof. Jayant Haritsa
Supercomputer Education and Research Centre (SERC)
and
Department of Computer Science and Automation (CSA)
Indian Institute of Science (IISc), Bangalore
Date: Thursday, March 27, 2003
Time: 3:45 PM
Venue: CS-101
Abstract
Query optimization is a computationally intensive process, especially for complex queries. In this talk, we present a tool, called PLASTIC, that can be used by query optimizers to amortize the optimization cost. Our scheme groups similar queries into clusters and uses the optimizer-generated plan for the cluster representative to execute all future queries assigned to the cluster. Query similarity is evaluated based on a comparison of query structures and the associated table schemas and statistics, and a classifier is employed for efficient cluster assignments. Experiments with a variety of queries on the DB2 and Oracle optimizers show that PLASTIC predicts the correct plan choice in most cases, thereby providing significantly improved query optimization times. Further, even when errors are made, the additional execution cost incurred due to the sub-optimal plan choices is marginal. PLASTIC also improves query execution efficiency by making it feasible for optimizers to always run at their highest optimization level. Further, the benefits of "plan hints," a common technique for influencing optimizer plan choices for specific queries, automatically percolate to the entire set of queries that are associated with this plan. Lastly, since the association of queries with clusters is based on database statistics, the plan choice for a given query is adaptive to the current state of the database. After the talk, a working prototype of the PLASTIC software will be demonstrated on the IBM DB2 system.
About the speaker
Jayant Haritsa is on the faculty of the Supercomputer Education & Research Centre and the Department of Computer Science & Automation at the Indian Institute of Science, Bangalore. He received the BTech degree in Electronics and Communications Engineering from the Indian Institute of Technology (Madras), and the MS and PhD degrees in Computer Science from the University of Wisconsin (Madison). His research interests are in database systems and real-time systems.