Using similarity distance for performance prediction of the query optimization process

Abstract

Listen

Translate article

Query optimization is a sophisticated process whose resource consumption and quality of the best execution plan depends on the query complexity, available resources of the RDBMS server, and the current instance of the database. For a self-managing RDBMS such as SAP SQL Anywhere, the query optimizer must adapt each optimization process to the current resources as these servers are run in highly diverse environments with no DBA for tuning, running mixed workloads with little correlation between query complexity, its execution time, or database size. The main goal of the work presented in this paper is to be able to dynamically choose a join enumeration algorithm among the algorithms available to the query optimizer based on the known characteristics of the algorithms, the query complexity and its expensiveness, and the available resources for the optimization process. First, the paper discusses the experimental results of the optimization time breakdown and the memory consumption for a set of join enumeration algorithms ranging from highly heuristics algorithms to dynamic programming algorithms with exhaustive bushy trees enumeration. Next, we present a novel technique to predict the optimization time and memory consumption for a current query by using similarity distances between its query graph and a set of etalon queries with known performance. The new technique can be used by any query optimizer for choosing among join enumeration algorithms, or optimization levels, based on such characteristics as query complexity and available resources.