System, method, and computer-readable medium for enhancing query execution by an optimizer in a database system

摘要

A system, method, and computer-readable medium that provide automatic and dynamic control of optimizer settings in a large complex system environment are provided. The optimizer may pursue more aggressive query execution plans and switch to a more conservative approach in cases of actual observed inaccuracies.

主权项

1. A method of improving the robustness of an optimizer executable by a processor and the predictability of the performance of execution plans in a database system, comprising:
receiving, with the optimizer, a first query to be executed, wherein the first query is one of a plurality of requests in a database workload; obtaining, with the optimizer, a confidence threshold parameter associated with the first query; generating, with the optimizer, a query execution plan for the first query utilizing an optimizer confidence value compliant with the confidence threshold parameter; obtaining, with a runtime query execution system executable by the processor, an error exception threshold associated with the first query; determining, with the runtime query execution system, an exception count, wherein the exception count is indicative of a number of times that a query execution cost of the query execution plan exceeds an optimizer estimated cost of the query execution plan by an amount that exceeds the error exception threshold; determining, with the runtime query execution system, the exception count equals a maximum exception count parameter within a predetermined period of time; generating, with the runtime query execution system, an adjusted confidence threshold parameter responsive to determining the exception count equals the maximum exception count parameter within the predetermined period of time; and applying, with the optimizer, the adjusted confidence threshold parameter to at least one other request in the database workload.