Interactive plan hints for query optimization

ABSTRACT

Commercial database systems expose query hints to fix poor plans produced by the query optimizer. However, current query hints are not flexible enough to deal with a variety of non-trivial scenarios, and can be at times cumbersome for DBAs to interact with. In this demonstration we present a framework that enables visual specification of hints to influence the optimizer to pick better plans. Our framework goes considerably beyond existing hinting mechanisms and significantly improves the usability of such functionality.

REFERENCES

• 1: ]]Giving optimization hints to DB2, IBM, 2003. Available at http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/-index.jsp?topic=/com.ibm.db2.doc.admin/p9li375.htm.
• 2: Nicolas Bruno , Surajit Chaudhuri , Ravi Ramamurthy, Power Hints for Query Optimization, Proceedings of the 2009 IEEE International Conference on Data Engineering, p.469-480, March 29-April 02, 2009 [doi>10.1109/ICDE.2009.68]
• 3: ]]I. Chan. Oracle(R) Database Performance Tuning Guide 10g Release 2 (10.2), Oracle, 2008. Available at http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm.
• 4: S. Christodoulakis, Implications of certain assumptions in database performance evauation, ACM Transactions on Database Systems (TODS), v.9 n.2, p.163-186, June 1984 [doi>10.1145/329.318578]
• 5: ]]B. A. Patel. Forcing query plans, Microsoft Corp., 2005. Available at http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx.
• 6: ]]SQLServer Books Online. Query hint (transact-sql), Microsoft Corp., 2007. Available at http://technet.microsoft.com/en-us/library/ms181714.aspx.

INDEX TERMS

Primary Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.7 Database Administration

General Terms:
Algorithms, Design

Keywords:
query hinting, query optimization