Publication Date: 2013-Oct-24
The IP.com Prior Art Database
Disclosed is a method of scheduling re-optimization in the query optimizer of a database management system. The method uses the ratio of optimization to execution time as the determinant or constraint for when to apply re-optimization.
Page 01 of 1
The query optimizer of a database management system is designed to choose the most efficient way to perform a query. The optimizer is a computer program that is given the query, statistics, and query bind values as input. The query execution plan is the output. The best plan for the query might change for the next execution of the query if bind values and/or statistics changed significantly. Therefore, it is important to re-optimize the query if significant plan changes are likely.
One of the known solutions involves specific user directives that force re-optimization on certain conditions. For example, the user might require that the query is re-optimized each time bind values have changed.
The disclosed method uses the ratio of optimization to execution time as the determinant or constraint for when to apply re-optimization. First, a method determines that re-optimization may change the execution plan due to changes in bind values or statistics. Then, re-optimization is applied if the ratio of total time spent optimizing this particular query to the total time running the query, each of which may have occurred several times, does not exceed a certain limit. For example, one may demand that total optimization time should not exceed 5% of total execution time.
An embodiment of the current invention must measure the total optimization time and
the total execution time for each query. The query is not re-optimized unless the ratio