Object-level database performance management

• US 8,788,527 B1
• Filed: 08/15/2011
• Issued: 07/22/2014
• Est. Priority Date: 12/31/2003
• Status: Expired due to Fees

• Current Assignee: Precise Software Solutions, Inc. (Idera, Inc.)
• Original Assignee: Precise Software Solutions, Inc. (Idera, Inc.)
• Inventors: Nadel, Gil, Kolk, Kristiaan
• Primary Examiner(s): HO, BINH VAN
• Application Number:
  US13/209,745
• Time in Patent Office: 1,072 Days
• Field of Search: 707/781
• US Class Current: 707/781
• 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/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99939 Privileged access
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A method and system are provided for object-level database monitoring and tuning in a performance management system. Performance data for a plurality of database objects in a database server computer system are collected and stored. A performance problem in the database server computer system is detected. A problematic database object is identified using the performance data for the plurality of database objects, wherein the problematic database object is related to the performance problem. The problematic database object is tuned to improve performance of the database server computer system.

Claims

1. A method, including steps of

by a measurement component at a server, for one or more database objects, measuring a performance timing effect associated with accessing said database objects;

identifying a particular database object in response to said performance timing effect, said particular database object adversely effecting performance of said server in correlation with said performance timing effect;

wherein said steps of identifying include steps of periodically sampling a database performance view, said database performance view being responsive to said performance timing effect; and

measuring which particular database objects exhibit said performance timing effect relatively more frequently;

generating one or more tuning recommendations, wherein the one or more tuning recommendations comprise one or more of;

moving said particular database object to a different storage component, or creating a new access path to said particular database object.

2. A method as in claim 1, wherein

said particular database object includes at least one of:

a block involved in an I/O operation,

an object involved in an I/O operation,

an object involved in lock contention.

3. A method as in claim 1, wherein

said performance timing effect includes at least one of:

a measure of CPU time, a time delay, an access time, an application lock wait, an I/O wait, a wait time in response to contention.

4. A method as in claim 1, wherein

said steps of identifying include steps of

automatic reporting of said particular database object.

5. A method as in claim 1, wherein

said steps of identifying include steps of

correlating said database objects with one or more results of said steps of measuring a performance timing effect.

6. A method as in claim 1, wherein

said steps of tuning include at least one of:

transferring said particular database object between a 1st and a 2nd type of memory;

creating a new access path to said particular database object;

transferring said particular database object between a 1st and a 2nd location in a memory;

altering a table at said server.

7. A method as in claim 1, wherein

said steps of identifying include steps of

detecting a performance problem associated with accessing said server.

8. A method as in claim 7, wherein

said steps of identifying include steps of

scheduled analysis of one or more results of said steps of measuring a performance timing effect.

9. A method as in claim 1, further comprising tuning said particular database object based on said one or more tuning recommendations.

10. A method as in claim 9, wherein said tuning step is performed automatically.

11. A method as in claim 9, wherein said tuning step comprises tuning a plurality of database objects.

12. A method as in claim 1, further comprising collecting and storing performance data for a plurality of database objects.

13. A method as in claim 1, wherein said one or more tuning recommendations improves a measure of performance of said server.

14. A method as in claim 1, wherein said one or more tuning recommendations improves a measure of performance of an application that accesses said database objects.

15. A method as in claim 1, wherein said performance timing effect comprises access patterns.

16. A method, including steps of

by a measurement component determining an effect of using queries to access one or more database objects on a resource consumption performance of said queries;

identifying a particular database object in response to said steps of determining, said particular database object adversely effecting performance of said server in correlation with said resource consumption performance;

wherein said steps of identifying include steps of

periodically sampling a database performance view, said database performance view being responsive to said resource consumption performance; and

measuring which particular database objects exhibit said resource consumption performance relatively more frequently;

improving a measure of performance of said queries in response to said steps of identifying by one or more of;

moving said particular database object to a different storage component, or creating a new access path to said particular database object.

17. A method as in claim 16, wherein

said resource consumption includes a time delay.

18. A method as in claim 16, wherein

said steps of identifying include steps of automatic reporting of said particular database object.

19. A method as in claim 16, wherein

said steps of identifying include steps of

correlating said database objects with one or more results of said steps of determining an effect.

20. A method as in claim 16, wherein

said resource consumption performance includes a time measure.

21. A method as in claim 16, wherein

said steps of improving include steps of tuning said particular database object.

22. A method as in claim 21, wherein

said steps of tuning include at least one of:

moving said particular database object;

creating a new access path to said particular database object.

23. A method as in claim 16, wherein said improving step is performed automatically.

24. A system as in claim 16, wherein said improving step is based on one or more tuning recommendations.

25. A method as in claim 16, wherein said improving step comprises tuning a plurality of database objects.

26. A method as in claim 16, further comprising collecting and storing performance data for a plurality of database objects.

27. A method as in claim 16, wherein said improving step improves a measure of performance of said server.

28. A method as in claim 16, wherein said improving step improves a measure of performance of an application that accesses said database objects.

29. A method as in claim 16, wherein said resource consumption comprises access patterns.

30. A method, including steps of

receiving onto a computer-readable physical medium, sending from a computer-readable physical medium, or storing on a computer-readable physical medium, information interpretable by a computing device, the information including instructions to perform steps of

by a measurement component at a server, for one or more database objects, measuring a performance timing effect associated with accessing said database objects;

identifying a particular database object in response to said performance timing effect, said particular database object adversely effecting performance of said server in correlation with said performance timing effect;

wherein said steps of identifying include steps of

periodically sampling a database performance view, said database performance view being responsive to said performance timing effect; and

measuring which particular database objects exhibit said performance timing effect relatively more frequently;

generating one or more tuning recommendations, wherein the one or more tuning recommendations comprise one or more of;

moving said particular database object to a different storage component, or creating a new access path to said particular database object.

31. A method as in claim 30, wherein

said particular database object includes at least one of:

a block involved in an I/O operation,

an object involved in an I/O operation,

an object involved in lock contention.

32. A method as in claim 30, wherein

said performance timing effect includes at least one of:

a measure of CPU time, a time delay, an access time, an application lock wait, an I/O wait, a wait time in response to contention.

33. A method as in claim 30, wherein

said steps of identifying include steps of

automatic reporting of said particular database object.

34. A method as in claim 30, wherein

said steps of identifying include steps of

correlating said database objects with one or more results of said steps of measuring a performance timing effect.

35. A method as in claim 30, wherein

said steps of tuning include at least one of:

creating a new access path to said particular database object;

transferring said particular database object between a 1st and a 2nd location in a memory;

transferring said particular database object between a 1st and a 2nd type of memory;

partitioning or rebuilding a table at said server.

36. A method as in claim 30, wherein

said steps of identifying include steps of

detecting a performance problem associated with accessing said server.

37. A method as in claim 36, wherein

said steps of detecting a performance problem include steps of

scheduled analysis of one or more results of said steps of measuring a performance timing effect.

38. A method as in claim 30, further comprising tuning said particular database object based on said one or more tuning recommendations.

39. A method as in claim 38, wherein said tuning step is performed automatically.

40. A method as in claim 38, wherein said tuning step comprises tuning a plurality of database objects.

41. A method as in claim 30, further comprising collecting and storing performance data for a plurality of database objects.

42. A method as in claim 30, wherein said one or more tuning recommendations improves a measure of performance of said server.

43. A method as in claim 30, wherein said one or more tuning recommendations improves a measure of performance of an application that accesses said database objects.

44. A method as in claim 30, wherein said performance timing effect comprises access patterns.

45. A database server, including

one or more processors;

one or more database objects that participate in the execution of SQL statements, said SQL statements being executed by said one or more processors and said database objects being accessed in response thereto;

a measure of performance of said execution of SQL statements including resource consumption, said measure of performance being responsive to said SQL statements being executed by said one or more processors;

at least one particular said database object adversely affecting said resource consumption; and

agent software operating at said database server,

said agent software identifying said particular database object in response to;

monitoring said database server,

sampling said database objects for resource consumption cases,

maintaining performance data, and

correlating said performance data with said particular database object,

wherein said agent software is operative to tune said particular database object, whereby improving performance of said database server;

wherein said agent software is operative to generate one or more tuning recommendations;

wherein said one or more tuning recommendations comprise one or more of;

moving said particular database object to a different storage component, or creating a new access path to said particular database object.

46. A database server as in claim 45, wherein

said resource consumption cases include at least one of:

a time delay, an access time, an application lock wait, an I/O wait, a wait time in response to contention.

47. A database server as in claim 45, wherein said resource consumption comprises access patterns.

48. A database server as in claim 45, wherein said agent software is operative to tune a plurality of database objects.

49. A database server as in claim 45, wherein said tuning improves performance of an application that accesses said database objects.

50. A system including

one or more processors;

a measurement component including agent software modules capable of identifying particular database objects and capturing performance metrics on said database objects, said

agent software modules being executed by said one or more processors and said performance metrics being updated in response thereto;

wherein said agent software is capable of generating one or more tuning recommendations;

wherein said one or more tuning recommendations comprise one or more of;

moving said particular database object to a different storage component, or creating a new access path to said particular database object;

a discovery component including installations of agent software on each server running target applications, said installations being executed by one or more processors;

a console component communicating key performance indicators to users at appropriate times;

a performance warehouse including a repository of said performance metrics accessible to said measurement component, said discovery component, and said console component.

51. A system as in claim 50, wherein

said performance warehouse includes historical data with respect to said performance metrics.

52. A system as in claim 50, wherein

said console component includes a software element capable of at least one of:

establishing thresholds for said indicators,

reporting and charting said indicators,

providing alerts for said indicators,

managing user interaction with said measurement component and said discovery component.

53. A system as in claim 50, wherein

said servers running target applications include at least one of:

a web server, an application server, a database server, a storage server.

54. A system as in claim 50, wherein said agent software is capable of tuning a plurality of database objects.

55. A system as in claim 50, wherein said agent software is capable of tuning a plurality of database objects based on one or more tuning recommendations.

56. A system as in claim 55, wherein said tuning improves a measure of performance of at least one server accessing said database objects.