SQL tuning sets

• US 7,664,778 B2
• Filed: 09/07/2004
• Issued: 02/16/2010
• Est. Priority Date: 09/06/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Dias, Karl, Yagoub, Khaled, Dageville, Benoit, Ziauddin, Mohamed
• Primary Examiner(s): Vo; Tim T.
• Assistant Examiner(s): Morrison; Jay A
• Application Number:
  US10/936,449
• Publication Number:
  US 20050125393A1
• Time in Patent Office: 1,988 Days
• Field of Search: 707/103, 707/1, 707/2, 707/3, 707/7, 707/103.R, 707/103.Y
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A method and system for storing one or more database query language statements and performance information for each query language statement as a persistent database object. In one or more embodiments, the method and system stores the resource intensive statements and the performance information for each resource intensive statement as a second persistent database object.

Claims

1. A computer implemented method, comprising:

populating a plurality of database query language statements and corresponding performance information as a plurality of first persistent database objects in a database;

identifying, by using a processor, one or more database query language statements from the plurality of database query language statements and performance information for the one or more data base query language statements, wherein

the plurality of database query language statements are filtered based at least in part upon identification of the one or more high load database query language statements such that the set of the one or more high load query statements are identified by ranking and selecting the one or more database query language statements from the set of one or more high load query statements, in which at least one of the set of the one or more high load database query language statements are tuned by using at least a part of the performance information;

persistently storing the one or more database query language statements and a first performance information as one or more second persistent database objects, wherein

the first performance information is used to tune one of the one or more database query language statements as at least some of the one or more second persistent database objects,

the first performance information comprises execution measurements and execution context corresponding to the one or more database query language statements, and

the execution context comprises at least one of a user schema, a name of an application issuing a query language statement, an action of an application issuing a query language statement, a list of bind variables, and system environment information.

2. The method of claim 1, further comprising:

filtering the statements based on the performance information.

3. The method of claim 2, further comprising:

identifying a portion of the filtered statements as resource intensive statements.

4. The method of claim 3, further comprising:

storing the resource intensive statements and the performance information for each resource intensive statement as a second persistent database object.

5. The method of claim 4, further comprising:

automatically tuning each of the one or more database query language statements in the second persistent database object.

6. The method of claim 1, wherein the database query language statements are SQL statements.

19. The method of claim 1, further comprising:

filtering the statements based on one or more user-specified criteria; and

storing the filtered statements and the performance information for each filtered statement as a third persistent database object.

20. The method of claim 19, wherein the user-specified criteria comprise one selected from the group consisting of:

a representative set of statements for a given application, all statements for a given application, and incremental capture.

21. The computer implemented method of claim 1, further comprising:

determining whether a second database query language statement is to be persistently stored due to a future change to the database, wherein the second database query language statement is determined not to be persistently stored as the one or more database query language statements before an implementation of the future change.

7. An apparatus, comprising:

means for populating a plurality of database query language statements and corresponding performance information as a plurality of first persistent database objects in a database;

a processor configured for identifying one or more database query language statements from the plurality of database query language statements and performance information for the one or more data base query language statements, wherein

the plurality of database query language statements are filtered based at least in part upon identification of the one or more high load database query language statements such that the set of the one or more high load query statements are identified by ranking and selecting the one or more database query language statements from the set of one or more high load query statements, in which at least one of the set of the one or more high load database query language statements are tuned by using at least a part of the performance information;

means for persistently storing the one or more database query language statements and performance information as one or more second persistent database objects, wherein

the performance information is used to tune one of the one or more database query language statements as at least some of the one or more second persistent database objects,

the performance information comprises execution measurements and execution context corresponding to the one or more database query language statements, and

the execution context comprises at least one of a user schema, a name of an application issuing a query language statement, an action of an application issuing a query language statement, a list of bind variables, and system environment information.

8. The apparatus of claim 7, further comprising:

means for filtering the statements based on the performance information.

9. The apparatus of claim 8, further comprising:

means for identifying a portion of the filtered statements as resource intensive statements.

10. The apparatus of claim 9, further comprising:

means for storing the resource intensive statements and the performance information for each resource intensive statement as a second persistent database object.

11. The apparatus of claim 10, further comprising:

means for automatically tuning each statement in the second persistent database object.

12. The apparatus of claim 7, wherein the database query language statements are SQL statements.

13. A computer readable medium storing a computer program of instructions which, when executed by a processing system, cause the processing system to perform a process, the process comprising:

populating a plurality of database query language statements and corresponding performance information as a plurality of first persistent database objects in a database;

identifying, by using a processor, one or more database query language statements from the plurality of database query language statements and performance information for the one or more data base query language statements, wherein

the plurality of database query language statements are filtered based at least in part upon identification of the one or more high load database query language statements such that the set of the one or more high load query statements are identified by ranking and selecting the one or more database query language statements from the set of one or more high load query statements, in which at least one of the set of the one or more high load database query language statements are tuned by using at least a part of the performance information;

persistently storing the one or more query language statements and a first performance information as one or more second persistent database objects, wherein

the first performance information is used to tune one of the one or more database query language statements as at least some of the one or more second persistent database objects,

the first performance information comprises execution measurements and execution context corresponding to the one or more database query language statements, and

the execution context comprises at least one of a user schema, a name of an application issuing a query language statement, an action of an application issuing a query language statement, a list of bind variables, and system environment information.

14. The medium of claim 13, wherein the computer program, when executed, further performs the method comprising:

filtering the statements based on the performance information.

15. The medium of claim 14, wherein the computer program, when executed, further performs the method comprising:

identifying a portion of the filtered statements as resource intensive statements.

16. The medium of claim 15, wherein the computer program, when executed, further performs the method comprising:

storing the resource intensive statements and the performance information for each resource intensive statement as a second persistent database object.

17. The medium of claim 16, wherein the computer program, when executed, further performs the method comprising:

automatically tuning each statement in the second persistent database object.

18. The medium of claim 13, wherein the query language statements are SQL statements.