SQL Tuner: A Utility to Assist in the Query Tuning Process

摘要

Despite the sophistication of query optimizers in modern database management systems (DBMS), inefficient query execution plans are not uncommon in practice due to various reasons such as lack of data distribution statistics. The nature of the DBMS optimizer necessitates application-level query tuning to determine where and why the optimizer made an incorrect cost estimation as well as how to fix it. In this paper, we present SQL Tuner, a utility that assists the query tuning process. It combines automatic statistics gathering with user knowledge and intuition, and guides the users through a systematic procedure to find the optimal execution plan. We tested our utility against a large scale production data warehouse, and found that the utility's suggestions were robust and sometimes outperformed the execution plans suggested by the DBMS optimizer dramatically.