Selective Automatic Refreshing of Stored Execution Plans

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; and on 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; and on 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; and on 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; and where 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; and where 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; and on 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; defining 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; and on 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; and on 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; and where 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; and where 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.