Method and apparatus for asking the database optimizer to re-optimize a poor performing plan
Publication Date: 2010-Mar-09
The IP.com Prior Art Database
Disclosed is a method that gives a database administrator or developer a way to re-start a query without disturbing the application that issued the query.
Disclosed is a method for a database administrator to re-start a long running database query after making changes to improve performance. When a database administrator or database application developer is trying to tune a long-running database query, they must wait until the query is completed to try changes intended to improve performance. An application developer must cancel the application running the query and re-start from the beginning after correcting the problem that caused the poor performance. A database administrator that notices a poor performing query while monitoring general database performance may know little about a particular database application that issued the poor performing query and may not be able to restart a poor performing query after correcting an environmental problem. The disclosed solution will give the administrator and developer a way to restart a query without disturbing the application that issued the query.
As a query is running, the disclosed solution will give the database administrator or application developer the ability to press a button to re-optimize a query from graphical display tools used to display the query plan. This disclosure assumes that an infrastructure is in place to do Adaptive Query Processing (AQP). AQP processing runs in a thread that runs parallel to the query and monitors the progress of the query. The AQP thread has a mechanism available to replace an executing database query with a new plan and restart without user intervention. The button in this disclosure ties into this thread as follows: