SQL PERFORMANCE ANALYZER

• US 20090077017A1
• Filed: 08/08/2008
• Published: 03/19/2009
• Est. Priority Date: 09/18/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Belknap, Peter, Dias, Karl, Yagoub, Khaled, Dageville, Benoit
• Granted Patent: US 8,341,178 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 11/3476 Data logging G06F11/14, G06F11/2205 take precedence
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

Techniques are provided for analyzing performance differences for a set of database query language statements on two different database systems. The performance analysis is based on quantitative measurements and estimates of the execution of the set of database query language statements on the two different database systems. This performance analysis process may be used by database administrators to predict impacts to performance due to a change in a database system.

Claims

1. A computer-implemented method comprising:

selecting, from a workload set, a set of targeted database query language statements for performance analysis;

wherein the workload set comprises database query language statements and performance data for the database query language statements;

executing, on a first database system, the set of targeted query language statements;

wherein executing the set of targeted database query language statements on the first database system comprises gathering a first set of performance data about the execution of the targeted database query language statements on the first database system;

executing, on a second database system, the set of targeted query language statements;

wherein executing the set of targeted database query language statements on the second database system comprises gathering a second set of performance data about the execution of the targeted database query language statements on the second database system;

comparing the first set of performance data with the second set of performance data;

storing, in a computer-readable medium, information that indicates a result of the comparison.

2. The computer-implemented method of claim 1, wherein the workload set is captured from a production database system.

3. The computer-implemented method of claim 2, wherein the workload set is captured by:

capturing database query language statements that have been executed on the production database system over a period of time; and

capturing performance data for the database query language statements.

4. The computer-implemented method of claim 2, wherein the workload set is captured from at least one diagnostic trace of the production database system.

5. The computer-implemented method of claim 1, wherein the workload set further comprises execution context data for the database query language statements.

6. The computer-implemented method of claim 5, wherein the execution context data includes bind values and optimizer parameter values.

7. The computer-implemented method of claim 1, wherein the set of targeted database query language statements comprises database query language statements that originate from a specific application.

8. The computer-implemented method of claim 1, wherein the first set of performance data includes, for each database query language statement in the set of targeted database query language statements, a total execution time.

9. The computer-implemented method of claim 8, wherein the first set of performance data further includes, for each database query language statement in the set of targeted database query language statements, a CPU time and an I/O time.

10. The computer-implemented method of claim 1, wherein each database query language statement in the set of targeted database query language statements is executed on the first database system in isolation from other database query language statements in the set of targeted database query language statements.

11. The computer-implemented method of claim 1, wherein:

executing the set of targeted query language statements comprises generating, for each database query language statement in the set of targeted database query language statements, an execution plan; and

the first set of performance data includes, for the each database query language statement, an estimated cost based on the execution plan generated for the each database query language statement.

12. The computer-implemented method of claim 1, wherein executing the set of targeted query language statements on the first database system comprises executing the set of targeted query language statements in an order specified by a user.

13. The computer-implemented method of claim 1, wherein executing the set of targeted query language statements on the first database system is terminated if the execution reaches a pre-determined time limit.

14. The computer-implemented method of claim 1, wherein executing the set of targeted query language statements on the first database system does not modify any data stored in a database contained in the first database system.

15. The computer-implemented method of claim 1, wherein the second database system is a modified version of the first database system.

16. The computer-implemented method of claim 1, wherein the information that indicates a result of the comparison includes a difference in a total performance metric for executing the set of targeted database query language statements between the first database system and the second database system.

17. The computer-implemented method of claim 1, wherein the information that indicates a result of the comparison includes, for each database query language statement in the set of targeted database query language statements, a difference in performance metric for executing the each database query language statement between the first database system and the second database system.

18. The computer-implemented method of claim 17, wherein the performance metric is execution time.

19. The computer-implemented method of claim 17, wherein the performance metric is a CPU time.

20. The computer-implemented method of claim 11, wherein:

the second set of performance data includes, for each database query language statement in the set of targeted database query language statements, an estimated cost based on an execution plan generated for the each database query language statement for execution on the second database system;

the information that indicates a result of the comparison includes information that indicates, for each database query language statement in the set of targeted database query language statements, whether the execution plan generated for the each database query language statement for execution on the second database system differs from the execution plan generated for the each database query language statement for execution on the first database system.

21. The computer-implemented method of claim 1, further comprising:

generating a report that includes the information that indicates a result of the comparison.

22. The computer-implemented method of claim 21, wherein:

the report further includes a recommendation for a database query language statement in the set of targeted database query language statements whose performance in the second database system is worse than performance in the first database system;

the recommendation is to tune the database query language statement.

23. The computer-implemented method of claim 21, wherein:

the report further includes a recommendation for a database query language statement in the set of targeted database query language statements whose performance in the second database system is worse than performance in the first database system;

the recommendation is to create a plan baseline for the database query language statement;

the plan baseline is the execution plan for the database query language statement in the first database system; and

creation of the plan baseline for the database query language statements results in the plan baseline being used in executing the database query language statement in the second database system.

24. The computer-implemented method of claim 1, wherein the step of executing the set of targeted database query language statements on the first database comprises executing the set of targeted database query language statements through a database link.

25. A computer-readable 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:

selecting, from a workload set, a set of targeted database query language statements for performance analysis;

wherein the workload set comprises database query language statements and performance data for the database query language statements;

executing, on a first database system, the set of targeted query language statements;

wherein executing the set of targeted database query language statements on the first database system comprises gathering a first set of performance data about the execution of the targeted database query language statements on the first database system;

executing, on a second database system, the set of targeted query language statements;

wherein executing the set of targeted database query language statements on the second database system comprises gathering a second set of performance data about the execution of the targeted database query language statements on the second database system;

comparing the first set of performance data with the second set of performance data;

storing, in a computer-readable medium, information that indicates a result of the comparison.

26. The computer-readable storage medium of claim 25, wherein the workload set is captured from a production database system.

27. The computer-readable storage medium of claim 26, wherein the workload set is captured by:

capturing database query language statements that have been executed on the production database system over a period of time; and

capturing performance data for the database query language statements.

28. The computer-readable storage medium of claim 26, wherein the workload set is captured from at least one diagnostic trace of the production database system.

29. The computer-readable storage medium of claim 25, wherein the workload set further comprises execution context data for the database query language statements.

30. The computer-readable storage medium of claim 29, wherein the execution context data includes bind values and optimizer parameter values.

31. The computer-readable storage medium of claim 25, wherein the set of targeted database query language statements comprises database query language statements that originate from a specific application.

32. The computer-readable storage medium of claim 25, wherein the first set of performance data includes, for each database query language statement in the set of targeted database query language statements, a total execution time.

33. The computer-readable storage medium of claim 32, wherein the first set of performance data further includes, for each database query language statement in the set of targeted database query language statements, a CPU time and an I/O time.

34. The computer-readable storage medium of claim 25, wherein each database query language statement in the set of targeted database query language statements is executed on the first database system in isolation from other database query language statements in the set of targeted database query language statements.

35. The computer-readable storage medium of claim 25, wherein:

the instructions for executing the set of targeted query language statements comprises instructions for generating, for each database query language statement in the set of targeted database query language statements, an execution plan; and

the first set of performance data includes, for the each database query language statement, an estimated cost based on the execution plan generated for the each database query language statement.

36. The computer-readable storage medium of claim 25, wherein executing the set of targeted query language statements on the first database system comprises executing the set of targeted query language statements in an order specified by a user.

37. The computer-readable storage medium of claim 25, wherein executing the set of targeted query language statements on the first database system is terminated if the execution reaches a pre-determined time limit.

38. The computer-readable storage medium of claim 25, wherein executing the set of targeted query language statements on the first database system does not modify any data stored in a database contained in the first database system.

39. The computer-readable storage medium of claim 25, wherein the second database system is a modified version of the first database system.

40. The computer-readable storage medium of claim 25, wherein the information that indicates a result of the comparison includes a difference in a total performance metric for executing the set of targeted database query language statements between the first database system and the second database system.

41. The computer-readable storage medium of claim 25, wherein the information that indicates a result of the comparison includes, for each database query language statement in the set of targeted database query language statements, a difference in performance metric for executing the each database query language statement between the first database system and the second database system.

42. The computer-readable storage medium of claim 41, wherein the performance metric is execution time.

43. The computer-implemented method of claim 41, wherein the performance metric is a CPU time.

44. The computer-readable storage medium of claim 35, wherein:

the second set of performance data includes, for each database query language statement in the set of targeted database query language statements, an estimated cost based on an execution plan generated for the each database query language statement for execution on the second database system;

the information that indicates a result of the comparison includes information that indicates, for each database query language statement in the set of targeted database query language statements, whether the execution plan generated for the each database query language statement for execution on the second database system differs from the execution plan generated for the each database query language statement for execution on the first database system.

45. The computer-readable storage medium of claim 25, further comprising:

generating a report that includes the information that indicates a result of the comparison.

46. The computer-readable storage medium of claim 45, wherein:

the report further includes a recommendation for a database query language statement in the set of targeted database query language statements whose performance in the second database system is worse than performance in the first database system;

the recommendation is to tune the database query language statement.

47. The computer-readable storage medium of claim 46, wherein:

the report further includes a recommendation for a database query language statement in the set of targeted database query language statements whose performance in the second database system is worse than performance in the first database system;

the recommendation is to create a plan baseline for the database query language statement;

the plan baseline is the execution plan for the database query language statement in the first database system; and

creation of the plan baseline for the database query language statements results in the plan baseline being used in executing the database query language statement in the second database system.

48. The computer-readable storage medium of claim 25, wherein the step of executing the set of targeted database query language statements on the first database comprises executing the set of targeted database query language statements through a database link.