Preserving a Query Plan Cache

• US 20090100114A1
• Filed: 10/10/2007
• Published: 04/16/2009
• Est. Priority Date: 10/10/2007
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bestgen, Robert Joseph, Santosuosso, John Matthew, Kethireddy, Shantan
• Granted Patent: US 7,831,569 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 11/2071 using a plurality of controllers
• CPC Class Codes: G06F 11/2097 maintaining the standby controller/processing unit updated initialisation or re-synchronisation thereof G06F11/1658 and subgroups
• CPC Class Codes: G06F 11/3476 Data logging G06F11/14, G06F11/2205 take precedence
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

A method, apparatus, and program product are provided for preserving a query plan cache on a backup system having a dataspace containing a copy of data and a copy of a query plan cache from a production system. An update is initiated of at least a portion of the copy of the data on the backup system with a portion of the data on the production system. A merge is initiated of updated query plans in the query plan cache from the production system with corresponding query plans in the copy of the query plan cache on the backup system. Objects are correlated in the updated query plans in the copy of the query plan cache with the updated copy of the data on the backup system.

Claims

1. A method for preserving a query plan cache, the method comprising, in a backup system having a dataspace containing a copy of data and a copy of a query plan cache from a production system:

initiating an update of at least a portion of the copy of the data on the backup system with a portion of the data on the production system;

initiating a merge of updated query plans in the query plan cache from the production system with corresponding query plans in the copy of the query plan cache on the backup system; and

correlating objects in the updated query plans in the copy of the query plan cache with the updated copy of the data on the backup system.

2. The method of claim 1 wherein merging updated query plans comprises:

marking, on the production system, query plans in the query plan cache that have changed since the last update; and

shipping updated query plans from the query plan cache on the production system to the copy of the query plan cache on the backup system to update the copy of the query plan cache.

3. The method of claim 1 wherein correlating objects comprises:

correlating objects in the updated query plans with the updated copy of the data based on name.

4. The method of claim 3 wherein the name is a long name.

5. The method of claim 3 wherein the name is a short name.

6. The method of claim 1 further comprising:

performing statistics collection on the backup system for the updated plans in the copy of the query plan cache.

7. The method of claim 6 wherein performing statistics collection comprises:

performing deep statistics collections; and

creating run time objects referenced in the updated copy of the query plan cache.

8. The method of claim 7 wherein run time objects are selected from the group consisting of hash tables, sorts, maintained temporary indexes, and combinations thereof.

9. The method of claim 7 further comprising:

utilizing a query time limit governor to limit statistics collections and index creations.

10. The method of claim 7 further comprising:

utilizing a storage limit governor to limit statistics collections and index creations.

11. An apparatus comprising:

a processor; and

program code configured to be executed by the processor to preserve a query plan cache on a backup system having a dataspace containing a copy of data and a copy of a query plan cache from a production system, the program code resident on the computer readable medium and configured to initiate an update of at least a portion of the copy of the data on the backup system with a portion of the data on the production system, initiate a merge of updated query plans in the query plan cache from the production system with corresponding query plans in the copy of the query plan cache on the backup system, and correlate objects in the updated query plans in the copy of the query plan cache with the updated copy of the data on the backup system.

12. The apparatus of claim 11 wherein the program code is configured to merge updated query plans by marking, on the production system, query plans in the query plan cache that have changed since the last update, and shipping updated query plans from the query plan cache on the production system to the copy of the query plan cache on the backup system to update the copy of the query plan cache.

13. The apparatus of claim 11 wherein the program code is configured to correlate objects by correlating objects in the updated query plans with the updated copy of the data based on name.

14. The apparatus of claim 13 wherein the name is a long name.

15. The apparatus of claim 13 wherein the name is a short name.

16. The apparatus of claim 11 wherein the program product is further configured to perform statistics collection on the backup system for the updated plans in the copy of the query plan cache.

17. The apparatus of claim 16 wherein the program product is configured to perform statistics collection by performing deep statistics collections, and creating run time objects referenced in the updated copy of the query plan cache.

18. The apparatus of claim 17 wherein run time objects are selected from the group consisting of hash tables, sorts, maintained temporary indexes, and combinations thereof.

19. The apparatus of claim 17 wherein the program product is further configured to utilize a query time limit governor to limit statistics collections and index creations.

20. The apparatus of claim 17 wherein the program product is further configured to utilize a storage limit governor to limit statistics collections and index creations.

21. A program product, comprising:

a computer readable medium; and

a program code configured to preserve a query plan cache on a backup system having a dataspace containing a copy of data and a copy of a query plan cache from a production system, the program code resident on the computer readable medium and configured to initiate an update of at least a portion of the copy of the data on the backup system with a portion of the data on the production system, initiate a merge of updated query plans in the query plan cache from the production system with corresponding query plans in the copy of the query plan cache on the backup system, and correlate objects in the updated query plans in the copy of the query plan cache with the updated copy of the data on the backup system.

22. The program product of claim 21 wherein the program code is configured to initiate a merge of updated query plans by marking, on the production system, query plans in the query plan cache that have changed since the last update, and shipping updated query plans from the query plan cache on the production system to the copy of the query plan cache on the backup system to update the copy of the query plan cache.

23. The program product of claim 21 wherein the program code is configured to correlate objects by correlating objects in the updated query plans with the updated copy of the data based on name.

24. The program product of claim 21 wherein the program code is further configured to perform statistics collection on the backup system for the updated plans in the copy of the query plan cache.

25. The program product of claim 24 wherein the program code is configured to perform statistics collection by performing deep statistics collections, and creating run time objects referenced in the updated copy of the query plan cache.