SQL performance analyzer

• US 8,341,178 B2
• Filed: 08/08/2008
• Issued: 12/25/2012
• 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, Dageville, Benoit, Dias, Karl, Yagoub, Khaled
• Primary Examiner(s): Cao, Phuong Thao
• Application Number:
  US12/188,981
• Publication Number:
  US 20090077017A1
• Time in Patent Office: 1,600 Days
• Field of Search: 707/999.003, 707/999.005, 707/769, 707/964
• US Class Current: 707/769
• 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;

executing, on a first database system, the set of targeted database 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 each database query language statement of said targeted database query language statements on the first database system;

executing, on a second database system, the set of targeted database query language statements, wherein the database of the second database system is a modified version of the database of the first database system;

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 said each database query language statement of said targeted database query language statements on the second database system;

wherein each of the first set of performance data and second set of performance data comprises statistics based on at least one of the following;

(a) CPU time consumed to execute said each database query language statement of said set of targeted database query language statements,

(b) buffer reads incurred to execute said each database query language statement of said set of targeted database query language statements, and

(c) disk reads incurred to execute said each database query language statement of said set of targeted database query language statements;

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

generating information that indicates a result of the comparison,

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

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 a performance metric for executing said each database query language statement between the first database system and the second database system.

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.

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

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

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

17. The computer-implemented method of claim 16, 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.

18. The computer-implemented method of claim 16, 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 statement results in the plan baseline being used in executing the database query language statement in the second database system.

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

20. 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;

executing, on a first database system, the set of targeted database 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 each database query language statement of said targeted database query language statements on the first database system;

executing, on a second database system, the set of targeted database query language statements, wherein the database of the second database system is a modified version of the database of the first database system;

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 said each database query language statement of said targeted database query language statements on the second database system;

wherein each of the first set of performance data and second set of performance data comprises statistics based on at least one of the following;

(a) CPU time consumed to execute said each database query language statement of said set of targeted database query language statements,

(b) buffer reads incurred to execute said each database query language statement of said set of targeted database query language statements, and

(c) disk reads incurred to execute said each database query language statement of said set of targeted database query language statements;

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

generating information that indicates a result of the comparison,

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

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 a performance metric for executing said each database query language statement between the first database system and the second database system.

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

22. The computer-readable storage medium of claim 21, 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.

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

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

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

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

27. The computer-readable storage medium of claim 20, 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.

28. The computer-readable storage medium of claim 27, 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.

29. The computer-readable storage medium of claim 20, 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.

30. The computer-readable storage medium of claim 20, 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.

31. The computer-readable storage medium of claim 20, 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.

32. The computer-readable storage medium of claim 20, 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.

33. The computer-readable storage medium of claim 20, 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.

34. The computer-readable storage medium of claim 30, 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.

35. The computer-readable storage medium of claim 20, further comprising:

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

36. The computer-readable storage medium of claim 35, 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.

37. The computer-readable storage medium of claim 35, 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.

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