Fully automated SQL tuning

Patent metadata

Field	Value
Publication number	US-9720941-B2
Application number	US-201414549449-A
Country	US
Kind code	B2
Filing date	Nov 20, 2014
Priority date	Sep 14, 2007
Publication date	Aug 1, 2017
Grant date	Aug 1, 2017

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.

First claim

What is claimed is:

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

Assignees

• Oracle Int Corp

Inventors

• Belknap Peter
• Dageville Benoit
• Dias Karl
• Yagoub Khaled

Classifications

• G06F16/2455

  Query execution · CPC title

• G06F16/2453

  Query optimisation · CPC title

• G06F16/217

  Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409) · CPC title

• Y10S707/99932

  Access augmentation or optimizing · CPC title

• G06F16/951

  Indexing; Web crawling techniques · CPC title