SELECTIVE AUTOMATIC REFRESHING OF STORED EXECUTION PLANS

Abstract

A system and method for selectively recompiling stored execution plans within a database system. In one technique the execution plans include respective version numbers of database software operable within the database system during compilation of the respective plans. A request is received to execute one of the stored execution plans, and the stored execution plan is retrieved from computer memory. A version number is obtained of database software operable within the database system during retrieval of the stored execution plan. The obtained version number is compared with the version number associated with the retrieved execution plan. On detecting a mismatch between the obtained version number and the retrieved version number, the stored execution plan is recompiled. In another technique the stored execution plans include respective cost parameters representing the hardware capabilities of the system at the time of plan compilation. A set of cost parameters representing the cost of executing the stored execution plan within the database system is compared with the stored cost parameters associated with the retrieved execution plan. On detecting a significant difference between the defined cost parameters and the retrieved cost parameters, the stored execution plan is recompiled.

Description

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a block diagram of an exemplary large computer system in which the techniques described below are implemented.

FIG. 2 is a block diagram of the parsing engine of the computer system of FIG. 1.

FIG. 3 is a flow chart of the parser of FIG. 2.

FIG. 4 is an example stored procedure and stored execution plan.

Claims

1. A method of selectively recompiling stored execution plans within a database system, the execution plans including respective version numbers of database software operable within the database system during compilation of the respective plans, the method comprising: receiving a request to execute one of the stored execution plans;retrieving the stored execution plan from computer memory;obtaining a version number of database software operable within the database system during retrieval of the stored execution plan;comparing the obtained version number with the version number associated with the retrieved execution plan; andon detecting a mismatch between the obtained version number and the retrieved version number, recompiling the stored execution plan.

2. A method of selectively recompiling stored execution plans within a database system, the stored execution plans including respective cost parameters representing the hardware capabilities of the system at the time of plan compilation, the method comprising: receiving a request to execute one of the stored execution plans;retrieving the stored execution plan from computer memory;defining a set of cost parameters representing the cost of executing the stored execution plan within the database system;comparing the defined cost parameters with the stored cost parameters associated with the retrieved execution plan; andon detecting a significant difference between the defined cost parameters and the retrieved cost parameters, recompiling the stored execution plan.

3. The method of claim 2 wherein the significant difference includes a difference of 10% or greater.

4. The method of claim 2 wherein the cost parameters include the cost to read one block from disk within the database system.

5. The method of claim 2 wherein the cost parameters include the configured size of a resource within the database system.

6. A method of selectively recompiling stored execution plans within a database system, the execution plans including respective sub-optimal indicators, the method comprising: receiving a request to execute one of the stored execution plans;retrieving the stored execution plan from computer memory;checking the status of the sub-optimal indicator associated with the retrieved execution plan; andon detecting a positive value for the sub-optimal indicator, recompiling the stored execution plan.

7. A method of selecting one or more stored execution plans within a database system for recompilation, the stored execution plans referencing respective one or more tables defined within the database system, the method comprising: collecting statistics on one or more of the tables within the database system;retrieving successive stored execution plans from computer memory; andwhere a retrieved stored execution plan references at least one table on which statistics have been collected, setting the value of a marker in the header associated with the stored execution plan to indicate that the plan is sub-optimal.

8. A method of selecting one or more stored execution plans within a database system for recompilation, the stored execution plans referencing respective one or more tables defined within the database system, the method comprising: generating one or more indices on one or more of the tables within the database system;retrieving successive stored execution plans from computer memory; andwhere a retrieved stored execution plan references at least one table for which one or more indices have been generated, setting the value of a marker in the header associated with the stored execution plan to indicate that the plan is sub-optimal.

9. A system for selectively recompiling stored execution plans within a database system, the execution plans including respective version numbers of database software operable within the database system during compilation of the respective plans, where the system is configured to: receive a request to execute one of the stored execution plans;retrieve the stored execution plan from computer memory;obtain a version number of database software operable within the database system during retrieval of the stored execution plan;compare the obtained version number with the version number associated with the retrieved execution plan; andon detecting a mismatch between the obtained version number and the retrieved version number, recompile the stored execution plan.

10. A system for selectively recompiling stored execution plans within a database system, the execution plans including respective cost parameters representing the cost of executing the stored execution plan within the database system, where the system is configured to: receive a request to execute one of the stored execution plans;retrieve the stored execution plan from computer memory;define a set of cost parameters representing the cost of executing the stored execution plan within the database system;compare the defined cost parameters with the stored cost parameters associated with the retrieved execution plan; andon detecting a significant difference between the defined cost parameters and the retrieved cost parameters, recompile the stored execution plan.

11. The system of claim 10, where the significant difference includes a difference of 10% or greater.

12. The system of claim 10 wherein the cost parameters include the cost to read one block from disk within the database system.

13. The system of claim 10 wherein the cost parameters include the configured size of a resource within the database system.

14. A system for selectively recompiling stored execution plans within a database system, the execution plans including respective sub-optimal indicators, where the system is configured to: receive a request to execute one of the stored execution plans;retrieve the stored execution plan from computer memory;check the status of the sub-optimal indicator associated with the retrieved execution plan; andon detecting a positive value for the sub-optimal indicator, recompile the stored execution plan.

15. A system for selecting one or more stored execution plans within a database system for recompilation, the stored execution plans referencing respective one or more tables defined within the database system, where the system is configured to: collect statistics on one or more of the tables within the database system;retrieve successive stored execution plans from computer memory; andwhere a retrieved stored execution plan references at least one table on which statistics have been collected, set the value of a marker in the header associated with the stored execution plan to indicate that the plan is sub-optimal.

16. A system for selecting one or more stored execution plans within a database system for recompilation, the stored execution plans referencing respective one or more tables defined within the database system, where the system is configured to: generate one or more indices on one or more of the tables within the database system;retrieve successive stored execution plans from computer memory; andwhere a retrieved stored execution plan references at least one table for which one or more indices have been generated, set the value of a marker in the header associated with the stored execution plan to indicate that the plan is sub-optimal.

17. A computer program stored on tangible storage media comprising executable instructions for performing a method of selectively recompiling stored execution plans within a database system, the execution plans including respective version numbers of database software operable within the database system during compilation of the respective plans, the method comprising: receiving a request to execute one of the stored execution plans;retrieving the stored execution plan from computer memory;obtaining a version number of database software operable within the database system during retrieval of the stored execution plan;comparing the obtained version number with the version number associated with the retrieved execution plan; andon detecting a mismatch between the obtained version number and the retrieved version number, recompiling the stored execution plan.

18. A computer program stored on tangible storage media comprising executable instructions for performing a method of selectively recompiling stored execution plans within a database system, the execution plans including respective cost parameters representing the cost of executing the stored execution plan within the database system, the method comprising: receiving a request to execute one of the stored execution plans;retrieving the stored execution plan from computer memory;defining a set of cost parameters representing the cost of executing the stored execution plan within the database system;comparing the defined cost parameters with the stored cost parameters associated with the retrieved execution plan; andon detecting a significant difference between the defined cost parameters and the retrieved cost parameters, recompiling the stored execution plan.

19. The computer program of claim 18 wherein the significant difference includes the difference of 10% or greater.

20. The computer program of claim 18 wherein the cost parameters include the cost to read one block from disk within the database system.

21. The computer program of claim 18 wherein the cost parameters include the configured size of a resource within the database system.

22. A computer program stored on tangible storage media comprising executable instructions for performing a method of selectively recompiling stored execution plans within a database system, the execution plans including respective sub-optimal indicators, the method comprising: receiving a request to execute one of the stored execution plans;retrieving the stored execution plan from computer memory;checking the status of the sub-optimal indicator associated with the retrieved execution plan; andon detecting a positive value for the sub-optimal indicator, recompiling the stored execution plan.

23. A computer program stored on tangible storage media comprising executable instructions for performing a method of selecting one or more stored execution plans within a database system for recompilation, the stored execution plans referencing respective one or more tables defined within the database system, the method comprising: collecting statistics on one or more of the tables within the database system;retrieving successive stored execution plans from computer memory; andwhere a retrieved stored execution plan references at least one table on which statistics have been collected, setting the value of a marker in the header associated with the stored execution plan to indicate that the plan is sub-optimal.

24. A computer program stored on tangible storage media comprising executable instructions for performing a method of selecting one or more stored execution plans within a database system for recompilation, the stored execution plans referencing respective one or more tables defined within the database system, the method comprising: generating one or more indices on one or more of the tables within the database system;retrieving successive stored execution plans from computer memory; andwhere a retrieved stored execution plan references at least one table for which one or more indices have been generated, setting the value of a marker in the header associated with the stored execution plan to indicate that the plan is sub-optimal.