System and methodology for automatic tuning of database query optimizer

1. In a database system having an optimizer for selecting a query plan for executing a database query, a method for automatically tuning query performance to prevent query performance regression that may occur during upgrade of the database system from a prior version to a new version, the method comprising:

in response to receiving a given database query for execution, specifying a query plan generated by the prior version's optimizer as a baseline best plan for executing the given database query;

generating at least one new query plan using the new version's optimizer;

learning performance for each new query plan generated by recording corresponding query execution metrics;

if a given new query plan is observed to have better performance than the best plan previously specified, specifying that given new query plan to be the best plan for executing the given database query;

if a given new query plan is observed to have worse performance than the best plan previously specified, specifying that given new query plan to be a bad plan to be avoided in the future; and

automatically tuning future execution of the given database query by using the query plan that the system learned was the best plan.

2. The method of claim 1 , wherein the method automatically tunes execution of the given database query without user intervention.

3. The method of claim 1 , wherein the query plan generated by the prior version's optimizer is used for future execution of the given database query in response to determining that no new query plan is observed to have better performance.

4. The method of claim 1 , wherein said generating step includes:

avoiding using any new query plan generated that has been previously identified to be a bad plan.

5. The method of claim 1 , wherein said query execution metrics include time required for the database system to complete execution of a given query plan.

6. The method of claim 1 , wherein query execution metrics for each new query plan generated is stored in an associated learning object.

7. The method of claim 6 , wherein the learning object indicates whether its associated query plan has been tuned.

8. The method of claim 6 , wherein the learning object is associated with a particular database query based on text that comprises the query.

9. The method of claim 1 , wherein the learning step includes:

learning performance for each new query plan generated by recording corresponding query execution metrics a certain number of times.

10. The method of claim 9 , wherein the certain number of times is user configurable.

11. A database system having an automatically tuning optimizer for selecting a query plan for executing a database query such that query performance regression is avoided during upgrade of the database system from a prior version to a new version, the system comprising:

a computer having a processor and memory;

a query plan generated by the prior version's optimizer, which serves as a baseline best plan for executing a given database query;

at least one new query plan generated by the new version's optimizer;

a learning object associated with each query plan for tracking corresponding query execution metrics, whereby the system may learn which new query plan, if any, is the best plan for executing the given database query and which new query plans are bad plans to be avoided in the future; and

a module for automatically tuning future execution of the given database query, based on which query plan the system learned was the best plan.

12. The system of claim 11 , wherein the system automatically tunes execution of the given database query without user intervention.

13. The system of claim 11 , wherein the query plan generated by the prior version's optimizer is used for future execution of the given database query in response to determining that no new query plan is observed to have better performance.

14. The system of claim 11 , wherein the system automatically avoids using any new query plan generated that has been previously identified to be a bad plan.

15. The system of claim 11 , wherein said query execution metrics include time required for the system to complete execution of a given query plan.

16. The system of claim 11 , wherein each learning object indicates whether its associated query plan has been tuned.

17. The system of claim 11 , wherein each learning object is associated with a particular database query based on text that comprises the query.

18. The system of claim 11 , wherein each learning object records corresponding query execution metrics observed over a certain number of executions.

19. The system of claim 18 , wherein the certain number of executions is user configurable.

20. The system of claim 18 , wherein the certain number of executions is equal to three.

21. In a database system having an optimizer for selecting a query plan for executing a database query, a method for tuning query performance to prevent query performance regression, the method comprising:

establishing a baseline best plan for executing the database query;

generating a new query plan for executing the database query;

if the new query plan has not been previously identified as a bad plan, monitoring performance of the new query plan;

if the new query plan has better performance than the best plan previously established, establishing the new query plan to be the best plan for executing the given database query;

if the new query plan does not have better performance than the best plan previously established, establishing the new query plan to be a bad plan; and

performing subsequent executions of the database query using the query plan that the system established was the best plan.

22. The method of claim 21 , wherein the method automatically tunes query performance without user intervention.

23. The method of claim 21 , wherein the baseline best plan is used for future execution of the given database query in, response to determining that no new query plan is observed to have better performance.

24. The method of claim 21 , wherein said monitoring performance step includes monitoring time required for the system to complete execution of the new query plan being monitored.

25. The method of claim 21 , wherein said monitoring performance step includes storing execution metrics in a learning object associated with the new query plan, said learning object including information indicating whether the new query plan has been tuned yet.