Fully automated SQL tuning

• US 9,720,941 B2
• Filed: 11/20/2014
• Issued: 08/01/2017
• Est. Priority Date: 09/14/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Dageville, Benoit, Dias, Karl, Belknap, Peter, Yagoub, Khaled
• Primary Examiner(s): Cao, Phuong Thao
• Application Number:
  US14/549,449
• Publication Number:
  US 20150081669A1
• Time in Patent Office: 985 Days
• Field of Search: 707718
• 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, automatically selecting a plurality of database query language statements for automatic tuning, wherein the workload set comprises multiple database query language statements and performance data for the multiple database query language statements;

automatically tuning the plurality of database query language statements until one or more time periods are reached or exceeded, thereby generating a plurality of tuning recommendations for a subset of database query language statements of said plurality of database query language statements, each tuning recommendation of said plurality of tuning recommendations being a tuning recommendation for a database query language statement of said subset of database query language statements;

automatically testing the plurality of tuning recommendations against a database, wherein testing the plurality of tuning recommendations comprises, for one or more tuning recommendations of said plurality of tuning recommendations, automatically executing a particular database query language statement from said subset of database query language statements with said one or more tuning recommendations enabled thereby automatically generating an execution plan for the particular database query language statement based on said one or more tuning recommendations enabled for testing and automatically testing said execution plan against one or more other alternative execution plans for the particular database query language statement generated based on enabling, for testing, one or more other tuning recommendations from the plurality of tuning recommendations;

based at least in part on automatically testing the plurality of tuning recommendations against the database, automatically implementing at least one tuning recommendation of said plurality of tuning recommendations and not implementing at least another tuning recommendation of said plurality of tuning recommendations; and

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

2. The computer-implemented method of claim 1, wherein enabling said one or more tuning recommendations of the plurality of tuning recommendations against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.

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

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

or

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

4. The computer-implemented method of claim 3, wherein the single time period is tracked by an independent and separate mechanism from a mechanism performing the tuning.

5. The computer-implemented method of claim 1, wherein the testing said plurality of tuning recommendations further comprises measuring an execution time period for executing the particular database query language statement with said one or more tuning recommendations enabled.

6. The computer-implemented method of claim 5, further comprising:

if the execution time period for executing the particular database query language statement reaches or exceeds an execution time period for a completed execution of the particular database query language statement with different one or more tuning recommendations from said plurality of tuning recommendations, enabled, then aborting the executing the particular database query language statement.

7. The computer-implemented method of claim 5, wherein the execution time period is limited to a certain time period, which is extended to complete at least one execution of the particular database query language statement with a certain tuning recommendation, from said plurality of tuning recommendations, enabled.

8. The computer-implemented method of claim 1, wherein the testing further comprises measuring resource usage by said executing the particular database query language statement with said one or more tuning recommendations enabled.

9. The computer-implemented method of claim 1, wherein the testing further comprises measuring benefits based on performance improvement of said executing the particular database query language statement with said one or more tuning recommendations enabled.

10. The computer-implemented method of claim 9, further comprising, based on the testing, determining a subset of said plurality of tuning recommendations resulted in performance improvement that meets a criterion.

11. The computer-implemented method of claim 1, wherein the automatically selecting the plurality of database query language statements from the workload set is based at least on a frequency of one or more executions of each of the plurality of database query language statements.

12. The computer-implemented method of claim 1, wherein said automatically selecting the plurality of database query language statements for automatic tuning, said automatically tuning the plurality of database query language statements, and said automatically testing the plurality of tuning recommendations against the database are performed until a certain time period, of the one or more time periods, is reached.

13. The computer-implemented method of claim 12, wherein said automatically implementing the at least one tuning recommendation is also performed until the certain time period is reached.

14. The computer-implemented method of claim 1, further comprising automatically increasing a testing time period, configured to limit automatically testing one or more execution plans, until finishing automatic execution of at least one execution plan for the particular database query language statement.

15. The computer-implemented method of claim 1, further comprising automatically terminating said automatically testing said execution plan when resulting performance data for said execution plan is automatically determined to be worse than performance data of at least one of the one or more other alternative execution plans for the particular database query language statement.

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, automatically selecting a plurality of database query language statements for automatic tuning, wherein the workload set comprises multiple database query language statements and performance data for the multiple database query language statements;

automatically tuning the plurality of database query language statements until one or more time periods for said tuning the plurality of database query language statements are reached or exceeded, thereby generating a plurality of tuning recommendations for a subset of database query language statements of said plurality of database query language statements, each tuning recommendation of said plurality of tuning recommendations being a tuning recommendation for a database query language statement of said subset of database query language statements;

automatically testing the plurality of tuning recommendations against a database, wherein testing the plurality of tuning recommendations comprises, for one or more tuning recommendations of said plurality of tuning recommendations, automatically executing a particular database query language statement from said subset of database query language statements with said one or more tuning recommendations enabled thereby automatically generating an execution plan for the particular database query language statement based on said one or more tuning recommendations enabled for testing and automatically testing said execution plan against one or more other alternative execution plans for the particular database query language statement generated based on enabling, for testing, one or more other tuning recommendations from the plurality of tuning recommendations;

based at least in part on automatically testing the plurality of tuning recommendations against the database, automatically implementing at least one tuning recommendation of said plurality of tuning recommendations and not implementing at least another tuning recommendation of said plurality of tuning recommendations.

17. The computer-readable non-transitory storage medium of claim 16, wherein enabling said one or more tuning recommendations of the plurality of tuning recommendations against said database causes a number of database changes, wherein the number of database changes is below a maximum number of database changes.

18. The computer-readable non-transitory storage medium of claim 16, wherein the tuning the plurality of database query language statements until the one or more time periods are reached or exceeded includes at least one of:

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

or

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

19. The computer-readable non-transitory storage medium of claim 18, wherein the single time period is tracked by an independent and separate mechanism from a mechanism performing the tuning.

20. The computer-readable non-transitory storage medium of claim 16, wherein the testing said plurality of tuning recommendations further comprises measuring an execution time period for executing the particular database query language statement with said one or more tuning recommendations enabled.

21. The computer-readable non-transitory storage medium of claim 20, the instructions further comprising instructions for:

if the execution time period for executing the particular database query language statement reaches or exceeds an execution time period for a completed execution of the particular database query language statement with different one or more tuning recommendations from said plurality of tuning recommendations, enabled, then aborting the executing the particular database query language statement.

22. The computer-readable non-transitory storage medium of claim 20, wherein the execution time period is limited to a certain time period, which is extended to complete at least one execution of the particular database query language statement with a certain tuning recommendation, from said plurality of tuning recommendations, enabled.

23. The computer-readable non-transitory storage medium of claim 16, wherein the testing further comprises measuring resource usage by said executing the particular database query language statement with said one or more tuning recommendations enabled.

24. The computer-readable non-transitory storage medium of claim 16, wherein the testing further comprises measuring benefits based on performance improvement of said executing the particular database query language statement with said one or more tuning recommendations enabled.

25. The computer-readable non-transitory storage medium of claim 24, the instructions further comprising instructions for, based on the testing, determining a subset of said plurality of tuning recommendations resulted in performance improvement that meets a criterion.