Extreme visualisation of query optimizer search space

ABSTRACT

This demonstration showcases a system for visualizing and analyzing search spaces generated by the SQL Anywhere optimizer during the optimization process of a SQL statement. SQL Anywhere dynamically optimizes each statement every time it is executed. The decisions made by the optimizer during the optimization process are both cost-based and heuristics adapted to the current state of the server and the database instance. Many performance issues can be understood and resolved by analyzing the search space generated when optimizing a certain request. In our experience, there are two main classes of performance issues related to the decisions made by a query optimizer:(1) a request is very slow due to a suboptimal access plan; and (2) a request has a different, less optimal access plan than a previous execution. We have enhanced SQL Anywhere to log, in a very compact format, its search space during the optimization process when tracing mode is on. These search space logs can be used for performance analysis in the absence of the database instances or of extra information about the SQL Anywhere server state at the time the logs were generated. This demonstration introduces the SearchSpaceAnalyzer System, a research prototype used to analyze the search spaces of the SQL Anywhere optimizer. The system visualizes and analyzes (1) a single search space and (2) the differences between two search spaces generated for the same query by two different optimization processes. The SearchSpaceAnalyze System can be used for the analysis of any query optimizer search spaces as long as the logged data is recorded using the syntax understood by the system.

REFERENCES

• 1: Raihan Al-Ekram , Archana Adma , Olga Baysal, diffX: an algorithm to detect changes in multi-version XML documents, Proceedings of the 2005 conference of the Centre for Advanced Studies on Collaborative research, p.1-11, October 17-20, 2005, Toranto, Ontario, Canada
• 2: Ivan T. Bowman , Peter Bumbulis , Dan Farrar , Anil K. Goel , Brendan Lucier , Anisoara Nica , G. N. Paulley , John Smirnios , Matthew Young-Lai, SQL Anywhere: A Holistic Approach to Database Self-management, Proceedings of the 2007 IEEE 23rd International Conference on Data Engineering Workshop, p.414-423, April 17-20, 2007 [doi>10.1109/ICDEW.2007.4401024]
• 3: A. Nica. System and methodology for generating bushy trees using a left-deep tree join enumeration algorithm. US Patent 7,184,998, Feb. 2007.

INDEX TERMS

Primary Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.4 Systems
          Subjects: Query processing

Additional Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.3 Languages
          Subjects: Query languages

General Terms:
Algorithms, Experimentation, Performance, Theory

Keywords:
enumeration algorithm, query optimization, search space, sql anywhere, tree difference algorithm, visualisation