Progressive Optimization in Action

Progressive Optimization (POP) is a technique to make query plans robust, and minimize need for DBA intervention, by repeatedly re-optimizing a query during runtime if the cardinalities estimated during optimization prove to be significantly incorrect. POP works by carefully calculating validity ranges for each plan operator under which the overall plan can be optimal. POP then instruments the query plan with checkpoints that validate at runtime that cardinalities do lie within validity ranges, and reoptimizes the query otherwise. In this demonstration we showcase POP implemented for a research prototype version of IBM’s DB2 DBMS, using a mix of real-world and synthetic benchmark databases and workloads. For selected queries of the workload we display the query plans with validity ranges as well as the placement of the various kinds of CHECK operators using the DB2 graphical plan explain tool. We also execute the queries, showing how and where re-optimization is triggered through th...

• Type: Conference
• Year: 2004
• Where: VLDB
• Authors: Vijayshankar Raman, Volker Markl, David E. Simmen, Guy M. Lohman, Hamid Pirahesh
• Keywords: Database, Plan Operator, Query Plans, Validity Ranges, VLDB 2004