FULLY AUTOMATED SQL TUNING

• US 20090077016A1
• Filed: 08/08/2008
• Published: 03/19/2009
• Est. Priority Date: 09/14/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Dias, Karl, Belknap, Peter, Yagoub, Khaled, Dageville, Benoit
• Granted Patent: US 8,903,801 B2
• Time in Patent Office: Days
• 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/2453 Query optimisation
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

Techniques are provided for a fully-automated process for tuning database query language statements that selects database query language statements for tuning, tunes the database query language statements and generates tuning recommendations, tests the tuning recommendations, and determines whether to implement the tuning recommendations based on the test results. The fully-automated tuning process may also automatically implement certain tuning recommendations and monitor the performance of the database query language statements for which tuning recommendations have been implemented.

Claims

1. A computer-implemented method comprising:

from a workload set, identifying a set of database query language statements for automatic tuning;

wherein the workload set comprises database query language statements and performance data for the database query language statements;

automatically tuning a particular database query language statement from the set of database query language statements;

wherein automatically tuning the particular database query language statement comprises generating a set of one or more tuning recommendations for the particular database query language statement;

executing the particular database query language statement in a test;

wherein executing the particular database query language statement in the test comprises;

enabling a first tuning recommendation from the set of one or more tuning recommendations in an environment; and

gathering performance data about the execution of the particular database query language statement in the test;

comparing the performance data about execution of the particular database query language statement in the test with performance data about the particular database query language statement in the workload set; and

based on at least one result of the comparison, determining whether to implement a second tuning recommendation from the set of one or more tuning recommendations in a non-test environment.

2. The computer-implemented method of claim 1, wherein the first tuning recommendation is same as the second tuning recommendation.

3. The computer-implemented method of claim 1, wherein the first tuning recommendation is different from the second tuning recommendation.

4. The computer-implemented method of claim 1, wherein the workload set comprises database query language statements that have been executed over a specific period of time and performance data for the database query language statements that have been executed over a specific period of time.

5. The computer-implemented method of claim 4, wherein identifying a set of database query language statements for automatic tuning comprises identifying a set of database query language statements as having long execution times and high frequencies of execution during the specific period of time.

6. The computer-implemented method of claim 4, wherein identifying a set of database query language statements as having long execution times and high frequencies of execution comprises identifying a set of database query language statements that have long execution times and high frequencies of execution during a period of time within the specific period of time.

7. The computer-implemented method of claim 1, wherein database query language statements in the set of database query language statements are prioritized, further comprising:

automatically tuning database query language statements from the set of database query language statements in order of priority.

8. The computer-implemented method of claim 1, wherein:

the particular database query language statement is an SQL statement;

the set of one or more tuning recommendations includes a tuning recommendation to implement an SQL Profile; and

an SQL Profile specifies statistics adjustment factors for the particular database query language statement.

9. The computer-implemented method of claim 1, wherein determining whether to implement the second tuning recommendation in a non-test environment comprises determining whether comparing the performance data about the execution of the particular database query language statement in the test with performance data about the particular database query language statement in the workload set indicates a performance improvement of at least a specific amount.

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

in response to determining to implement the second tuning recommendation in a non-test environment, implementing the second tuning recommendation in a non-test environment.

11. The computer-implemented method of claim 1, wherein the steps of identifying a set of query language statements, automatically tuning a particular database query language statement, executing the particular database query language statement in a test, comparing the performance data about the execution of the particular database query language statement in the test, and determining whether to implement the second tuning recommendation are performed in a controlled environment on a machine in a manner such that activities in the controlled environment do not interrupt activities on the machine outside the controlled environment.

12. The computer-implemented method of claim 11, wherein the controlled environment limits the maximum amount of time spent on performing the steps of identifying a set of query language statements, automatically tuning a particular database query language statement, executing the particular database query language statement in a test, comparing the performance data about the execution of the particular database query language statement in the test, and determining whether to implement the second tuning recommendation.

13. The computer-implemented method of claim 12, wherein the controlled environment further limits the maximum amount of time spent on automatically tuning a particular database query language statement.

14. The computer-implemented method of claim 11, wherein a time limit enforcement mechanism external to the control environment limits the maximum amount of time spent on performing the steps of identifying a set of query language statements, automatically tuning a particular database query language statement, executing the particular database query language statement in a test, comparing the performance data about the execution of the particular database query language statement in the test, and determining whether to implement the second tuning recommendation in the control environment.

15. The computer-implemented method of claim 11, wherein:

the controlled environment periodically checks if the activities within the controlled environment are causing any contention with an activity outside the controlled environment; and

the controlled environment aborts the activities within the controlled environment upon detection that activities within the controlled environment are causing a contention with an activity outside the controlled environment.

16. The computer-implemented method of claim 10, further comprising:

gathering performance data about the execution of the particular database query language statement with the second tuning recommendation implemented in the non-test environment.

17. The computer-implemented method of claim 10, further comprising:

generating a report that identifies the particular database query language statement, describes the set of one or more tuning recommendations, describes performance data about the execution of the particular database query language statement in the test, and whether the tuning recommendation is implemented in the non-test environment.

18. A computer-readable storage medium storing instructions, wherein the instructions include instructions which, when executed by one or more processors, cause the one or more processors to perform steps of:

from a workload set, identifying a set of database query language statements for automatic tuning;

wherein the workload set comprises database query language statements and performance data for the database query language statements;

automatically tuning a particular database query language statement from the set of database query language statements;

wherein automatically tuning the particular database query language statement comprises generating a set of one or more tuning recommendations for the particular database query language statement;

executing the particular database query language statement in a test;

wherein executing the particular database query language statement in the test comprises;

implementing a first tuning recommendation from the set of one or more tuning recommendations in an environment; and

gathering performance data about the execution of the particular database query language statement in the test;

comparing the performance data about execution of the particular database query language statement in the test with performance data about the particular database query language statement in the workload set; and

based on at least one result of the comparison, determining whether to implement a second tuning recommendation from the set of one or more tuning recommendations in a non-test environment.

19. The computer-readable storage medium of claim 18, wherein the first tuning recommendation is same as the second tuning recommendation.

20. The computer-readable storage medium of claim 19, wherein the first tuning recommendation is different from the second tuning recommendation.

21. The computer-readable storage medium of claim 18, wherein the workload set comprises database query language statements that have been executed over a specific period of time and performance data for the database query language statements that have been executed over a specific period of time.

22. The computer-readable storage medium of claim 21, wherein the instructions for identifying a set of database query language statements for automatic tuning comprises instructions for identifying a set of database query language statements as having long execution times and high frequencies of execution during the specific period of time.

23. The computer-readable storage medium of claim 21, wherein the instructions for identifying a set of database query language statements as having long execution times and high frequencies of execution comprises instructions for identifying a set of database query language statements that have long execution times and high frequencies of execution during a period of time within the specific period of time.

24. The computer-readable storage medium of claim 18, wherein database query language statements in the set of database query language statements are prioritized, further comprising instructions for:

automatically tuning database query language statements from the set of database query language statements in order of priority.

25. The computer-readable storage medium method of claim 18, wherein:

the particular database query language statement is an SQL statement;

the set of one or more tuning recommendations includes a tuning recommendation to implement an SQL Profile; and

an SQL Profile specifies statistics adjustment factors for the particular database query language statement.

26. The computer-readable storage medium of claim 18, wherein the instructions for determining whether to implement the second tuning recommendation in a non-test environment comprises instructions for determining whether comparing the performance data about the execution of the particular database query language statement in the test with performance data about the particular database query language statement in the workload set indicates a performance improvement of at least a specific amount.

27. The computer-readable storage medium of claim 18, further comprising instructions for:

in response to determining to implement the second tuning recommendation in a non-test environment, implementing the second tuning recommendation in a non-test environment.

28. The computer-readable storage medium of claim 18, wherein the instructions for identifying a set of query language statements, automatically tuning a particular database query language statement, executing the particular database query language statement in a test, comparing the performance data about the execution of the particular database query language statement in the test, and determining whether to implement the second tuning recommendation are executed in a controlled environment on a machine in a manner such that activities in the controlled environment do not interrupt activities on the machine outside the controlled environment.

29. The computer-readable storage medium of claim 28, wherein the controlled environment limits the maximum amount of time spent on executing the instructions for identifying a set of query language statements, automatically tuning a particular database query language statement, executing the particular database query language statement in a test, comparing the performance data about the execution of the particular database query language statement in the test, and determining whether to implement the second tuning recommendation.

30. The computer-readable storage medium of claim 29, wherein the controlled environment further limits the maximum amount of time spent on executing instructions for automatically tuning a particular database query language statement.

31. The computer-readable storage medium of claim 28, wherein a time limit enforcement mechanism external to the control environment limits the maximum amount of time spent on executing instructions for identifying a set of query language statements, automatically tuning a particular database query language statement, executing the particular database query language statement in a test, comparing the performance data about the execution of the particular database query language statement in the test, and determining whether to implement the second tuning recommendation in the control environment.

32. The computer-readable storage medium of claim 28, wherein:

the controlled environment periodically checks if the activities within the controlled environment are causing any contention with an activity outside the controlled environment; and

the controlled environment aborts the activities within the controlled environment upon detection that activities within the controlled environment are causing a contention with an activity outside the controlled environment.

33. The computer-readable storage medium of claim 27, further comprising instructions for:

gathering performance data about the execution of the particular database query language statement with the second tuning recommendation implemented in the non-test environment.

34. The computer-readable storage medium of claim 27, further comprising instructions for:

generating a report that identifies the particular database query language statement, describes the set of one or more tuning recommendations, describes performance data about the execution of the particular database query language statement in the test, and whether the tuning recommendation is implemented in the non-test environment.