A Profiling and Quality Assessment Tool for Query Optimizers
- Technology Benefits
- · Firsttoolkit that separates the assessment of the system component from othercomponents, making it possible to compare different optimizers.· Allowsfor the comparison of different versions of the optimizer of the same datamanagement system and for the comparison of optimizers of different datamanagement systems.· Itis minimally invasive to the database management system being used.
- Detailed Technology Description
- A Profiling and Quality Assessment Tool for Query Optimizers
- Countries
- Not Available
- Application No.
- None
- *Abstract
-
Queryoptimizers are one of the most complex components of a database managementsystem. Optimizers are responsible for discovering the most efficient way toprocess a user’s query. Existing performance assessment approaches for databaseengines produce a performance assessment of the query run-time system ratherthan its query optimizer. Currently there are no tools for evaluating thequality of query optimizers. To address this challenge, researchers at BrandiesUniversity have introduced OptMark, a system for profiling and evaluating thequality of a query optimizer.
OptMarkis a toolkit that quantifies the quality of a query optimizer, independently ofany other component of the database management system. Our toolkit is able toaccomplish this by two ways: first, by decoupling the quality of an optimizerfrom the quality of its underlying execution engine; and second, by evaluatingindependently both the effectiveness of an optimizer and its efficacy.
OptMarkevaluates the relative performance factor in order to provide an insight intothe quality of the optimizer’s purposed plan compared with other alternativeplans. The evaluation process of this metric requires us to generate a set ofalternative plants and collect their execution times. OptMark relies on queryhints to affect the optimizer’s choice to select a specific plan, however theplan that gets executes may not be the one specified in the hint-based query. It is hard to detect before execution whethera plan is executed as the one specified by the query hints. The execution plancan be manually compared to the hint-based query after execution to check ifthey 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 theexecution plan is the same as the one specified by the query hintsindependently of DBMSs.
OptMark’s approach forevaluating the effectiveness of an optimizer involves reporting the threeeffectiveness metrics absolute performance factor, relative performance factor,and optimality frequency. OptMark isable to report the relative and absolute performance factor of a given profilingquery by generating and executing a sample of plans compared with the optimizerchosen plans.
- *IP Issue Date
- None
- *IP Type
- Other Patent
- Country/Region
- USA
