Query Optimizer Assessing Tool

About

Query optimizers are one of the most complex components of a database management system. Optimizers are responsible for discovering the most efficient way to process a user’s query. Existing performance assessment approaches for database engines produce a performance assessment of the query run-time system rather than its query optimizer. Currently there are no tools for evaluating the quality of query optimizers. To address this challenge, researchers at Brandies University have introduced OptMark, a system for profiling and evaluating the quality of a query optimizer. OptMark is a toolkit that quantifies the quality of a query optimizer, independently of any other component of the database management system. Our toolkit is able to accomplish this by two ways: first, by decoupling the quality of an optimizer from the quality of its underlying execution engine; and second, by evaluating independently both the effectiveness of an optimizer and its efficacy. OptMark evaluates the relative performance factor in order to provide an insight into the quality of the optimizer’s purposed plan compared with other alternative plans. The evaluation process of this metric requires us to generate a set of alternative plants and collect their execution times. OptMark relies on query hints to affect the optimizer’s choice to select a specific plan, however the plan that gets executes may not be the one specified in the hint-based query. It is hard to detect before execution whether a plan is executed as the one specified by the query hints. The execution plan can be manually compared to the hint-based query after execution to check if they are the same, however that would never be efficient with a big workload. Therefore, it would be useful if there is a general approach to check if the execution plan is the same as the one specified by the query hints independently of DBMSs. OptMark’s approach for evaluating the effectiveness of an optimizer involves reporting the three effectiveness metrics absolute performance factor, relative performance factor, and optimal frequency. OptMark is able to report the relative and absolute performance factor of a given profiling query by generating and executing a sample of plans compared with the optimizer chosen plans.