Fully automated SQL tuning

• US 8,903,801 B2
• Filed: 08/08/2008
• Issued: 12/02/2014
• Est. Priority Date: 09/14/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Belknap, Peter, Dageville, Benoit, Dias, Karl, Yagoub, Khaled
• Primary Examiner(s): Cao, Phuong Thao
• Application Number:
  US12/188,975
• Publication Number:
  US 20090077016A1
• Time in Patent Office: 2,307 Days
• Field of Search: 707/999.003, 707/999.005, 707/713, 707/E17.017, 707/999.002
• 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/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 steps of:

from a workload set, identifying a plurality of database query language statements for automatic tuning, wherein the workload set comprises database query language statements and current performance data for the database query language statements;

executing each database query language statement from said plurality of query language statements against a database;

collecting new performance data from said executing each database query language statement, said collecting comprising measuring resource usage by said executing each database query language statement;

detecting that conditions in said database that affect executing said plurality of database query language statements changed based at least in part on comparison of the new performance data with the current performance data;

in response to the detecting, tuning a subset of database query language statements from said plurality of database query language statements, said subset of database query language statements comprising a database query language statement from said plurality of database query language statements, wherein the new performance data is different from the current performance data for the database query language statement;

wherein the tuning the subset of database query language statements comprises generating a plurality of tuning recommendations for execution of the subset of database query language statements;

testing the plurality of tuning recommendations against said database, wherein the testing the plurality of tuning recommendations comprises, for each tuning recommendation of said plurality of tuning recommendations;

executing a respective database query language statement from said subset of database query language statements with said each tuning recommendation enabled;

measuring resource usage by said executing the respective database query language statement with said each tuning recommendation enabled, wherein the resource usage comprises processor time or buffer gets; and

measuring benefits based on performance improvement of said executing the respective database query language statement with said each tuning recommendation enabled;

based on the testing, determining a subset of said plurality of tuning recommendations resulted in performance improvement that meets a specific set of criteria;

implementing the subset of said plurality of tuning recommendations; and

wherein the steps are automatically performed by one or more computing devices.

2. 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 the specific period of time.

3. The computer-implemented method of claim 2, wherein the identifying the plurality 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.

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

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

5. The computer-implemented method of claim 1, wherein the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements 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.

6. The computer-implemented method of claim 5, wherein the controlled environment limits, to a maximum amount of time, time spent on performing the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements.

7. The computer-implemented method of claim 5, wherein a time limit enforcement mechanism external to the controlled environment limits, to a maximum amount of time, time spent on performing the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements.

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

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

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

9. The computer-implemented method of claim 1, wherein the tuning the subset of database query language statements is performed until one or more time periods are reached or exceeded, thereby generating the plurality of tuning recommendations for a particular subset of database query language statements of said subset of database query language statements, each tuning recommendation of said plurality of tuning recommendations being a tuning recommendation for a particular database query language statement of said subset of database query language statements.

10. The computer-implemented method of claim 9, wherein the enabling said each tuning recommendation against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.

11. The computer-implemented method of claim 9, wherein the tuning the subset of database query language statements until one or more time periods are reached or exceeded includes at least one of:

tuning a single database query language statement of said subset of database query language statements until a particular time period of said one or more time periods is reached or exceeded; and

tuning multiple database query language statements of said subset of database query language statements until a single time period of said one or more time periods is reached or exceeded.

12. The computer-implemented method of claim 1, wherein the enabling said each tuning recommendation against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.

13. The computer-implemented method of claim 1, wherein the resource usage comprises both processor time and buffer gets.

14. The computer-implemented method of claim 1,

wherein the implementing the subset of said plurality of tuning recommendations includes storing in association with a particular database query language statement of said subset of database query language statements respective one or more adjustment factors.

15. The computer-implemented method of claim 14 further comprises:

generating an execution plan for said particular database query language statement, wherein the generating the execution plan comprises determining that said particular database query language statement is associated with said respective one or more adjustment factors, wherein the execution plan is generated based on said respective one or more adjustment factors.

16. A computer-readable non-transitory 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 plurality of database query language statements for automatic tuning, wherein the workload set comprises database query language statements and current performance data for the database query language statements;

executing each database query language statement from said plurality of query language statements against a database;

collecting new performance data from said executing each database query language statement, said collecting comprising measuring resource usage by said executing each database query language statement;

detecting that conditions in said database that affect executing said plurality of database query language statements changed based at least in part on comparison of the new performance data with the current performance data;

in response to the detecting, tuning a subset of database query language statements from said plurality of database query language statements, said subset of database query language statements comprising a database query language statement from said plurality of database query language statements, wherein the new performance data is different from the current performance data for the database query language statement;

wherein the tuning the subset of database query language statements comprises generating a plurality of tuning recommendations for execution of the subset of database query language statements;

testing the plurality of tuning recommendations against said database, wherein the testing the plurality of tuning recommendations comprises, for each tuning recommendation of said plurality of tuning recommendations;

executing a respective database query language statement from said subset of database query language statements with said each tuning recommendation enabled;

measuring resource usage by said executing the respective database query language statement with said each tuning recommendation enabled, wherein the resource usage comprises processor time or buffer gets; and

measuring benefits based on performance improvement of said executing the respective database query language statement with said each tuning recommendation enabled;

based on the testing, determining a subset of said plurality of tuning recommendations resulted in performance improvement that meets a specific set of criteria; and

implementing the subset of said plurality of tuning recommendations.

17. The computer-readable storage medium of claim 16, 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 the specific period of time.

18. The computer-readable storage medium of claim 17, wherein the identifying the plurality 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.

19. The computer-readable storage medium of claim 16, wherein the subset of database query language statements are prioritized, the instructions further comprising instructions for:

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

20. The computer-readable storage medium of claim 16, wherein the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements 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.

21. The computer-readable storage medium of claim 20, wherein the controlled environment limits, to a maximum amount of time, time spent on performing the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements.

22. The computer-readable storage medium of claim 20, wherein a time limit enforcement mechanism external to the controlled environment limits, to a maximum amount of time, time spent on performing the identifying the plurality of database query language statements for automatic tuning and tuning the subset of database query language statements from the plurality of database query language statements.

23. The computer-readable storage medium of claim 20, wherein:

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

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

24. The computer-readable storage medium of claim 16, wherein the tuning the subset of database query language statements is performed until one or more time periods are reached or exceeded, thereby generating the plurality of tuning recommendations for a particular subset of database query language statements of said subset of database query language statements, each tuning recommendation of said plurality of tuning recommendations being a tuning recommendation for a particular database query language statement of said subset of database query language statements.

25. The computer-readable storage medium of claim 24, wherein the enabling said each tuning recommendation against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.

26. The computer-readable storage medium of claim 24, wherein the tuning the subset of database query language statements until one or more time periods are reached or exceeded includes at least one of:

tuning a single database query language statement of said subset of database query language statements until a particular time period of said one or more time periods is reached or exceeded; and

tuning multiple database query language statements of said subset of database query language statements until a single time period of said one or more time periods is reached or exceeded.

27. The computer-readable storage medium of claim 16, wherein the enabling said each tuning recommendation against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.

28. The computer-readable storage medium of claim 16, wherein the

resource usage comprises both processor time and buffer gets.

29. The computer-readable storage medium of claim 16,

wherein the implementing the subset of said plurality of tuning recommendations includes storing in association with a particular database query language statement of said subset of database query language statements respective one or more adjustment factors.

30. The computer-readable storage medium of claim 29, wherein the instructions further comprise instructions for:

generating an execution plan for said particular database query language statement,

wherein the generating the execution plan comprises determining that said particular database query language statement is associated with said respective one or more adjustment factors, wherein the execution plan is generated based on said respective one or more adjustment factors.