Auto-tuning SQL statements

• US 20050120000A1
• Filed: 09/07/2004
• Published: 06/02/2005
• 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, Zait, Mohamed, Dageville, Benoit, Ziauddin, Mohamed
• Granted Patent: US 7,805,411 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/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

Auto-tuning can be performed by receiving a database query language statement and performance information related to the statement, determining whether one or more performance statistics of the statement are available or missing in the performance information, and determining an auto-tuning hint for each missing statistic.

Claims

1. A method comprising:

receiving a database query language statement and performance information related to the statement;

determining whether one or more performance statistics of the statement are available or missing in the performance information; and

determining an auto-tuning hint for each missing statistic.

2. The method of claim 1, further comprising:

determining whether each performance statistic in the performance information is accurate; and

generating an auto-tuning hint for each inaccurate statistic.

3. The method of claim 1, wherein determining whether each performance statistic in the performance information is accurate comprises:

re-collecting the performance statistic;

comparing the value of the re-collected statistic with the value of the performance statistic in the performance information.

4. The method of claim 1, further comprising:

automatically determining one or more optimizer settings for the statement based on the performance information; and

generating an auto-tuning hint for each determined optimizer setting.

5. The method of claim 4, wherein the performance information is execution history of the statement.

6. The method of claim 5, wherein one of the optimizer settings is an optimizer mode, and automatically determining the optimizer mode comprises:

examining the execution history to determine an average number of rows returned by the statement; and

setting a parameter for the optimizer mode based on the average number of rows.

7. The method of claim 4, wherein the one or more optimizer settings are selected from the group consisting of:

an optimizer mode, an optimizer level, an optimizer goal, a degree of parallelism, an index caching ratio.

8. The method of claim 4, wherein the performance information is selected from the group consisting of:

execution history of the statement, bind information, compilation environment information, information related to a degree of parallelism, a time limit.

9. The method of claim 1, further comprising:

validating table estimates of the statement; and

generating an auto-tuning hint for each validated table estimate.

10. The method of claim 9, wherein validating table estimates comprises:

beginning the validation at leaf nodes of an execution plan tree; and

continuing the validation toward a root node of the execution plan tree.

11. The method of claim 9, wherein validating table estimates comprises:

recursively executing one or more portions of the statement.

12. The method of claim 11, wherein recursively executing comprises:

executing the one or more portions a number n of times; and

during a j^th execution, determining one or more j-table join estimates.

13. The method of claim 9, wherein validating comprises:

validating single table estimates.

14. The method of claim 13, wherein validating further comprises:

validating 2-table join estimates.

15. The method of claim 9, wherein validating comprises:

performing a dynamic compute query.

16. The method of claim 9, wherein validating comprises:

performing a dynamic sampling query.

17. The method of claim 16, wherein performing the dynamic sampling query is repeated until a statistically valid estimate is obtained.

18. The method of claim 9, wherein validating is performed until a time limit is reached.

19. The method of claim 1, further comprising:

validating cost estimates for performing actions in an execution plan; and

generating an auto-tuning hint for each validated cost estimate.

20. The method of claim 19, wherein validating cost estimates is performed with a cost model based on cost component factors selected from the group consisting of:

input/output (I/O) characteristics, central processing unit (CPU) characteristics, memory performance characteristics.

21. (canceled)

22. An apparatus comprising:

means for receiving a database query language statement and performance information related to the statement;

means for determining whether one or more performance statistics of the statement are available or missing in the performance information; and

means for determining an auto-tuning hint for each missing statistic.

23. The apparatus of claim 22, further comprising:

means for determining whether each performance statistic in the performance information is accurate; and

means for generating an auto-tuning hint for each inaccurate statistic.

24. The apparatus of claim 22, wherein said means for determining whether each performance statistic in the performance information is accurate comprises:

means for re-collecting the performance statistic;

means for comparing the value of the re-collected statistic with the value of the performance statistic in the performance information.

25. The apparatus of claim 22, further comprising:

means for automatically determining one or more optimizer settings for the statement based on the performance information; and

means for generating an auto-tuning hint for each determined optimizer setting.

30. The apparatus of claim 22, further comprising:

means for validating table estimates of the statement; and

means for generating an auto-tuning hint for each validated table estimate.

40. The apparatus of claim 22, further comprising:

means for validating cost estimates for performing actions in an execution plan; and

means for generating an auto-tuning hint for each validated cost estimate.

26-29. -29. (canceled)

31-39. -39. (canceled)

41. (canceled)

42. (canceled)

43. A computer readable medium storing a computer program of instructions, which, when executed by a processing system, cause the system to perform a method comprising:

receiving a database query language statement and performance information related to the statement;

determining whether one or more performance statistics of the statement are available or missing in the performance information; and

determining an auto-tuning hint for each missing statistic.

44. The medium of claim 43, wherein the method further comprises:

determining whether each performance statistic in the performance information is accurate; and

generating an auto-tuning hint for each inaccurate statistic.

45. The medium of claim 43, wherein determining whether each performance statistic in the performance information is accurate comprises:

re-collecting the performance statistic;

comparing the value of the re-collected statistic with the value of the performance statistic in the performance information.

46. The medium of claim 43, wherein the method further comprises:

automatically determining one or more optimizer settings for the statement based on the performance information; and

generating an auto-tuning hint for each determined optimizer setting.

51. The medium of claim 43, wherein the method further comprising:

validating table estimates of the statement; and

generating an auto-tuning hint for each validated table estimate.

61. The medium of claim 43, wherein the method further comprising:

validating cost estimates for performing actions in an execution plan; and

generating an auto-tuning hint for each validated cost estimate.

62. The medium of claim 61, wherein validating cost estimates is performed with a cost model based on cost component factors selected from the group consisting of:

input/output (I/O) characteristics, central processing unit (CPU) characteristics, memory performance characteristics.

47-50. -50. (canceled)

52-60. -60. (canceled)

63. (canceled)

64. A method comprising:

receiving a SQL statement at an auto tuning optimizer; and

self-correcting information that the auto tuning optimizer uses to generate an execution plan for the SQL statement.

65. The method of claim 64, wherein self-correcting information comprises:

building one or more auto tuning hints to help the optimizer generate a best execution plan for the statement.

66. The method of claim 64, further comprising:

setting a time limit for performing the self-correcting.

67. The method of claim 66, wherein setting the time limit comprises:

predicting an execution time of a current plan for the SQL statement; and

setting the time limit as a fraction of the predicted execution time.

68. The method of claim 64, further comprising:

building a new execution plan for the SQL statement using the self-corrected information;

determining a cost of the new plan; and

comparing the cost of the new plan with a cost of an old execution plan for the SQL statement.

69. The method of claim 64, further comprising:

using the self-corrected information to build auto tuning hints to correct base statistics.

70. The method of claim 69, wherein the auto tuning hints that are built to correct base statistics are selected from the group consisting of:

column statistics, table statistics, and index statistics.

71. The method of claim 64, further comprising:

using the self-corrected information to build auto tuning hints to correct optimizer settings.

72. The method of claim 64, further comprising:

using the self-corrected information to build auto tuning hints to correct optimizer cardinality estimates or optimizer cost estimates.