System and method for selectively preparing customized reports of query explain data

• US 6,195,653 B1
• Filed: 01/13/2000
• Issued: 02/27/2001
• Est. Priority Date: 10/14/1997
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Couch, Tanya, Bleizeffer, Terry Michael, Lea, Joseph Davis III, Lai, Theresa Hsing, Drummond, Catherine Elizabeth Wuebker, Oberstadt, David Harold, Hughes, Virginia Walbridge Jr.
• Primary Examiner(s): Breene, John
• Assistant Examiner(s): ROBINSON, GRETA LEE
• Application Number:
  US09/482,595
• Time in Patent Office: 411 Days
• Field of Search: 707/2, 707/104, 707/102, 707/4, 707/526
• 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: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A method for selectively preparing customized reports of query explain data comprises selectively retrieving from the database subsets of the query explain data for the at least one query statement; formatting the subsets for display to a user; displaying within a report customization window one or more controls for selectively designating subsets of the query explain data for the at least one query statement to be included in the report; and in response to selection of the one or more controls, generating a report that includes the designated subsets of the query explain data for the at least one query statement.

Claims

1. An apparatus for selectively preparing customized reports of query explain data for at least one query statement to be executed by a database, the apparatus comprising:

a memory device having thereon modules of operational data and executable code for execution by a processor, the modules comprising;

a plurality of report generation modules configured to selectively retrieve, from the database, subsets of the query explain data for the at least one query statement and to format the subsets for display to a user;

a customization module configured to provide an interactive customization display on a display device; and

a controls module configured to provide within the interactive customization display one or more controls for selectively designating subsets of the query explain data for the at least one query statement to be included in the report;

wherein the report generation modules are selectively invokable by the one or more controls to include in the report the designated subsets of the query explain data for the at least one query statement.

2. The apparatus of claim 1, further comprising:

a query selection module configured to receive a user selection of the at least one query statement.

3. The apparatus of claim 1, wherein the query statement comprises a Structured Query Language (SQL) statement.

4. The apparatus of claim 1, wherein at least a portion of the query explain data is generated by the database in response to one of a dynamic explain command and a binding of a query statement.

5. The apparatus of claim 1, wherein the query explain data comprises statement cost data comprising an estimated cost for executing the at least one query statement.

6. The apparatus of claim 5, wherein one of the report generation modules comprises a statement cost module configured to selectively retrieve from the database the statement cost data for the at least one query statement.

7. The apparatus of claim 6, wherein the statement cost data is stored within a statement table in the database.

8. The apparatus of claim 7, further comprising:

a querying module coupled to the statement cost module and configured to query the statement table to obtain therefrom the statement cost data.

9. The apparatus of claim 1, wherein the query explain data comprises access path data comprising at least one access path step generated by the database to execute the at least one query statement.

10. The apparatus of claim 9, wherein one of the report generation modules comprises an access path module configured to selectively retrieve from the database the access path data for the at least one query statement.

11. The apparatus of claim 10, wherein the access path module is configured to selectively retrieve criteria details for at least one access path step selected from the group consisting of a sort, an index access, a multiple index access, a table space scan, a join, and a return.

12. The apparatus of claim 10, wherein the access path data is stored within a plan table in the database.

13. The apparatus of claim 12, further comprising:

a querying module coupled to the access path module and configured to query the plan table in the database to obtain therefrom the access path data.

14. The apparatus of claim 1, wherein the query explain data comprises object statistics data including statistics for at least one database object accessed by the at least one query statement.

15. The apparatus of claim 14, wherein one of the report generation modules comprises an object statistics module configured to selectively retrieve from the database the object statistics for the at least one object accessed by the at least one query statement.

16. The apparatus of claim 14, wherein the object statistics module is configured to selectively retrieve object statistics for at least one object selected from the group consisting of an index, a table, a table space, a column, and a column distribution.

17. The apparatus of claim 16, wherein the object statistics data is stored within a catalog in the database.

18. The apparatus of claim 17, further comprising:

a querying module coupled to the object statistics module and configured to query the catalog to obtain therefrom the object statistics data.

19. The apparatus of claim 1, wherein the query explain data comprises statement cost data including an estimated cost for executing the at least one query statement, access path data including at least one access path step generated by the database to execute the at least one query statement, and object statistics data including statistics for at least one database object accessed by the at least one query statement, the report generation modules comprising:

a statement cost module configured to selectively retrieve from the database the statement cost data for the at least one query statement;

an access path module configured to selectively retrieve from the database the access path data for the at least one query statement; and

an object statistics module configured to selectively retrieve from the database the object statistics for the at least one object accessed by the at least one query statement.

20. A method for selectively preparing customized reports of query explain data for at least one query statement to be executed by a database, the method comprising:

selectively retrieving from the database subsets of the query explain data for the at least one query statement;

formatting the subsets for display to a user;

displaying within a report customization window one or more controls for selectively designating subsets of the query explain data for the at least one query statement to be included in the report; and

in response to selection of the one or more controls, generating a report that includes the designated subsets of the query explain data for the at least one query statement.

21. The method of claim 20, further comprising:

receiving a user selection of the at least one query statement.

22. The method of claim 20, wherein the query statement comprises a Structured Query Language (SQL) statement.

23. The method of claim 20, wherein at least a portion of the query explain data is generated by the database in response to one of a dynamic explain command and a binding of a query statement.

24. The method of claim 20, wherein the query explain data comprises statement cost data comprising an estimated cost for executing the at least one query statement.

25. The method of claim 24, further comprising:

selectively retrieving from the database the statement cost data for the at least one query statement.

26. The method of claim 25, wherein the statement cost data is stored within a statement table in the database.

27. The method of claim 26, further comprising:

querying the statement table to obtain therefrom the statement cost data.

28. The method of claim 20, wherein the query explain data comprises access path data comprising at least one access path step generated by the database to execute the at least one query statement.

29. The method of claim 28, further comprising:

selectively retrieving from the database the access path data for the at least one query statement.

30. The method of claim 29, further comprising:

selectively retrieving criteria details for at least one access path step selected from the group consisting of a sort, an index access, a multiple index access, a table space scan, a join, and a return.

31. The method of claim 29, wherein the access path data is stored within a plan table in the database.

32. The method of claim 31, further comprising:

querying the plan table in the database to obtain therefrom the access path data.

33. The method of claim 20, wherein the query explain data comprises object statistics data including statistics for at least one database object accessed by the at least one query statement.

34. The method of claim 33, further comprising:

selectively retrieving from the database the object statistics for the at least one object accessed by the at least one query statement.

35. The method of claim 34, further comprising:

selectively retrieving object statistics for at least one object selected from the group consisting of an index, a table, a table space, a column, and a column distribution.

36. The method of claim 34, wherein the object statistics data is stored within a catalog in the database.

37. The method of claim 36, further comprising:

querying the catalog to obtain therefrom the object statistics data.

38. The method of claim 20, wherein the query explain data comprises statement cost data including an estimated cost for executing the at least one query statement, access path data including at least one access path step generated by the database to execute the at least one query statement, and object statistics data including statistics for at least one database object accessed by the at least one query statement, the method further comprising:

selectively retrieving from the database the statement cost data for the at least one query statement;

selectively retrieving from the database the access path data for the at least one query statement; and

selectively retrieving from the database the object statistics for the at least one object accessed by the at least one query statement.

39. An article of manufacture comprising a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform a method for selectively preparing customized reports of query explain data for at least one query statement to be executed by a database, the method comprising:

selectively retrieving from the database subsets of the query explain data for the at least one query statement;

formatting the subsets for display to a user;

displaying within the report customization window one or more controls for selectively designating subsets of the query explain data for the at least one query statement to be included in the report; and

in response to selection of the one or more controls, generating a report that includes the designated subsets of the query explain data for the at least one query statement.

40. The article of manufacture of claim 39, the method further comprising:

receiving a user selection of the at least one query statement.

41. The article of manufacture of claim 39, wherein the query statement comprises a Structured Query Language (SQL) statement.

42. The article of manufacture of claim 39, wherein at least a portion of the query explain data is generated by the database in response to one of a dynamic explain command and a binding of a query statement.

43. The article of manufacture of claim 39, wherein the query explain data comprises statement cost data comprising an estimated cost for executing the at least one query statement.

44. The article of manufacture of claim 43, the method further comprising:

selectively retrieving from the database the statement cost data for the at least one query statement.

45. The article of manufacture of claim 44, wherein the statement cost data is stored within a statement table in the database.

46. The article of manufacture of claim 45, the method further comprising:

querying the statement table to obtain therefrom the statement cost data.

47. The article of manufacture of claim 39, wherein the query explain data comprises access path data comprising at least one access path step generated by the database to execute the at least one query statement.

48. The article of manufacture of claim 47, the method further comprising:

selectively retrieving from the database the access path data for the at least one query statement.

49. The article of manufacture of claim 48, the method further comprising:

selectively retrieving criteria details for at least one access path step selected from the group consisting of a sort, an index access, a multiple index access, a table space scan, a join, and a return.

50. The article of manufacture of claim 47, wherein the access path data is stored within a plan table in the database.

51. The article of manufacture of claim 50, the method further comprising:

querying the plan table in the database to obtain therefrom the access path data.

52. The article of manufacture of claim 51, wherein the query explain data comprises object statistics data including statistics for at least one database object accessed by the at least one query statement.

53. The article of manufacture of claim 52, the method further comprising:

selectively retrieving from the database the object statistics for the at least one object accessed by the at least one query statement.

54. The article of manufacture of claim 53, the method further comprising:

selectively retrieving object statistics for at least one object selected from the group consisting of an index, a table, a table space, a column, and a column distribution.

55. The article of manufacture of claim 52, wherein the object statistics data is stored within a catalog in the database.

56. The article of manufacture of claim 55, the method further comprising:

querying the catalog to obtain therefrom the object statistics data.

57. The article of manufacture of claim 39, wherein the query explain data comprises statement cost data including an estimated cost for executing the at least one query statement, access path data including at least one access path step generated by the database to execute the at least one query statement, and object statistics data including statistics for at least one database object accessed by the at least one query statement, the method further comprising:

selectively retrieving from the database the statement cost data for the at least one query statement;

selectively retrieving from the database the access path data for the at least one query statement; and

selectively retrieving from the database the object statistics for the at least one object accessed by the at least one query statement.