Auto-tuning SQL statements

• US 7,805,411 B2
• Filed: 09/07/2004
• Issued: 09/28/2010
• 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
• Primary Examiner(s): Morrison; Jay A
• Application Number:
  US10/935,908
• Publication Number:
  US 20050120000A1
• Time in Patent Office: 2,212 Days
• Field of Search: 707/2
• US Class Current: 707/688
• 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 computer implemented method, comprising:

using at least one computer system configured for;

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

determining whether one or more statistics relating to the database query language statement are available or accurate in the performance information;

determining a hint for a missing or an inaccurate statistic in the performance information;

creating or improving an estimate of a value of an execution of the query language statement by at least recursively determining whether a predicate selectivity exceeds a threshold, wherein

the act of creating or improving the estimate comprises sampling an adjustable portion of data against which the query language statement is to be performed without requiring performance of a full table scan based at least in part upon a relationship between the predicate selectivity and the threshold; and

verifying the estimate by executing the query language statement to perform at least the full table scan based at least in part upon the predicate selectivity, wherein the act of verifying the estimate performs a sample of a result of the full table scan to adjust the estimate.

2. The method of claim 1, further comprising:

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

generating the 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; and

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 the 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 the 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 it 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 the 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.

43. The computer implemented method of claim 1, wherein the hint comprises a first adjustment to the adjustable portion of data to determine an amount of the data that is needed for the estimate to be statistically valid.

44. The computer implemented method of claim 1, wherein the hint further comprises a second adjustment to the predicate selectivity to adjust an access path for recursive execution of the query language statement.

21. An apparatus, comprising:

at least one computer system configured for;

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

determining whether one or more statistics relating to the database query language statement are available or accurate in the performance information;

determining a hint for a missing or an inaccurate statistic in the performance information; and

creating or improving an estimate of a value of an execution of the query language statement by at least recursively determining whether a predicate selectivity exceeds a threshold, wherein

the act of creating or improving the estimate comprises sampling an adjustable portion of data against which the query language statement is to be performed without requiring performance of a full table scan based at least in part upon a relationship between the predicate selectivity and the threshold; and

verifying the estimate by executing the query language statement to generate the hint to perform at least the full table scan based at least in part upon the predicate selectivity, wherein the act of verifying the estimate performs a sample of a result of the full table scan to adjust the estimate.

22. The apparatus of claim 21, further comprising:

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

means for generating the query optimization hint for each inaccurate statistic.

23. The apparatus of claim 21, wherein the computer system configured for determining whether one or more statistics are available or accurate comprises:

means for re-collecting the performance statistic; and

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

24. The apparatus of claim 21, further comprising:

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

means for generating the hint for each determined optimizer setting.

25. The apparatus of claim 21, further comprising:

means for validating table estimates of the statement; and

means for generating the hint for each validated table estimate.

26. The apparatus of claim 21, further comprising:

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

means for generating the hint for each validated cost estimate.

27. A volatile or non-volatile computer readable storage medium storing a computer program of instructions, which, when executed by a computer system, cause the computer system to perform a method, the method comprising:

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

determining whether one or more statistics relating to the database query language statement are available or accurate in the performance information;

determining a hint for a missing or an inaccurate statistic in the performance information; and

creating or improving an estimate of a value of an execution of the query language statement by at least recursively determining whether a predicate selectivity exceeds a threshold, wherein

the act of creating or improving the estimate comprises sampling an adjustable portion of data against which the query language statement is to be performed without requiring performance of a full table scan based at least in part upon a relationship between the predicate selectivity and the threshold; and

verifying the estimate by recursively executing the query language statement to generate the hint to adjust at least the adjustable portion of the data by performing at least the full table scan based at least in part upon the predicate selectivity, wherein the act of verifying the estimate performs a sample of the full table scan to adjust the estimate.

28. The medium of claim 27, wherein the method further comprises:

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

generating the query optimization hint for each inaccurate statistic.

29. The medium of claim 27, 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.

30. The medium of claim 27, wherein the method further comprises:

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

generating the query optimization hint for each determined optimizer setting.

31. The medium of claim 27, wherein the method further comprising:

validating table estimates of the statement; and

generating the query optimization hint for each validated table estimate.

32. The medium of claim 27, wherein the method further comprising:

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

generating the query optimization hint for each validated cost estimate.

33. The medium of claim 32, 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.

34. A computer implemented method, comprising:

using at least one computer system configured for;

receiving a SQL statement for a database for compilation, at an auto tuning optimizer and performance information related to the SQL statement; and

determining whether one or more statistics relating to the SQL statement are available or accurate in the performance information;

determining a hint for a missing or an inaccurate statistic in the performance information;

creating or improving an estimate of a value determined by the auto-tuning optimizer for generating an execution plan by sampling an adjustable portion of data of the database without requiring performance of a full table scan and by using a hint based at least in part upon a recursive determination of a relationship between a predicate selectivity and a threshold or a determination of whether an index for the SQL statement exists; and

verifying the estimate by executing the SQL statement to perform at least the full table scan based at least in part upon the predicate selectivity, wherein the act of verifying the estimate performs a sample of a result of the full table scan to adjust the estimate.

35. The method of claim 34, wherein self-correcting information comprises:

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

36. The method of claim 34, further comprising:

setting a time limit for performing the self-correcting.

37. The method of claim 36, 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.

38. The method of claim 34, further comprising:

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

determining a cost of the new execution plan; and

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

39. The method of claim 34, further comprising:

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

40. The method of claim 39, 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.

41. The method of claim 34, further comprising:

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

42. The method of claim 34, further comprising:

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