Automatic SQL tuning advisor

• US 7,747,606 B2
• Filed: 09/07/2004
• Issued: 06/29/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: Das, Dinesh, Dias, Karl, Zait, Mohamed, Ramacher, Mark, Yagoub, Khaled, Dageville, Benoit, Tan, Leng Leng, Ziauddin, Mohamed
• Primary Examiner(s): Cottingham; John R.
• Assistant Examiner(s): Pham; Michael
• Application Number:
  US10/936,778
• Publication Number:
  US 20050125427A1
• Time in Patent Office: 2,121 Days
• Field of Search: 707 1-100 S
• US Class Current: 707/713
• 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 for receiving a database query language statement and performance information about the statement at an optimizer and generating one or more tuning actions for the statement with the performance information is disclosed.

Claims

1. A computer implemented method, comprising:

using a computer system which comprises at least one computer processor and is programmed for performing;

receiving a database query language statement and performance information about the database query language statement at an optimizer;

receiving a time budget for tuning the database query language statement;

generating one or more tuning recommendations for the database query language statement,

wherein

the one or more tuning recommendations are generated based at least in part upon the performance information with consideration of the time budget, the one or more tuning recommendations comprise one or more rewrites of database query language text for the database query language statement,

the generating the one or more tuning recommendations further comprises;

performing an automatic access path analysis to recommend one or more indices based at least in part on one or more data access patterns of the database query language statement and upon when the database query language statement performance is improved by replacing one or more existing access path by one or more new index access paths,

performing an automatic structure analysis that generates one or more tuning recommendations for one or more rewrites for the database query language statement, and

performing an automatic statistics analysis that generates adjustment factors to correct one or more missing or stale statistics,

at least one of the one or more rewrites of the database query language text is not semantically equivalent to the database query language text of the database query language statement in that the at least one of the one or more rewrites of the database query language text comprises a second query operator function which is used to replace a different, first query operation function in the database query language text of the database query language statement and the one or more rewrites of the database query language text produces an equivalent query result as the database query language text of the database query language statement does, and

at least one of the one or more tuning recommendations is associated with a rationale to help a user understand the at least one of the one or more tuning recommendations, in which the rationale comprises information about an execution plans before applying the at least one of the one or more tuning recommendations and one or more first execution plans that are generated after applying the at least one of the one or more tuning recommendations; and

displaying the one or more tuning recommendations on a display device or storing the one or more tuning recommendations in data structures configured for the tuning recommendations on a computer readable storage medium of the computer system.

2. The computer implemented method of claim 1, wherein each of the one or more tuning recommendations includes information for a database system to automatically execute the one or more tuning recommendations.

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

storing the one or more tuning recommendations in a memory device;

retrieving the one or more tuning recommendations that are stored; and

generating one or more additional recommendations.

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

displaying the one or more recommendations to a user;

receiving a selection of a subset of the one or more tuning recommendations from the user; and

determining a benefit for the subset of the one or more tuning recommendations.

5. The computer implemented method of claim 1, wherein generating the one or more tuning recommendations is performed automatically.

6. The computer implemented method of claim 1, wherein the database query language statement is a SQL statement.

20. The computer implemented method of claim 1 in which the time budget comprises a time limit of at least 30 minutes.

7. An apparatus, comprising:

a computer readable storage medium that holds computing instructions for execution;

at least one computer processor that executes the computing instructions, wherein the computing instructions comprise;

integrating tuning advice for a high load statement from a plurality of sources;

receiving a time budget for tuning the high load statement; and

generating one or more tuning recommendations from the tuning advice with consideration of the time budget, wherein

the one or more tuning recommendations comprise one or more rewrites of database query language text for the high load statement, and

the generating the one or more tuning recommendations further comprises;

performing an automatic access path analysis to recommend one or more indices based at least in part on one or more data access patterns of the high load statement and upon when performance of the high load statement is improved by replacing one or more existing access path by one or more new index access paths,

performing an automatic structure analysis that generates one or more tuning recommendations for one or more rewrites for the high load statement, and

performing an automatic statistics analysis that generates adjustment factors to correct one or more missing or stale statistics,

at least one of the one or more rewrites of the database query language text is not semantically equivalent to the database query language text of the high load statement in that the at least one of the one or more rewrites of the database query language text comprises a second query operator function which is used to replace a different, first query operation function in the database query language text of the high load statement and the one or more rewrites of the database query language text produces an equivalent query result as the database query language text of the high load statement does, and

at least one of the one or more tuning recommendations is associated with a rationale to help a user understand the at least one of the one or more tuning recommendations, in which the rationale comprises information about an execution plans before applying the at least one of the one or more tuning recommendations and one or more first execution plans that are generated after applying the at least one of the one or more tuning recommendations.

8. The apparatus of claim 7, wherein one of the sources is selected from the group consisting of:

means for analyzing the accuracy of statistics related to the high load statement;

means for correcting inaccurate statistics;

means for storing a profile of auxiliary information related to the high load statement;

means for analyzing an access path related to the high load statement; and

means for generating the profile for the statement.

9. The apparatus of claim 7, further comprising:

means for presenting the one or more recommendations to a user.

10. The apparatus of claim 9, further comprising:

means for selecting one or more of the presented recommendations.

11. The apparatus of claim 10, further comprising:

means for generating a tuned execution plan for the high load statement based on the selected recommendations.

12. The apparatus of claim 7, wherein the high load statement is a SQL statement.

21. The apparatus of claim 7 in which the time budget comprises a time limit of at least 30 minutes.

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

using the processing system which comprises at least one computer processor and is programmed for performing;

receiving a database query language statement and performance information about the database query language statement at an optimizer;

receiving a time budget for tuning the database query language statement; and

generating one or more tuning recommendations for the database query language statement,

wherein

the one or more tuning recommendations are generated based at least in part upon the performance information with consideration of the time budget, the one or more tuning recommendations comprise one or more rewrites of database query language text for the database query language statement,

the generating the one or more tuning recommendations further comprises;

performing an automatic access path analysis to recommend one or more indices based at least in part on one or more data access patterns of the database query language statement and upon when the database query language statement performance is improved by replacing one or more existing access path by one or more new index access paths,

performing an automatic structure analysis that generates one or more tuning recommendations for one or more rewrites for the database query language statement, and

performing an automatic statistics analysis that generates adjustment factors to correct one or more missing or stale statistics,

at least one of the one or more rewrites of the database query language text is not semantically equivalent to the database query language text of the database query language statement in that the at least one of the one or more rewrites comprises a second query operation function which is used to replace a different, first query operation function in the database query language text of the database query language statement and the database query language text produces an equivalent query result as the database query language text of the database query language statement does, and

at least one of the one or more tuning recommendations is associated with a rationale to help a user understand the at least one of the one or more tuning recommendations, in which the rationale comprises information about an execution plans before applying the at least one of the one or more tuning recommendations and one or more first execution plans that are generated after applying the at least one of the one or more tuning recommendations.

14. The computer readable storage medium of claim 13, wherein each of the one or more tuning recommendations comprises:

a cost benefit analysis of generating an execution plan with the one or more tuning recommendations compared to generating an execution plan without the one or more tuning recommendations.

15. The computer readable storage medium of claim 13, wherein each of the one or more tuning recommendations includes information for a database system to automatically execute the one or more tuning recommendations.

16. The computer readable storage medium of claim 13, further comprising:

storing the one or more tuning recommendations in a memory device;

retrieving the stored one or more tuning recommendations that are stored; and

generating one or more additional recommendations.

17. The computer readable storage medium of claim 13, further comprising:

displaying the one or more tuning recommendations to a user;

receiving a selection of a subset of the one or more tuning recommendations from the user; and

determining a benefit for the subset of the one or more tuning recommendations.

18. The computer readable storage medium of claim 13, wherein generating the one or more tuning recommendations is performed automatically.

19. The computer readable storage medium of claim 13, wherein the database query language statement comprises a SQL statement.

22. The computer readable storage medium of claim 13 in which the time budget comprises a time limit of at least 30 minutes.